As I’ve mentioned before, these days I use Excel more and more for developing and testing SQL code. As part of that I often compare of sets of output from SQL. And as part of that I sometimes I find it useful to pivot multiple worksheets.
For example, I just finished a project of translating a query from one data warehouse to another. The new database has a completely different schema than the old – new tables, new fields, new behaviors. My goal was to develop a query that returned the same results from the new database as those from the old.
To compare the outputs, I created two tables (listobjects) in a single workbook. The first table had a connection to the old data warehouse and uses the old query as its Command Text. The second table is connected to the new data warehouse, and was where I’d test the SQL I was developing.
Especially at first, there were quite a few differences in the output of these two queries in these two tables. Comparing the outputs in a pivot table let me see these differences clearly, both in summary and in detail.
Here’s a very simple example using my trusty pie data. In this example I have two different tables on two sheets with slightly different pie orders. Here’s the output from data warehouse 1…
and here it is from data warehouse 88b…
I conveniently placed differences in the Quantity column near the top, so you may be able to just pick them out by eye. And you may even have caught the one date field discrepancy. However, after combining the two tables into one, adding a “Source” column and then pivoting, the differences become easy to pick out, especially with a little conditional formatting:
In the pivot above, “2”s in the Grand Total column represent all the records where the two queries returned the same results. The “1”s point to the discrepancies.
This is a flexible and powerful comparison method. Benefits include:
- You can quickly add or subtract fields from the pivot to pinpoint the differences.
- You can change the orders of the fields.
- If you add subtotals you can then double-click on those with disrepancies to drill down to just those results.
For a while I created these combined source tables manually, just pasting the two sets of results together, adding a column “Source” column with “DW_1” and “DW_88b.” This worked fairly well, but after several times it cried out for automation.
The VBA below keys off of selected sheets in a workbook. Just select the ones you want to pivot and then run the code. Here you can see that both sheets are selected, and I’ve added the “Pivot Multiple Sheets” macro to the tab’s right-click menu (with MenuRighter, of course).
The code first collects all the data necessary for the connection and then closes the source workbook. (I did this to avoid memory leaks or whatever it is that makes things go wonky if the workbook is open at the same time I’m creating a connection to it.) It uses that data to create the Source and SQL strings. The SQL is just a series of SELECTS, one for each selected worksheet, connected with UNION ALLs.
The newly created worbook contains a table with the connection to the source workbook and a pivot table pointed at that table. The table’s “Source” field becomes a column in the pivot table, containing the names of the two or more worksheets. The rest of the table columns become pivot table row fields. The connection in this workbook is live, so that if you make changes to the source they will appear in this workbook once you refresh the data and pivot:
Sub Pivot_Multiple_Sheets()
Dim wbToPivot As Excel.Workbook
Dim SheetsToPivot As Excel.Sheets
Dim SourceFullName As String
Dim SourceString As String
Dim wbWithPivot As Excel.Workbook
Dim wsWithQueryTable As Excel.Worksheet
Dim SheetsToPivotCount As Long
Dim SheetsToPivotNames() As String
Dim qt As Excel.QueryTable
Dim i As Long
Dim SqlSelects() As String
Dim sql As String
Dim pvt As Excel.PivotTable
Dim pvtField As Excel.PivotField
If ActiveWorkbook Is Nothing Then
MsgBox "No active workbook."
Exit Sub
End If
Set wbToPivot = ActiveWorkbook
If Not wbToPivot.Saved Then
MsgBox "Please save this workbook before running." & vbCrLf & _
"Workbook will be closed by this utility" & _
"after the process is completed."
Exit Sub
End If
'This code acts on the Selected Sheets
Set SheetsToPivot = wbToPivot.Windows(1).SelectedSheets
If SheetsToPivot.Count = 1 Then
MsgBox "Please select two or more worksheets (no charts)."
Exit Sub
End If
SheetsToPivotCount = SheetsToPivot.Count
For i = 1 To SheetsToPivotCount
If Not TypeName(SheetsToPivot(i)) = "Worksheet" Then
MsgBox "Please select two or more worksheets (no charts)."
Exit Sub
End If
Next i
SourceFullName = wbToPivot.FullName
ReDim SheetsToPivotNames(1 To SheetsToPivotCount)
For i = 1 To SheetsToPivotCount
SheetsToPivotNames(i) = SheetsToPivot(i).Name
Next i
'Change Selection to only one sheeet
SheetsToPivot(1).Select
'Close the source workbook before creating the new one and its connections
'Save it so not prompted
wbToPivot.Close True
Set wbWithPivot = Workbooks.Add
'Delete any extra worksheets
For i = wbWithPivot.Worksheets.Count To 2 Step -1
Application.DisplayAlerts = False
wbWithPivot.Worksheets(i).Delete
Application.DisplayAlerts = True
Next i
Set wsWithQueryTable = wbWithPivot.Worksheets(1)
wsWithQueryTable.Name = "Data Table"
'Don't know why this is needed, but otherwise .CommandText line below fails
wsWithQueryTable.Activate
'I got rid of a lot of fields in connection - still seems to work
SourceString = "ODBC;DSN=Excel Files;DBQ=" & SourceFullName
'Create an array of SELECT statements
ReDim SqlSelects(1 To SheetsToPivotCount)
For i = 1 To SheetsToPivotCount
SqlSelects(i) = "SELECT" & vbCrLf & _
"'" & SheetsToPivotNames(i) & "' as Source," & vbCrLf & _
"Sheet" & i & ".*" & vbCrLf & _
"FROM" & vbCrLf & _
"`" & SourceFullName & "`.[" & SheetsToPivotNames(i) & "$] AS Sheet" & i
Next i
'Connect the SELECTS with UNION ALL
For i = LBound(SqlSelects) To UBound(SqlSelects) - 1
sql = sql & SqlSelects(i) & vbCrLf & "UNION ALL" & vbCrLf
Next i
sql = sql & SqlSelects(i)
Set qt = wsWithQueryTable.ListObjects.Add(SourceType:=0, Source:=SourceString, Destination:=wsWithQueryTable.Range("$A$1")).QueryTable
With qt
.CommandText = sql
.ListObject.DisplayName = "tbl" & Format(Now(), "yyyymmddhhmmss") & Right(Format(Timer, "#0.00"), 2)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'I like it to preserve the widths the first time it's run, and below turn it to false
.AdjustColumnWidth = True
.Refresh BackgroundQuery:=False
.AdjustColumnWidth = False
End With
wbWithPivot.Worksheets.Add
With ActiveSheet
.Name = "Pivot"
Set pvt = .Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=qt.ListObject.Name).CreatePivotTable(TableDestination:=.Range("A1"))
pvt.AddDataField Field:=pvt.PivotFields("Source"), Function:=xlCount
With pvt.PivotFields("Source")
.Orientation = xlColumnField
.Position = 1
End With
For Each pvtField In pvt.PivotFields
If pvtField.Name <> "Source" Then
pvtField.Orientation = xlRowField
pvtField.Position = pvt.RowFields.Count
End If
Next pvtField
End With
End Sub
To use this code put it in your Personal.xlsb or any workbook besides the one with the source data.
This code could use some more error-checking. For example, if the two sheets have a different number of columns. Even more important is the addition of whatever kind of general error handling you use so you exit gracefully from bad connection strings and other such inevitable problems.
Speaking of bad connection strings, you may notice that I’ve ditched the Default Directory, DriverId, BufferSize, MaxPageTimeOuts and whatnot from the connection. I did that to see if it worked. It did, so I never added them back. I see that they reappear in the connection properties for the table:
I ran this code in Excel 2010 and 2013. I don’t know how portable this code is to other Excel versions. I also don’t know if you’ll have performance issues if you have the source and pivot workbooks open at the same time.
If you’re interested in this topic be sure to take a look at Kirill Lapin’s method, posted on Contextures. His method keeps the source and the pivot table in one workbook, deleting the connection in between refreshes of the pivot table. I think Kirill’s method is nice for more traditional pivot table use where you want to merge different data sets with the same format, e.g., eastern and western sales regions.
I like my method because it requires no setup for the source workbook, keeps a refreshable connection and arranges the pivot table for comparison.
I’d love to hear anybody’s opinion on the stability of this method, i.e., when opening both the source and the connected data at the same time. Also, I’m curious if this code works in other versions besides 2010 and 2013. These are areas where my knowledge is pretty piecemeal, so any help would be appreciated.
