I am trying to write some SQL code within VBA to execute a query on a relatively large excel file(500,000 lines), where I would like to find the total sales for these given subcategories:
My data looks like this:
234324 3400 BookCases South
234345 2700 Tables North
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source='C:\Path\To\Workbook.xlsm';" _ & "Extended Properties=""Excel 8.0;HDR=YES;"";" strSQL = "SELECT * FROM [Sheet1$]"
You can treat excel files like data sources like you would an access file, and query it that way :)