CODEWITHSUNDEEP
sql-servervbaexcel
Mars Simpson
Mars Simpson

Reputation: 23

SQL Functionality in Excel VBA

For those who don't know, it is fairly easy to add SQL functionality to VBA macros. The following article provides the code: http://analystcave.com/excel-using-sql-in-vba-on-excel-data/

I modified this a bit (happy to provide the code) so that it outputs nicely and put it in a Sub that I can call. This saves me from having to do multiple sorts, copy paste, etc. to find specific data from a large worksheet.

Note a problem, however, when working with the workbook from an online source e.g. Gmail:

.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";"

This works fine when the file is saved to a drive, but from an online site, Excel can't connect. Any suggestions on modifying the connection string for when the file isn't saved anywhere?

Upvotes: 1

Views: 480

Answers (1)

Mars Simpson
Mars Simpson

Reputation: 23

For anyone who's interested, this code (based on the code from Analyst Cave) works great for using SQL in VBA. Save the following as a Sub:

Option Explicit

Sub QuerySQL(result_location As Range, query As String)

    Dim ResultWS As Worksheet
    Set ResultWS = ThisWorkbook.Sheets("Query Results")
    ResultWS.Cells.ClearContents

    If query = "" Then Exit Sub

    Dim cn As Object, rs As Object
    'Add to the workbook a database connection with itself
    'Note other ConnectionString could be used to access a variety of media
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With

    'Build and execute the SQL query
    Set rs = cn.Execute(query)
    If rs.EOF = True Then Exit Sub

    'Print column labels
    Dim i As Long, j As Long
    For i = 0 To rs.Fields.Count - 1
        result_location.Offset(0, i).Value = rs.Fields(i).Name
    Next i

    'Print column contents
    i = 0
    Do
        For j = 0 To rs.Fields.Count - 1
            result_location.Offset(i + 1, j).Value = rs.Fields(j).Value
        Next j

        rs.MoveNext
        i = i + 1
    Loop Until rs.EOF

    'Close the connections
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Sub

To use it, simply do the following:

Dim myQuery As String
myQuery = "SELECT * FROM [Sheet2$]"
Call QuerySQL(ThisWorkbook.Sheets("Sheet1").Range("A1"), myQuery)

It uses MS Access style SQL. The above will look to Sheet2 as the table and print the result starting in A1 on Sheet1.

Upvotes: 1

Related Questions