Click here to Skip to main content
14,699,879 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 7: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 :)
   

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