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