SQL Left join 如何做到

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

chenhh803

你用立即窗口看一下strsql的结果是不是某些地方少了空格

chenhh803

1. Sub testLeftJoin()
   
2. Dim objcn As Object
   
3. 
   
4. Dim objrs As Object
   
5. Dim strsql As String
   
6. Dim strsql2 As String
   
7. Dim strsql3 As String
   
8. Set objcn = CreateObject("ADODB.Connection")
   
9. Set objrs = CreateObject("ADODB.RecordSet")
   
10. 
    
11. 
    
12. strPath = ThisWorkbook.FullName
    
13. objcn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
    
14.         & "Extended Properties=Excel 12.0;" _
    
15.         & "Data Source=" & strPath
    
16. 
    
17. 
    
18. strsql2 = "select ID,NAME from [sheet1$]"
    
19. strsql3 = "select ID,NAME from [sheet1$]"
    
20. 
    
21. strsql = "select a.ID,b.NAME from (" & strsql2 & ") a left join (" & strsql3 & ") b  " _
    
22.        & "on a.ID=b.ID "
    
23.      
    
24. objrs.Open strsql, objcn, 1, 3
    
25. 
    
26. Cells.ClearContents
    
27. 
    
28. Range("a2").CopyFromRecordset objrs
    
29. End Sub
    

复制代码

kuong2211

感謝指導，問題已經解決了。