求教VBA SQL

kuong2211

有兩個不同的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