VBA ADO连接到.xlsx文件 [英] VBA ADO connection to .xlsx file

问题描述

我正在尝试使用ADO连接从封闭的Excel 2007工作簿（.xlsx）复制数据。

我可以使用连接字符串。但是，当我尝试在Recordset中打开Command（第二行）时，出现了自动化错误。

在下面的代码中可能不清楚，所以： / p>

wsSummary是工作表对象
strSourceFile是包含我要从中复制目标数据的字符串（例如Template.xlsx）

  strSourceFile = wsSummary.Cells（nFirstRow + 4，7）
 strSheetSource = Sheet1 
 strSQL = SELECT *来自[& strSheetSource和] 
 
设置dbConnection =新的ADODB.Connection 
和dbConnection 
 .Provider = Microsoft.ACE.OLEDB.12.0; 
 .connectionString =数据源=& strPOINTDataPath& strSourceFile& _ 
;扩展属性= Excel 12.0 Xml; HDR = NO; IMEX = 1; 
 .ConnectionTimeout = 40 
。打开
以
结尾如果dbConnection =然后转到ToTo ErrorText 
 
 Set cmd = New ADODB.Command 
 with cmd 
 .ActiveConnection = dbConnection 
 .CommandText = strSQL 
结尾为
 
 Set rs = New ADODB.Recordset 
 With rs 
 .ActiveConnection = dbConnection 
 .CursorLocation = adUseClient 
 .CursorType = adOpenDynamic 
 .LockType = adLockOptimistic 
。打开cmd 
以
  




解决方案

我认为您错过了 $个字符您的SQL语句。尝试将相应的行更改为这一行：

  strSQL = SELECT * FROM [& strSheetSource和 $] 
  

或更改 strSheetSource变量

  strSheetSource = Sheet1 $ 
  




I am trying to copy data from a closed Excel 2007 workbook (.xlsx) using an ADO connection.

I have the connection string working. But I get an automation error when I try to open the Command in the Recordset (Second to last line).

This may not be clear in the below code so:

"wsSummary" is a worksheet object "strSourceFile" is a string with the target data I need to copy from (e.g. Template.xlsx)

strSourceFile = wsSummary.Cells(nFirstRow + 4, 7)
strSheetSource = "Sheet1"
strSQL = "SELECT * FROM [" & strSheetSource & "]"

Set dbConnection = New ADODB.Connection
With dbConnection
    .Provider = "Microsoft.ACE.OLEDB.12.0;"
    .connectionString = "Data Source=" & strPOINTDataPath & strSourceFile & _
                        ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
    .ConnectionTimeout = 40
    .Open
End With
If dbConnection = "" Then GoTo ErrorText

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = dbConnection
    .CommandText = strSQL
End With

Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = dbConnection
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open cmd
End With

解决方案

I think you missed $ character in your SQL statement. Try to change appropriate line into this one:

strSQL = "SELECT * FROM [" & strSheetSource & "$]"

or change strSheetSource variable into this:

strSheetSource = "Sheet1$"

这篇关于VBA ADO连接到.xlsx文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！