在 Excel 中运行 VBA 脚本时出现自动化错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2528152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Automation Error upon running VBA script in Excel
提问by brohjoe
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
采纳答案by pointer
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=81Works for me every time.
从您的帖子（和您的）回复来看，您的连接字符串似乎有问题？连接字符串可能会引起真正的头痛。我知道。尝试 Robert Gelb 的方法：http://www.vbrad.com/article.aspx? id=81 每次都对我有用。
