sql - Excel VBA - 通过sql查询从不同目录中的另一个工作簿导入数据（无Open.file方法）

Question

我无法从外部工作簿收集数据并进行复制。我可以在同一个工作簿中完成；但我无法写下路径或丢失的东西。该问题可能与shRead().

Sub ReadFromWorksheetADO()

Dim connection As New ADODB.connection
Dim sourceFile, wFile As String
Dim shRead, shWrite As Worksheet

Set shWrite = ThisWorkbook.Worksheets("D2")
sourceFile = "\\X\Y\Z\3_6\deneme6.xlsm"

connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source='\\X\Y\Z\3_6\deneme6.xlsm';" & _
        "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"

' Not sure need this definition -> wFile = ThisWorkbook.Path & Application.PathSeparator & "CurrentWB.xlsm"'

shRead = "[Excel 12.0;HDR=YES;DATABASE=" & sourceFile & "]"

Dim query As String
query = "Select * from [" & shRead.Name & "$] Where Item='1' "
'Alternative one -> query = "Select * from [R3.6$] Where Item='1' "'

Dim rs As New ADODB.Recordset
rs.Open query, connection

shWrite.Range("A2").CopyFromRecordset rs
connection.Close

End Sub

标签： sqlexcelvbaimportworksheet