Pivot query update with Excel

Update of an Excel database query to update pivots

A database query (e.g. ODBC database connection to a MySQL db) to update a pivot is a very powerful mechanismn. Usually to refresh the pivot the user has to trigger it manually. To refresh several files automatically I used the following scripts as a combination of windows batch file and Excel VB script.

This is the batch code I used:

@echo off
title Batch Run to update excel files
echo Set Batch Mode
set BatchMode=TRUE
REM set the path to Excel
set path=%path%;"C:\Program Files (x86)\Microsoft Office"\Office12\
echo Move to working directory
REM change to the drive where the excel files are stored
N:
cd "<patch to the directory store all the Excel files>"
REM work on all files starting with _*.xlsm
for %_% %%f in (_*.xlsm) do (
    echo Work on file: %%f
    EXCEL.EXE /e ".\%%f"
)

Within the Excel I used the following standard visual basic code to enable the automated update in case the evironment variable BatchMode=TRUE. If the variable is set, then the Excel will automate update the pivots and finally store the file under a new file name. If the variable is not set, then the Excel file open without any database query update.

Important is to place this code in the “ThisWorkbook” section, otherwise the function Workbook_Open will not automatically be called.

Private Declare Function GetEnvVar Lib "kernel32" Alias "GetEnvironmentVariableA" _
    (ByVal lpName As String, ByVal lpBuffer As String, ByVal nSize As Long) As Long

Function GetEnvironmentVariable(var As String) As String
    Dim numChars As Long
    GetEnvironmentVariable = String(255, " ")
    numChars = GetEnvVar(var, GetEnvironmentVariable, 255)
End Function

Private Sub Workbook_Open()
    '   we just want to update automated if batch mode is set
    If InStr(1, GetEnvironmentVariable("BatchMode"), "TRUE", vbTextCompare) Then
        ' call the function to refresh the pivots
        Call MacroQueryMySQL
        ActiveWorkbook.SaveAs (".\<some prefix if you need it>\" & Left(Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 1), Len(ActiveWorkbook.Name) - 4 - 1) & Format(Date, " yyyy-mm-dd") & ".xlsm")
        Application.Quit
    End If
End Sub

Sub MacroQueryMySQL()
    ' even this section can be automated if some naming convention is used in the ODBC query names
    ActiveWorkbook.Connections("Query from <name of the query").Refresh
End Sub