SQL Functionality in Excel VBA

Question

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?

Can you provide a sample ConnectionString in that case? You may need to manipulate it for it to work. — Wagner DosAnjos
– Wagner DosAnjos, Commented Jan 13, 2017 at 15:51
You cannot use this approach if the workbook isn't saved to disk. Best you could do is save a temporary copy to disk and query that. — Tim Williams
– Tim Williams, Commented Jan 13, 2017 at 16:09
@wdosanjos The current connection string uses ThisWorkbook.Path and .Name, but what to do if the file isn't saved and thus doesn't have a path? — Mars Simpson
– Mars Simpson, Commented Jan 13, 2017 at 16:35
Microsoft.ACE.OLEDB.12.0 requires a file, so until you save the file it will not work. — Wagner DosAnjos
– Wagner DosAnjos, Commented Jan 13, 2017 at 16:37
@wdosanjos Okay, so there's no way to point to the file in memory or anything like that? — Mars Simpson
– Mars Simpson, Commented Jan 13, 2017 at 16:40

Mars Simpson · Accepted Answer · 2017-01-13 15:48:04Z

1

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.

answered Jan 13, 2017 at 15:48

Mars Simpson

231 silver badge4 bronze badges

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

3 Comments

YowE3K Over a year ago

This doesn't appear to address the issue raised in the question. (And if you used result_location.Offset(1, 0).CopyFromRecordset rs to store the data, you would improve the speed dramatically.)

Mars Simpson Over a year ago

Just thought I'd add it as there are others asking how to add SQL to Excel and it works perfectly for me.

YowE3K Over a year ago

It shouldn't be added as an answer if it doesn't answer the question - it should be added as part of the question, but in this case it doesn't really need to be as you already had a good MCVE.

Collectives™ on Stack Overflow

SQL Functionality in Excel VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related