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.<\/p>\n
This is the batch code I used:<\/p>\n
\n@echo off\r\ntitle Batch Run to update excel files\r\necho Set Batch Mode\r\nset BatchMode=TRUE<\/span>\r\nREM set the path to Excel\r\nset path=%path%;\"C:\\Program Files (x86)\\Microsoft Office\"\\Office12\\\r\necho Move to working directory\r\nREM change to the drive where the excel files are stored\r\nN:\r\ncd \"<patch to the directory store all the Excel files>\"\r\nREM work on all files starting with _*.xlsm\r\nfor %_% %%f in (_*.xlsm) do (\r\n\u00a0\u00a0 \u00a0echo Work on file: %%f\r\n\u00a0\u00a0 \u00a0EXCEL.EXE \/e \".\\%%f\"\r\n)<\/pre>\n<\/blockquote>\nWithin the Excel I used the following standard visual basic code to enable the automated update in case the evironment variable BatchMode=TRUE<\/span>. 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.<\/p>\nImportant is to place this code in the “ThisWorkbook” section, otherwise the function Workbook_Open will not automatically be called.<\/p>\n
\nPrivate Declare Function GetEnvVar Lib \"kernel32\" Alias \"GetEnvironmentVariableA\" _\r\n\u00a0\u00a0\u00a0 (ByVal lpName As String, ByVal lpBuffer As String, ByVal nSize As Long) As Long\r\n\r\nFunction GetEnvironmentVariable(var As String) As String\r\n Dim numChars As Long\r\n\u00a0\u00a0\u00a0 GetEnvironmentVariable = String(255, \" \")\r\n\u00a0\u00a0\u00a0 numChars = GetEnvVar(var, GetEnvironmentVariable, 255)\r\nEnd Function\r\n\r\nPrivate Sub Workbook_Open()\r\n '\u00a0\u00a0 we just want to update automated if batch mode is set\r\n\u00a0\u00a0\u00a0 If InStr(1, GetEnvironmentVariable(\"BatchMode<\/span>\"), \"TRUE\", vbTextCompare) Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ' call the function to refresh the pivots\r\n Call MacroQueryMySQL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 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\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Application.Quit\r\n\u00a0\u00a0\u00a0 End If\r\nEnd Sub\r\n\r\nSub MacroQueryMySQL()\r\n ' even this section can be automated if some naming convention is used in the ODBC query names\r\n\u00a0\u00a0\u00a0 ActiveWorkbook.Connections(\"Query from <name of the query\").Refresh\r\nEnd Sub<\/pre>\n<\/blockquote>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Update of an Excel database query to update pivots A database query (e.g. ODBC database connection to a MySQL db)[…]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,30],"tags":[8,6,7],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-excel","category-tools","tag-database-query","tag-excel","tag-pivot"],"_links":{"self":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=16"}],"version-history":[{"count":7,"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":411,"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts\/16\/revisions\/411"}],"wp:attachment":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}