|
有兩個不同的EXCEL工作簿,請問如何LEFT JOIN在另外一個工作簿上顯示呢?
Sub testLeftJoin()
Dim objcn As Object
Dim objcn2 As Object
Dim objrs As Object
Dim strsql As String
Dim strsql2 As String
Dim strsql3 As String
Set objcn = CreateObject("adodb.connection")
Set objcn2 = CreateObject("adodb.connection")
Set objrs = CreateObject("adodb.recordset")
Cells.ClearContents
objcn.Open "provider = microsoft.ace.oledb.12.0;extended properties =excel 12.0;" _
& "data source=C:\Users\User\Desktop\test.xls;"
objcn2.Open "provider = microsoft.ace.oledb.12.0;extended properties =excel 12.0;" _
& "data source=C:\Users\User\Desktop\Testt.xlsx;"
strsql2 = "select ID,NAME from [sheet1$]"
strsql3 = "select ID,NAME from [sheet1$]"
strsql = "select a.ID,b.NAME from (" & strsql2 & ") a left join (" & strsql3 & ") b " _
& "on a.ID=b.ID ;"
objrs.Open strsql, objcn2, objcn<------在這裡報錯了.
Range("a2").CopyFromRecordset objrs
End Sub |
|