从SQL Server加载数据到Excel的最快方法。

I have a table on SQL Server that is relatively huge (approx 300,000 rows). This data is used as a pool of data to validate what users do in an Excel spreadsheet.

我在SQL Server上有一个相对较大的表(大约30万行)。此数据用作数据池，用于验证用户在Excel电子表格中所做的操作。

One way to ensure that users are always using the most up-to-date data is to set up a VBA to auto pull data from SQL server whenever the spreadsheet is open.

确保用户始终使用最新数据的一种方法是在电子表格打开时设置一个VBA来自动从SQL server中提取数据。

Code:

代码:

Private Sub Workbook_Open()
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB; Data Source=Server Name;Initial Catalog=Database;User ID=User;Password=Password; Trusted_Connection=no"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = "SELECT * FROM [Database]"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL

    'Copy Data to Excel'
        Sheets("TEPSD").Range("A1").CopyFromRecordset (objMyRecordset)

        objMyConn.Close

End Sub

The issue with this is it takes a very long time. Sometimes, it takes too long and I end up having to ctrl+break it.

这个问题需要很长时间。有时候，这需要很长时间，最后我不得不按ctrl+break它。

I was wondering whether there is a quicker way of doing this? Or another way to validate user data without having to pull the entire table to Excel?

我想知道有没有更快捷的方法来做这件事?或者另一种验证用户数据的方法，而不需要将整个表拖到Excel中?

Any input would be very much appreciated.

如有任何意见，我们将不胜感激。

2 个解决方案

#1

Following our discussion.

在我们的讨论。

At the moment, every user is trying to pull large amounts of data from the server in a short space of time, and then the data will get stale as it is not updated unless the user closes and reopens the workbook.

目前，每个用户都试图在很短的时间内从服务器中提取大量数据，然后数据将变得陈旧，因为除非用户关闭并重新打开工作簿，否则数据不会更新。

Instead, switch to a Worksheet_Change() macro. This will refresh the data every time a change is made.

相反，切换到Worksheet_Change()宏。这将在每次进行更改时刷新数据。

This will allow you to send a customised query every time the user makes a change - e.g. presses enter.

这将允许您在每次用户进行更改时发送自定义查询——例如按enter键。

Change your strSQL from a static query drawing out all the data to strSQL = "SELECT name FROM [Database] WHERE name = " & Target.Value

将strSQL从提取所有数据的静态查询更改为strSQL =“从[数据库]中选择name =”& Target.Value

N.B. You should really look up how VBA recordset wants you to do parameter concatenation in order to avoid Little Bobbie Tables.

您应该真正查找VBA记录集希望您如何进行参数连接，以避免小的Bobbie表。

If the query is successful - i.e. a valid entry - then it should return the same name. If there is no corresponding entry - i.e. not a valid selection - then you should get an empty recordset. This means you can test the result of the query with If rsObj.RecordCount < 1 Then in order to gracefully exit (or prompt the user etc.) when there is no valid match.

如果查询成功(即有效条目)，那么它应该返回相同的名称。如果没有相应的条目—即没有有效的选择—那么您应该得到一个空的记录集。这意味着您可以使用If rsObj测试查询结果。然后记录计数< 1，以便在没有有效匹配时优雅地退出(或提示用户等)。

#2

You should consider something like this.

你应该考虑这样的事情。

Connecting Excel to SQL Server

将Excel连接到SQL Server。

Devart Excel Add-in for SQL Server allows you to connect Excel to SQL Server databases, retrieve and load live SQL Server data to Excel, and then modify these data and save changes back to SQL Server. Here is how you can connect Excel to SQL server and load SQL Server data to Excel in few simple steps.

Devart用于SQL Server的Excel外接程序允许您将Excel连接到SQL Server数据库，检索并将实时SQL Server数据加载到Excel，然后修改这些数据并将更改保存回SQL Server。以下是如何将Excel连接到SQL server，并通过几个简单的步骤将SQL server数据加载到Excel中。

To start linking Excel to SQL Server, on the ribbon, click the DEVART tab and then click the Get Data button. This will display the Import Data wizard, where you need to create Excel SQL Server connection and configure query for getting data from SQL Server to Excel: Devart ribbon tab

要开始将Excel链接到SQL Server，请单击ribbon选项卡，然后单击Get Data按钮。这将显示Import Data向导，您需要创建Excel SQL Server连接并配置查询，以便从SQL Server获取数据到Excel: Devart ribbon选项卡

Specify Connection Parameters
指定连接参数

In the Connection Editor dialog box, you need to enter the necessary connection parameters:

在“连接编辑器”对话框中，您需要输入必要的连接参数:

Login details - select whether to use Windows authentication or SQL Server authentication. In the latter case you need to enter your SQL Server user name and password.
User name - your SQL Server user name. Required only for SQL Server authentication.
Password - your SQL Server password. Required only for SQL Server authentication.
Database - the name of SQL database to connect to Excel.

If you need to configure your Excel SQL Server connection in more details, you can optionally click the Advanced button and configure advanced connection parameters. There you can configure connection encryption, resiliency parameters, etc.

如果需要更详细地配置Excel SQL Server连接，可以选择单击Advanced按钮并配置高级连接参数。在那里，您可以配置连接加密、弹性参数等。

To check whether you have connected Excel to SQL Server your connection correctly, click the Test Connection button.

要检查您是否正确地将Excel连接到SQL Server，请单击Test connection按钮。

Excel SQL connection settings 2. Select whether to Store Connection in Excel Workbook

Excel SQL连接设置2。选择是否在Excel工作簿中存储连接。

You may optionally change settings how the connection and query data are stored in the Excel workbook and in Excel settings:

您可以选择更改连接和查询数据如何存储在Excel工作簿和Excel设置中的设置:

Allow saving add-in specific data in Excel worksheet - clear this check box in case you don't want to save any Excel add-in specific data in the Excel worksheet - connections, queries, etc. In this case, if you want to reload data from SQL Server to Excel or save modified data back to SQL Server, you will need to reenter both the connection settings and query.
Allow saving connection string in Excel worksheet - clear this check box if you want your SQL Server connection parameters not to be stored in the Excel. In this case you will need to reenter your connection settings each time you want to reload SQL Server data or modify and save them to SQL Server. However, you may share the Excel workbook, and nobody will be able to get any connection details from it.
Allow saving password - it is recommended to clear this check box. If you don't clear this check box, all the connection settings, including your SQL Server password, will be stored in the Excel workbook. And anyone having our Excel Add-in for SQL Server and the workbook will be able to link Excel to the SQL Server, get data from it, and modify them. But in this case you won't need to reenter anything when reloading data from SQL Server to Excel or saving them to SQL Server.
Allow reuse connection in Excel - select this check box if you want to save this connection on your computer and reuse it in other Excel workbooks. It does not affect saving connection parameters in the workbook itself. You need to specify the connection name, and after this you will be able to simply select this connection from the list

Configure Query to Get Data
配置查询以获取数据

You may either use Visual Query Builder to configure it visually, or switch to the SQL Query tab and type the SQL Query. To configure query visually, do the following:

您可以使用Visual Query Builder来可视化地配置它，或者切换到SQL Query选项卡并输入SQL查询。要直观地配置查询，请执行以下操作:

In the Object list select the SQL Server table to load its data to Excel.

In the tree below clear check boxes for the columns you don't want to import data from.

Optionally expand the relation node and select check boxes for the columns from the tables referenced by the current table's foreign keys to add them to the query.

In the box on the right you may optionally configure the filter conditions and ordering of the imported data and specify the max number of rows to load from SQL Server to Excel. For more information on configuring the query you may refer to our documentation, installed with the Excel Add-ins.

After specifying the query, you may optionally click Next and preview some of the first returned rows. Or click Finish and start data loading. Query for retrieving SQL Server data to Excel

在指定查询之后，您可以选择单击Next并预览第一个返回的行。或单击Finish并开始数据加载。查询检索到Excel的SQL Server数据

Editing Live SQL Server Data

编辑实时SQL服务器数据

After the data is loaded from SQL Server to Excel, you can work with these data like with usual Excel worksheet. You can instantly refresh data from SQL Server by clicking Refresh on the Devart tab of the ribbon, and thus, always have fresh live data from SQL Server in your workbook.

将数据从SQL Server加载到Excel之后，您可以使用这些数据，就像使用通常的Excel工作表一样。通过单击ribbon的Devart选项卡上的refresh，您可以立即从SQL Server刷新数据，因此，您的工作簿中总是有来自SQL Server的鲜活数据。

If you want to edit SQL Server data in Excel and save them back to SQL Server, you need to click Edit Mode on the Devart tab of the ribbon first. Otherwise, the changes you make cannot be saved to SQL Server.

如果要在Excel中编辑SQL Server数据并将其保存回SQL Server，需要先单击ribbon的Devart选项卡上的edit Mode。否则，所做的更改不能保存到SQL Server中。

After you start the Edit mode, you can edit the data as you usually do it in excel - delete rows, modify their cell values. Columns that cannot be edited in SQL Server, will have Italic font, and you cannot edit values in these columns. To add a new row, enter the required values to the last row of the table that is highlighted with green. Editing SQL Server data in Excel

在开始编辑模式后，您可以像在excel中那样编辑数据——删除行，修改它们的单元格值。无法在SQL Server中编辑的列将具有斜体字体，不能在这些列中编辑值。要添加新行，请将所需的值输入以绿色突出显示的表的最后一行。在Excel中编辑SQL服务器数据

To apply the changes to actual data in the database, click the Commit button. Or click Rollback to rollback all the changes. Please note that the changes are not saved to the database till you click Commit, even if you save the workbook.

要将更改应用到数据库中的实际数据，请单击Commit按钮。或者单击Rollback以回滚所有更改。请注意，在单击Commit之前，即使保存工作簿，也不会将更改保存到数据库中。

Also, consider this as well.

此外，还要考虑到这一点。

https://www.mssqltips.com/sqlservertip/3436/passing-dynamic-query-values-from-excel-to-sql-server/

I use these techniques in my office, and I'm moving close to 900k rows with around 24 columns, from SQL Server to Excel. It takes lest than 60 seconds to do the refresh.

我在我的办公室中使用了这些技术，从SQL Server到Excel，我正在接近900k行，大约有24列。刷新要花上60秒的时间。

#1