Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Can i combine two excel workbook in one query ?

Like: "UNION ALL" "JOIN" "LEFT JOIN"

I want combine EXCELFILE2 Worbook with EXCELFILE1
Sheets names "Rapor"

select F1 from [Rapor$] UNION ALL select F1 from [Rapor$] 


What I have tried:

Dim con As Object, RS As Object, sorgu As String
 Set con = CreateObject("Adodb.Connection")
  Set con1 = CreateObject("Adodb.Connection")
    Set RS = CreateObject("Adodb.RecordSet")
 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & "C:\EXCELFILE1.xlsx" & ";extended properties=""excel 12.0;hdr=NO"""
        sorgu2 = "select F1 from [Rapor$]"
     
        RS.Open sorgu2, con, 1, 3
        Cells(1, "O").CopyFromRecordset RS
       RS.Close
            
       con.Close
Posted
Updated 24-Sep-20 6:07am
Comments
ZurdoDev 22-Sep-20 12:12pm    
I don't know if you can, but have you tried it?

1 solution

 Set con = VBA.CreateObject("adodb.Connection")
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""Excel 12.0;hdr=NO"""
 
SORGU1 = "Select * from " & ThisWorkbook.FullName & ".[Sayfa4$] "
sorgu2 = "Select Rapor$.[Fatura No] AS FISFATNO2 from D:\GidenFatura.xlsx.[Rapor$] "
sorguEX = "SELECT * FROM (" & SORGU1 & ")A LEFT JOIN (" & sorgu2 & ") B ON A.FISFATNO=B.FISFATNO2"
 Set RS = con.Execute(sorguEX)
 For X = 1 To RS.Fields.Count
     Cells(1, X + 20) = RS.Fields(X - 1).Name
   Next
 Range("U2").CopyFromRecordset RS
 RS.Close
con.Close


with this codes i worked :)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900