Using SQL queries to read Excel files in macros

I’m doing my Masters in Geographic Information Systems at U of T and found the need to do this in my research.

You can treat Excel sheets as tables and perform SELECTs and JOINS like you would against a regular database. The language is Visual Basic (VBA to be exact). You can put the following code inside a Macro Sub. NAICS_CODE (Row 1) is a heading of one of the columns in the Results worksheet.

I did need to add the Microsoft ActiveX Data Objects 6.0 Library by going to Tools -> References in the VBA Project (the program that opens up when you edit a macro).

    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As New ADODB.Recordset

    With cn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "Data Source=C:\Users\zarar\Documents\researchdata.xls;" & _
            "Extended Properties=Excel 8.0;"
        .Open
    End With
    
    'Store all naicsCodes in an array
    Dim naicsCodes() As String
    'Get all distinct NAICS codes
    Set rs = cn.Execute("SELECT DISTINCT NAICS_CODE FROM [Results$]")
    Dim i
    i = 0
    Do Until rs.EOF
        ReDim Preserve naicsCodes(i + 1)
        naicsCodes(i) = rs.Fields("NAICS_CODE")
        i = i + 1
        rs.MoveNext
    Loop
    rs.Close
    cn.Close
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s