Qlikview Export to Excel Part 2

Qlikview Export to Excel Part 2 – Choosing Object to Export and Having User Determine Location/Name of File

The following post is the second in a series covering Qlikview and exporting objects (tables, charts, listboxes, etc.) to Microsoft Excel.  In the first post, I covered a very simple macro to export a specific Table Box to a specific location.  You can view part one here:  Qlikview Export to Excel Part 1

There is also a more advanced post in this series   Qlikview Export to Excel Tabs with Formatting.  This post covers the following topics:

  • Allow user to select a field to parse the table data into separate Excel Tabs
  • Performs additional formatting in Excel, once the data has been exported

 

In this post, I will demonstrate how to allow the user to select which object to export, prompting the user to specify a file name and location to export the file.

First, you will need to add the following to your Load Script:  Sourced from Qlik Community Post

Qlikview Load Script

This block of code will create fields in your Qlik app that display information about all of the objects.  You will need this for the user interface.

Qlikview Variables

Next, setup the following variables:

Qlikview Export to Excel

Qlikview Macro Code

Here is the Macro VBScript for exporting a Table Box to Excel:

Function

And the VBScript Function, to prompt user to select location to save the file.  Courtesy of  Rob van der Woude’s Scripting Pages

Qlikview Objects

Now, setup a table box inclusive of the fields you added in the Load Script above:

Qlikview Export to Excel

Create a button object, with the following Action:

Qlikview Export to Excel

At this point, your Qlikview App should look something like this:

Qlikview Export to Excel

Using the Table Box, select an object in field “ActualID”

Qlikview Export to Excel

Then click the “Export” button.  The Macro is called, first prompting you to select a location to save the Excel file.  Then you will be prompted to type in a name for the Excel file.  After you click OK, the Macro will complete and give you the “Export Complete” message box.  You can select any of the objects in the Table Box and the Macro will export each selection based on the Qlikview default settings.  If you click on the “Export” button without selecting an object, you will be prompted with a message and the export will be terminated.  The same will happen if you neglect to enter a name for the Excel file.

I hope this example was informative.  If you have anything to add, please feel free to post a comment.

Enjoy!

What’s next?

In the final post of this series (Qlikview Export to Excel), I will get into more advanced VBScript around exporting data from Qlikview to Excel.  This will include:

  • Exporting content to separate Excel tabs, based on a selected field
  • Additional formatting in Excel once the data is exported

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

4 thoughts on “Qlikview Export to Excel Part 2

    • Hello Jan,

      In the Sub ExportToExcel:

      Replace line: ExcelWorkbook.SaveAs strPath & “\” & FileName & “.xls”, 56
      With this: ExcelWorkbook.SaveAs strPath & “\” & FileName & “.xlsx”, 51

      That should do it. I tested it on my machine and it worked. Thanks for bringing this up as I’m sure other may be experiencing the same issue.

  1. Hello Venetia. All you need to do is add the Function “SelectFolder” code inside the Module window (Tools –> Edit Module), below the main Sub “ExportToExcel”. As long as you put the function code after the main macro, it will get called and send parameter myStartFolder.

    Hope this answers your question. Let me know if you need anything else.

Leave a Comment