petergensler petergensler - 1 year ago 91
SQL Question

Query Large Excel File using VBA/SQL

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:

Order ID|Sales|ProductSubCategory|Region
234324 3400 BookCases South
234345 2700 Tables North

This is the main criteria for the SQL Query:

  • Bookcases Chairs

  • Chairmats

  • Office Furnishings

  • Tables

I am relatively new to using VBA, and especially SQL within VBA, so any help would be greatly appreciated. Thanks for your time.

Answer Source
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 :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download