Dingo Dingo - 1 month ago 19
SQL Question

Recordset Query - Excel

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


Hi,

I have the above inside a Module in VBA (the code itself is sourced from here ). My question is, my columns in each file starts from row 15 and data goes down from row 16. How do I make it so that each file, it would look to UNION from row 15?

Thanks in advance!

Answer

With Excel workbook SQL queries via ADO or DAO, you can specify the regions of a worksheet by setting a range in the fashion: [Sheet$A1:Z2]. First find the last named column (recall for UNION they must be same lengths and types) and add rows sufficient for valid querying. Below uses Z999:

strQuery = _
    "SELECT * FROM [Sheet1$A15:Z999] " & _
    "IN '" & ThisWorkbook.Path & "\Source1.xlsx' " & _
    "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
    "UNION " & _
    "SELECT * FROM [Sheet1$A15:Z999] " & _
    "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:Z999] " & _
    "IN '" & ThisWorkbook.Path & "\Source3.xlsx' " & _
    "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
     "ORDER BY A;"