Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.

Refreshing Excel data tables before a load

Not applicable

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
            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

Labels (2)
Comments
Not applicable

This seems like it would solve an issue I'm having with triggering reloads of Excel workbooks linked to Sharepoint lists, but I'm not sure how to actually put it into use.

Do I include this script in the Excel file, or do I include it in the QV document?

Note: My Excel files already have an On-Open macro that should refresh the data from Sharepoint, and it does when a user opens the file for viewing, but it doesn't seem to do anything when the QV server opens it to reload the data.

0 Likes
Not applicable

I'm changed the code a bit, but I'm waiting for approval. You need to add the code as macro in QV. You can now call the macro to refresh all data tables or to call the On-Open Excel macro, like it would run when manually opening the Excel file.

In the QV load script use the code to refresh an Excel file. Please see the macro script header for example and explanation on usage.

0 Likes
Not applicable

Ok, so where exactly does one create a macro in QlikView?  I didn't know any scripting was possible outside of the load script.

0 Likes
Not applicable

In QlikView press the Ctrl+M keys to open the macro screen. Copy and paste the macro code in the right textbox and click the check button.

Please view the reference document voor more information on macro's: http://community.qlik.com/docs/DOC-3492

0 Likes
Not applicable

I would like to get this to work, but being a beginner, I don't know where I should specify the path and filename. I've copied the code into the Module editor (CTRL M), but am then stuck. 

Can anyone offer assistance?

Not applicable

Hi Niels

I tried to use your macro in a qvw but get an error could you please help out?

The data in the excel file is linked to a SharePoint site and should be refreshed on opening. (this is also implemented in the excel file, when I open the excel file its refreshing the data)

the error is get is "Script line error:  If  Then", using debug I can see the outcome of $(result) is "result <NULL>"

reload used (no changes made in  the macro code provided by you)

Let result = OpenExcel('..\External Data\TeamSite SI Open Records PV.xlsx', 1);
If $(result) Then
LOAD
*
FROM '..\External Data\TeamSite SI Open Records PV.xlsx' (ooxml, embedded labels, header is 1 lines, table is [SI Open Records]);
EndIf

Thanks for your help in advance

0 Likes
Not applicable

Hi Niels,

It works and refreshes Excel on my desktop reload and works with command line qv /r  ...

But it is not working and refreshing Excel on publisher/distribution service reloads.

Do you have any idea why ?

Thanks,

Ersen

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-04-25 12:41 PM
Updated by: