时间戳

首页 > 解决方案 > 使用 VBA 将数据从 SQL Server 移动到 Excel 的复杂性

sql - 使用 VBA 将数据从 SQL Server 移动到 Excel 的复杂性

问题描述

我正在尝试将数据从 SQL Server 提取到 Excel 中以填充预先存在的工作表，因此我为此编写了一个测试代码。虽然我主要测试的是建立与服务器的连接并不完美，但是当我打开连接时遇到了自动化错误错误 440。我在那部分代码中做错了什么。

到目前为止，我还没有进行任何修改，并检查以确保它编译得很好。

Sub GetPhysicalCount()
    Dim squery As String
    Dim cnLogs As New ADODB.Connection
    Dim rsData As New ADODB.Recordset
    Dim rsHeaders As New ADODB.Recordset
    Dim x As Long
    Dim y As Long
    Dim dbpath As String
    Dim dbname As String
    Dim AppExcel As Excel.Application
    Dim Workbook As Object
    Dim Worksheet As Object
    Dim strConn As String
    Dim Count As Long


    pUser = "AllenBroady"
    pPsw = "Cthulu90"
    pServer = "IMCPU_TEST"
    pCatalog = "MasterMFG"




    strConn = "Provider = SQLOLEDB;"
    strConn = strConn & "Initial Catalog= & pCatalog; & Data Source= & pServer;"
    strConn = strConn & "Integrated Security=sspi;& User ID= & pUser; & Password= & pPsw;"

    cnLogs.Open strConn

        With rsHeaders

            .ActiveConnection = cnLogs

                    .Open "SELECT * FROM MasterMFG WHERE Dept_Code = 001"

                 Do While Not rsHeaders.EOF

                    Cells(1, l_counter + 1) = rsHeaders(0)

                    l_counter = l_counter + 1

                    rsHeaders.MoveNext

                  Loop

            .Close

            End With

            With rsCount
                  .ActiveConnection = cnLogs
                    .Open "SELECT COUNT(Dept_Code)FROM MasterMFG WHERE Total <> 0"
                      Count = rsCount
                  .Close
            End With



            With rsData

            .ActiveConnection = cnLogs
                .Open "SELECT  Dept_Code, Total FROM MasterMFG WHERE Total <> 0"


               For i = 1 To Count

                    Sheet1.Range("A" & i + 1) = rsData.Fields(i)
                    rsData.MoveNext
                    Sheet1.Range("B" & i + 1) = rsData.Fields(i)
               Next i

            .Close

            End With

        cnLogs.Close

        Set cnLogs = Nothing

        Set rsHeaders = Nothing

        Set rsData = Nothing

    Sheets(1).UsedRange.EntireColumn.AutoFit

End Sub

我不断收到“自动化”错误（错误 440）

标签： sqlexcelvbasql-server-2017

解决方案

您的strConn = strConn字符串未正确连接。

您的变量没有评估，它们的名称只是直接放入字符串中。

改变，例如

 strConn = strConn & "Initial Catalog= & pCatalog; & Data Source= & pServer;"
 strConn = strConn & "Integrated Security=sspi;& UserID= & pUser; & Password= & pPsw;"

至

 strConn = strConn & "Initial Catalog=" & pCatalog & ";Data Source=" & pServer & ";"
 strConn = strConn & "Integrated Security=sspi;User ID= "& pUser; & "Password= " & pPsw & ";"

推荐阅读