Recordset Query - Excel

Question

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!

What's in the first 15 rows? Are the headers in row 1, or row 15? — Comintern
– Comintern, Commented Oct 19, 2016 at 19:27
Headers on row 15 - First 14 have some KPIs regarding the data below (standard for every file) — Dingo
– Dingo, Commented Oct 19, 2016 at 19:35
AFAIK to query Excel worksheets like this they need to be formatted as data tables, i.e. with column headings in row 1 and the data starting in row 2, and consistent data types throughout a column; these 14 rows of KPIs are possibly going to throw off column type inference. I don't think you can do this. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 19, 2016 at 19:39
Depending on what comes in the intervening rows, you could set the HDR property in the connection string to NO, and add a WHERE clause that would catch the rows above the header. It would mainly depend on the structure of the header and the data though. — Comintern
– Comintern, Commented Oct 19, 2016 at 19:42
Could you kindly help me out with the where clause? I've used it in the context of column...eg: Where [Customer Name] = "Max" But to capture 14 rows using where is bit confusing! Thanks — Dingo
– Dingo, Commented Oct 19, 2016 at 20:12

Parfait · Accepted Answer · 2016-10-19 20:21:24Z

2

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;"

answered Oct 19, 2016 at 20:21

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dingo Over a year ago

Appreciate your help :)

Collectives™ on Stack Overflow

Recordset Query - Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related