在Excel中运行VBA脚本时出现自动化错误

I'm getting an Automation error upon running VBA code in Excel 2007. I'm attempting to connect to a remote SQL Server DB and load data to from Excel to SQL Server.

我在Excel 2007中运行VBA代码时遇到自动化错误。我正在尝试连接到远程SQL Server数据库并将数据从Excel加载到SQL Server。

The error I get is,

我得到的错误是，

"Run-time error '-2147217843(80040e4d)': Automation error".

“运行时错误'-2147217843（80040e4d）'：自动化错误”。

I checked out the MSDN site and it suggested that this may be due to a bug associated with the sqloledb provider and one way to mitigate this is to use ODBC. Well I changed the connection string to reflect ODBC provider and associated parameters and I'm still getting the same error.

我检查了MSDN站点，它表明这可能是由于与sqloledb提供程序相关的错误，缓解这种情况的一种方法是使用ODBC。好吧，我更改了连接字符串以反映ODBC提供程序和相关参数，我仍然得到相同的错误。

Here is the code with ODBC as the provider:

以下是使用ODBC作为提供程序的代码：

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range



Public Sub loadData()
     'This was set up using Microsoft  ActiveX Data Components version 6.0.

     'Create ADODB connection object, open connection and construct the connection string object.
     Set cnt = New ADODB.Connection
     cnt.ConnectionString = _
     "Driver={SQL Server}; Server=onlineSQLServer2010.foo.com; Database=fooDB Uid=logonalready;Pwd='helpmeOB1';"

    cnt.Open

    On Error GoTo ErrorHandler

    'Open Excel and run query to export data to SQL Server.

     strSQL = "SELECT * INTO SalesOrders FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',   & _
    "'Data Source=C:\Database.xlsx; Extended Properties=Excel 12.0')...[SalesOrders$]"

    cnt.Execute (strSQL)

    'Error handling.
ErrorExit:
     'Reclaim memory from the connection objects
     Set rst = Nothing
     Set cnt = Nothing

   Exit Sub

ErrorHandler:
   MsgBox Err.Description, vbCritical
   Resume ErrorExit


   'clean up and reclaim memory resources.
    cnt.Close
    If CBool(cnt.State And adStateOpen) Then
    Set rst = Nothing
    Set cnt = Nothing

    End If

End Sub

1 个解决方案

#1

From the replies on your post (and yours) it seems your connectionstring is faulty? Connection strings can cause a real headache. I know. Try Robert Gelb's approach: http://www.vbrad.com/article.aspx?id=81 Works for me every time.

从你的帖子（和你的）的回复看来你的连接字符串是错误的？连接字符串可能会导致真正的头痛。我知道。试试Robert Gelb的方法：http：//www.vbrad.com/article.aspx？id = 81每次都适合我。