Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

REFRESH EXCEL DATA BEFORE LOAD TO QLIK

Not applicable

REFRESH EXCEL DATA BEFORE LOAD TO QLIK

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
            objWb.RefreshAll

        ' 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
                    qt.Refresh
                Next
            Next
            ' Refresh every pivottable on all sheets
            For Each sh in objWb.Worksheets
                For Each pvt In sh.PivotTables
                    pvt.RefreshTable
                Next
            Next
        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
    objExcel.Quit

    ' 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

Version history
Revision #:
1 of 1
Last update:
‎05-20-2015 05:12 AM
Updated by: