使用Excel VBA运行SQL查询

Question

使用Excel VBA运行SQL查询

52

我对SQL和VBA都比较陌生。我已经编写了一个SQL查询，希望能够从Excel工作簿中的VBA子程序中调用并运行该查询，然后将查询结果带入工作簿。我在网上（stackoverflow和其他地方）找到了一些子程序，声称可以实现这一点，但由于它们没有解释，我很难理解。例如，这是我在网上找到的一个子程序：

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
                  "Initial Catalog=MyDatabaseName;" & _
                  "Integrated Security=SSPI;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM Table1;")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

End Sub

首先，这个会起作用吗？第二，我需要在 sub 中替换什么（看起来像 provider、data source、initial catalog 等），我在哪里找到要替换的信息？

希望我的问题不太令人困惑，感谢您的帮助！

- Sam

1

代码的每个“部分”上面都有注释。你到底不理解什么？实际上，这段代码会创建一个与数据库的连接，对该连接执行查询，将结果加载到ResultSet中，将ResultSet复制到以“A1”为起始范围的表中... - T I

你使用的是哪种类型的SQL数据库？MS SQL？MySQL？SQLite？这将决定VBA代码是否能够正常工作。 - Michael

谢谢你的回复，Michael。当我尝试运行它时，它会突出显示Dim conn As ADODB.Connection，并说用户定义的类型未定义。我在谷歌上搜索了一下，看起来我需要使用“类型语句”。你有这方面的经验吗？在这种情况下，它会是什么样子？ - Sam

其次，您的VBA中是否已检查“Microsoft ActiveX Data Objects 2.8 Library”作为引用？ - Michael

好的，我认为你的问题来自我在下面第一张图片中展示的引用。确保倒数第二个复选框被选中（ActiveX），然后尝试运行它。你应该能够通过"Dim conn AS ADODB.Connection"这一行。接下来，你需要在ConnectionString中设置典型的SERVER、UESRNAME、PASSWORD和DATABASE。最后，你需要创建你的查询。 - Michael

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Michael · Accepted Answer

以下是我目前用于从 MS SQL Server 2008 中提取数据到 VBA 的代码。您需要确保您拥有正确的 ADODB 引用 [VBA 编辑器->工具->引用]，并确保选中了 Microsoft ActiveX Data Objects 2.8 Library，这是被选中的倒数第二行（我正在使用 Windows 7 上的 Excel 2010；您可能会有略微不同的 ActiveX 版本，但它仍将以 Microsoft ActiveX 开头）：

连接到 SQL 所需的引用

连接远程主机和用户名/密码的 MS SQL 子模块

Sub Download_Standard_BOM()
'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
   'Make sure to change:
       '1: PASSWORD
       '2: USERNAME
       '3: REMOTE_IP_ADDRESS
       '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Data Source=REMOTE_IP_ADDRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DATABASE"

    'Opens connection to the database
    cnn.Open ConnectionString
    'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

    'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT TOP 10 * FROM tbl_table"

    'Performs the actual query
    rst.Open StrQuery, cnn
    'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
End Sub