ADO to Union data from Excel and data from Text file

[lovallee]

Good day,

Question:
Using ADO (via VBA), is it possible for one single SQL query to simultaneously connect to both an Excel file and a .txt file? I would then do a UNION ALL operation to merge both sets of data.

Additional details:
I use ADO (via VBA) to connect to an Excel file as follow.
It works quite fine to connect to two or more Excel files with the same query by using an SQL string such as:

SELECT * FROM [Sheet1$] UNION SELECT * FROM [Sheet1$] IN 'C:\ADOTest.xls' 'Excel 8.0;'

Excerpt only

MainSourceFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & MainSourceFile & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

szSQL = Range("SQLcommand").Value2
   
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
   
rsCon.Open szConnect
rsData.Open szSQL, rsCon, adCmdText

Thank you!

Louis

 

[lovallee]

Solved: ADO to Union data from Excel and data from Text file

I finally found it.
Default "extended properties" are set in the connection string.
Specific files "extended properties" can be set for each file accessed by the query directly in the SQL string.


Example where the default "Extended properties" are set to Excel 8.0 and file specific properties are set to text file:

Code excerpt

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & MainSourceFile & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

SQL string:

SELECT * FROM Table1InMyExcelFile 'File properties set in the connection string
UNION ALL
SELECT * FROM [Text;Database=\\MyFolder\;HDR=YES;FMT=Delimited].[MyCSVFile.csv]

Louis

 

[Fazza]

Good work, Louis. Thanks, Fazza