Dingo Dingo - 1 month ago 6
SQL Question

Looping through folder with SQL query

strQuery = _
"SELECT * FROM [Sheet1$A15:E999] " & _
"IN '" & ThisWorkbook.Path & "\Source1.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;ExtendedProperties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Sheet1$A15:E999] " & _
"IN '" & ThisWorkbook.Path & "\Source2.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Sheet1$A15:E999] " & _
"IN '" & ThisWorkbook.Path & "\Source3.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"ORDER BY A;"


Good morning,

I have one last nail to go on this coding I have and any help is much appreciated. I am gathering numerous files from a single folder and file names are different (although data order and data are same).

Question is:


  • Is it possible to get all files via the 'strQuery' without slowing down the code? How do I go on to do this? (eg: I think maybe loop but it might slow down? - see below)

  • Is it possible to get (say) 100 excel file data read at once? (although I do not know names of it?)



I can modify strQuery (via assigning it a text string) and input a loop to go through every file but I recon this would require me to create a connection for every single file rather than all at once?

Any help is appreciated!

Thanks in advance.

--

Full Code below (I didn't know where to put this in a visible manner)

Sub SqlUnionTest()

Dim strConnection As String
Dim strQuery As String
Dim objConnection As Object
Dim objRecordSet As Object, qText As String

strConnection = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;" & _
"Data Source='" & ThisWorkbook.FullName & "';" & _
"Mode=Read;" & _
"Extended Properties=""Excel 12.0 Macro;"";"


Dim sFile As String

sFile = Dir(ThisWorkbook.Path & "\*.xlsx")

Do While sFile <> ""

strQuery = _
"SELECT * FROM [Sheet1$A15:E999] " & _
"IN '" & ThisWorkbook.Path & "\" & sFile & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;ExtendedProperties='HDR=YES;'] " & _
"UNION "

sFile = Dir()

Loop

strQuery = Left(strQuery, Len(strQuery) - 7) 'to remove last UNION which is not necessary

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = objConnection.Execute(strQuery)
RecordSetToWorksheet Sheets(1), objRecordSet
objConnection.Close

End Sub

Sub RecordSetToWorksheet(objSheet As Worksheet, objRecordSet As Object)

Dim i As Long

With objSheet
.Cells.Delete
For i = 1 To objRecordSet.Fields.Count
.Cells(1, i).Value = objRecordSet.Fields(i - 1).Name
Next
.Cells(2, 1).CopyFromRecordset objRecordSet
.Cells.Columns.AutoFit
End With

End Sub

Answer

You can use the DIR() function to loop through all the .xlsx files in the folder without knowing the specific file names. If you need to weed out any files, you can place conditional testing inside the loop.

Code untested

Dim sFile As String, strQuery As String

sFile = Dir(ThisWorkbook.Path & "\*.xlsx")

Do While sFile <> ""

    strQuery = _
     "SELECT * FROM [Sheet1$A15:E999] " & _
     "IN '" & ThisWorkbook.Path & "\" & sFile & _
     "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;ExtendedProperties='HDR=YES;'] " & _
     "UNION "

    sFile = Dir()

Loop

strQuery = Left(strQuery, Len(strQuery) - 7) 'to remove last UNION which is not necessary