Refreshing Excel data tables before a load

    We were looking for a solution to update Excel file pivottables before they are loaded into QlikView. I came up with the macro script below, which I want to share with you. I added an extra option to run the Workbook_Open macro. When opening an Excel file via QlikView, this macro will not start (to my experience). Choosing action option 2 will run the macro from QlikView, therefore starting whatever process is started when you open the Excel file manually.


    In QV press Ctrl+M to add the macro to the QV document. Be sure to allow system access for this macro function to work. Check the example script in the header of the function. Be aware that the filename should be quoted with single quotes.


    Please click the Like link if you are going to use this macro.


    ' *****************************************************
    ' Description: Function to open an Excel document and
    '              refresh all external data queries and
    '              pivottables in the Excel document or
    '              run the Workbook_Open macro that runs
    '              when manually opening the Excel document.
    ' Parameters:
    ' fileName > Specify the Excel document path to open.

    '            The path should be the absolute (complete) path on the filesystem.
    ' action   > 1 = Open Excel file and refresh all external data tables.
    '          > 2 = Open Excel file and run Workbook_Open macro.
    ' Example:
    ' LET result = OpenExcel('<path>\filename.xlsx', 1);
    ' If $(result) Then
    '      LOAD * FROM '<path>\filename.xlsx';
    ' EndIf
    ' *****************************************************
    Function OpenExcel(fileName, action)
    Dim objExcel, objWb, fso

        ' Get file extenstion from the document name
        ext = Mid(fileName, InStrRev(fileName, ".") + 1)

        ' Check if specified document name is an Excel document
        If ext <> "xlsx" And ext <> "xlsm" And ext <> "xls" Then
            OpenExcel = False
            Exit Function
        End If

        ' Create a filesystem instance
        Set fso = CreateObject("Scripting.FileSystemObject")
        ' Exit the function if the documentdoes not exist
        If Not fso.FileExists (fileName) Then
            OpenExcel = False
            Exit Function
        End If

        ' Create an Excel instance
        Set objExcel = CreateObject("Excel.Application")
        ' Exit the function if Excel object cannot be created. Excel is not installed?
        If objExcel Is Nothing Then
            OpenExcel = False
            Exit Function
        End If

        ' Disable Excel prompts and alerts
        objExcel.DisplayAlerts = False

        ' Open the Excel document
        Set objWb = objExcel.Workbooks.Open(fileName)
        ' Exit the function if document cannot be opened. Is the document already opened?
        If objWb Is Nothing Then
            OpenExcel = False
            Exit Function
        End If

        ' Do some stuff according to the specified action parameter
        Select Case action
        Case 1
            ' For Excel 2007 and later documents refresh all external data sources
            ' and pivottables in the document
            If ext = "xlsx" Or ext = "xlsm" Then

            ' For Excel 2003 document refresh each pivottable on each sheet
            ElseIf ext = "xls" Then
                ' First refresh each external query table on all sheets.
                ' Pivottables may depend on these query tables
                For Each sh in objWb.Worksheets
                    For Each qt In sh.QueryTables
                ' Refresh every pivottable on all sheets
                For Each sh in objWb.Worksheets
                    For Each pvt In sh.PivotTables
            End If

        Case 2

            ' Run the Workbook_Open macro
            objWb.Application.Run "ThisWorkbook.Workbook_Open"
        End Select

        ' Save Excel document and quit Excel
        objWb.Close True

        ' Cleanup memory
        Set fso = Nothing
        Set objWb = Nothing
        Set objExcel = Nothing

        ' Return true if the function finishes without an error.
        ' This does not mean that the Excel refresh was succesful
        OpenExcel = True
    End Function