Querying External Data in Excel

Change the SQL with VBA

The CommandText Property

The CommandText property holds the SQL statement that defines the table in an External Data table.  This property holds a String data type which means it can be changed or manipulated like any other text.

Changing the SQL

Assume you have an SQL statement like this:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.City
FROM `C:\Program Files\Microsoft Office\Office\Samples\Northwind`.Customers Customers
WHERE (Customers.City='Berlin')

Further assume that you don't want to filter on Berlin anymore, but rather want to return the records where the city is London.  You can rerun the Wizard and change the criteria in the Filter Data dialog.  You can also open the query in MSQuery, if you saved it or your SQL is too complex for the Wizard, and change the criteria there.  Of course this section isn't about the user interface, but VBA.  To change the SQL in VBA, you manipulate the string like you would any other.  To change the above SQL, you might have a line of code like this:

Sheet1.QueryTables(1).CommandText = _
     Replace(Sheet1.QueryTables(1).CommandText, _
     "WHERE (Customers.City='Berlin')", _
     "WHERE (Customers.City='London')")

The Replace function is not available in Excel versions prior to 2000.  You may think that replacing the entire WHERE clause is a little overkill.  Why not just replace Berlin with London?  Well, you're right, it is overkill.  Generally, I include as much of the text as I can in the Replace function to avoid problems.  If, for example, your WHERE clause filters on value that also happens to be a string located somewhere else in the SQL, you will be replacing more than you bargained for.  By including the WHERE statement in the Replace function, I can be confident that I'm replacing what I want to replace.

Only you will know the proper amount of text to replace.  If you have a long or complex WHERE clause, you may not want to replace the whole thing.  Know the risks and act accordingly.  Whenever you change the SQL in VBA, you have to call the Refresh method or the table will not show the updated results.

Change the Database Location

The Connection Property

The Connection property holds the connection string.  Changing the connection string is similar to changing the SQL described above.  Namely, it's just a string that can be manipulated with whichever string manipulation techniques are at your disposal.

Change the Database

Assume the connection string looks like this:

ODBC;DSN=MS Access 97 Database;DBQ=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;DefaultDir=C:\Program Files\Microsoft Office\Office\Samples;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

The DBQ element of the string identifies the database.  Now further assume that there are many External Data tables in a workbook with similar connection strings.  If you were to move Northwind.mdb to a different directory, this connection string would no longer work.  Here's an example that changes the connection string to point to the Northwind.mdb in it's new location (C:\NewFolder\Northwind.mdb in this example.)

Sub ChangeConn()

     Dim qt As QueryTable
     Dim Wsh As Worksheet
     Dim OldLoc As String, OldPath As String
     Dim NewLoc As String, NewPath As String
     Dim LastSlash As Long
     Const Ext As String = ".mdb"

     OldLoc = "C:\Program Files\Microsoft Office\Office\Samples\Northwind"
     NewLoc = "C:\NewFolder\Northwind"

     LastSlash = InStrRev(OldLoc, "\", , vbTextCompare)
     OldPath = Left(OldLoc, LastSlash - 1)

     LastSlash = InStrRev(NewLoc, "\", , vbTextCompare)
     NewPath = Left(NewLoc, LastSlash - 1)

     For Each Wsh In ThisWorkbook.Worksheets
          For Each qt In Wsh.QueryTables
               qt.Connection = Replace(qt.Connection, OldLoc & Ext, NewLoc & Ext)
               qt.CommandText = Replace(qt.CommandText, OldLoc, NewLoc)
               qt.Connection = Replace(qt.Connection, OldPath, NewPath)
               qt.Refresh
          Next qt
     Next Wsh

End Sub
 

This simply loops through each Worksheet and then through each QueryTable and uses the Replace function to manipulate the strings.  If you were to look at the CommandText property, you would likely see that the FROM clause contains the full path to the database.  Therefore, if you change the database in the Connection property, you must also change it in the CommandText property.  Also, be sure to use the Refresh method so that the information on the worksheet is updated.

In some cases, the file extension is not present in the SQL string.  In the example above, ...Northwind.mdb is replaced in the Connection, but just ...Northwind is replaced in the CommandText.  It pays to inspect these properties before writing code to change them.  Also, the Connection string may contain a DefaultDir element that points to a path.  You don't necessarily have to change the DefaultDir, but if the directory that exists there doesn't exist on your hard drive, you will get an error.  That's why the code above does a second Replace on the Connection that looks for and replaces just the path.