View All Your SQL (ODBC) Queries At Once

If you use the ODBC driver to get data from WebTrends into Excel, then you’re using a SQL query.

If you have a lot of SQL queries in one workbook, you can use this Excel macro (”ReadSQL( )”) to dump your ODBC information into its own Excel sheet.  It will have one row per query table and the following  four columns:

1. The name of the sheet
2. The number of the query table in the sheet (it’s “1″ unless you have more than one query table in that sheet)
3. The connection string for the query table (if you are connecting to more than one WebTrends profile, it helps to see the profile’s GUID)
4. The SQL query itself

Like this:

The beauty of all this is that there’s also a “WriteSQL( )” version of the macro.   This means you can make changes to the SQL queries or the connection strings right on the worksheet, then run the “WriteSQL( )” macro.  All the queries/strings shown on the sheet will update (inside Excel) to be exactly as you edited them.  Woot!

This is great for:

• changing SQL queries (’cuz you’re  using a simple text-based Excel sheet)
• quality-checking your SQL queries (’cuz all your queries are showing on one sheet)
• changing the time periods for all your queries at once ( just use Excel Find & Replace)
• replacing one profile with another (i.e. swapping a lot of profile GUIDs at once; use Excel Find & Replace)

Here’s the Excel macro code for “ReadSQL( )” – this version works in Excel 2003 (or if you’re in Excel 2007 and using cloned sheets and modifying queries that were originally inserted in Excel 2003).  We’re working on a pure 2007 version (thanks to Mike w).


Sub ReadSQL()
     Cells.Select
     Selection.Delete Shift:=xlUp
     x = 1
     For Each WKS In Worksheets
          y = 1
          For Each Qtable In WKS.QueryTables
               Cells(x, 1).Value = WKS.Name
               Cells(x, 2).Value = y
               Cells(x, 3).Value = Qtable.Connection
               Cells(x, 4).Value = Qtable.CommandText
               x = x + 1
               y = y + 1
          Next
     Next
     Columns(1).ColumnWidth = 20
     Columns(2).ColumnWidth = 3
     Columns(3).ColumnWidth = 50
     Columns(4).ColumnWidth = 50
     Range("A:C").WrapText = True
End Sub


and here is its counterpart “WriteSQL( )”:

Sub WriteSQL()
     x = 1
     Do While Not Cells(x, 1) = ""
          Worksheets(Cells(x, 1).Value).QueryTables(Cells(x, 2).Value).Connection = Cells(x, 3).Value
          Worksheets(Cells(x, 1).Value).QueryTables(Cells(x, 2).Value).CommandText = Cells(x, 4).Value
          x = x + 1
     Loop
End Sub


CAVEATS (Warnings):

• I’m assuming you already know how to add a macro to your Excel.  Either put it in your ODBC-based workbook, or alternatively you can put it in your Personal.xls workbook and it will be available all the time.
• You should always run the ReadSQL( ) macro immediately before making any edits, so that you are working with a perfectly fresh and accurate set of information.  Don’t rely on an old sheet that you may have filled (using ReadSQL) some time ago.  You need to be sure that what  you’re modifying actually matches the current state of your queries and connection strings.
• Run the ReadSQL( ) macro when you are on the sheet where you want the information to magically appear.  Similarly, have that sheet be the active sheet when you run WriteSQL( ).
• It’s probably best to not use it in a workbook that also has REST URLs, though we think it’ll work fine.
• And of course … there’s no warranty express or implied.

Postscript:

These macros come courtesy of the fabulous Mister Peabody, who acknowledges that ”the code is ugly.”  Mister Peabody has been meaning to go back and make some improvements, but it’s been working fine, so he hasn’t.

Tags

connection string, excel, macros, ODBC, ReadSQL, SQL, WriteSQL

Somewhat Related Posts

Automate your reports with the ODBC driver

...

Miscellaneous “candy jar” post #1

...

Seven ways to annotate your reporting

...