Qlikview Export to Excel Tabs with Formatting

Qlikview Export to Excel Tabs with Formatting – Exporting content to separate Excel tabs based on selected field, with additional formatting in Excel once the data is exported

The following post is the third in a series covering the Qlikview Export to Excel (tables, charts, listboxes, etc.) process.

In this post, I will demonstrate how to export the content of a table box or chart into separate Excel tabs based on a user-selected field.  Also, I will show how you can do some additional formatting in Excel once the data is exported.

 

Qlikview Export to Excel Tabs with Formatting – Load Script:

First, you will need to add the following to your Load Script, which includes the sample data used in this post so you can try it yourself :

 

Qlikview Export to Excel Part 3 – Qlikview Objects Setup:

After loading the data above, go ahead and setup the following objects:

  • Table Box:  Title “Weekly Player FPTS”
    • Week
    • Player
    • Position
    • NFLTeam
    • HomeAway
    • FPTS
  • Table Box:  Title “Weekly Position”
    • Position
    • Week
    • HomeAway
  • Straight Table Chart:  Title “Pts by Position”
    • Dimension 1 – Position
    • Dimension 2 – Week
    • Expression:  =Sum(FPTS)
      • Label “Pts”
    • Presentation – Totals – select “Totals on Last Row”

Note: Save Qlikview Document, then RELOAD before Continuing.  The Load Script needs to pick up attributes of the three objects above before you continue!

  • List Box for field “Caption”
  • List Box for field “ActualID”
  • Variables
    • vFieldName
      • Definition:  =Concat(%TabFields, ‘|’)
    • vObjectToExportID
      • Definition:  =Concat(ActualID, ‘|’)
    • vFptsConst
  • Input Box for Displayed Variables
    • Displayed Variables:  vFieldName, vObjectToExportID, vFptsConst, vDocPath
    • Title of Input Box “Variables”
  • Multi Box
    • Title of Multi Box “Select Column to Segment Excel Tabs”
    • Fields Displayed in Multibox:  %TabFields
      • Label “Select Field”
  • Input Box (for Setting variable vFptsConst)
    • Title of Input Box “Points Highlight”
    • Displayed Variables:  vFptsConst
      • Label “Select Value”
    • Constraints – Value List – Select “Predefined Values in Drop-down”
    • Constraints – Predefined Values – Check “Number Series” From 0 to 60 with Step equal to 1
  • Button
    • Text set to “Export”
    • Actions:  Select External, then “Run Macro”
      • Macro Name “ExportToExcel”
    • Caption – Check “Show Caption”
      • Title Text “Excel Export Button”

Qlikview Export to Excel Part 3 – VBScript Macro Setup:

Now, go into “Tools – Edit Modules”, then add the following VBScript Macros:

 

 

Don’t forget to set the following options in the Edit Module window:

 

Qlikview Export to Excel Tabs with Formatting

Qlikview Export to Excel Tabs with Formatting – Running through the example

Your Qlik app should look like this:

 

Qlikview Export to Excel Tabs with Formatting

 

Steps to execute the sample:

  1. Select an item from either the “Caption” or “ActualID” List Box.  This selection determines which ObjectID gets populated in the “vObjectToExportID” variable.  You can see your selection populate in the “Variables” Multi Box object after you make your choice.
    • Please note that if you fail to make a selection, the macro will prompt a message box to indicate this selection is missing, then terminate the macro
  2. In the drop-down Input Box “Select Column to Segment Excel Tabs”, pick which field you would like to segment the data in different Excel Tabs during the export.  This selection determines which Field gets populated in the “vFieldName” variable.  You can see your selection populate in the “Variables” Multi Box object after you make your choice.
    • Please note that if you fail to make a selection, the macro will prompt a message box to indicate this selection is missing, then terminate the macro
  3. In the drop-down Input Box “Points Highlight”, pick a value you would like to have the macro compare against field “FPTS” in Excel.  If the value in Excel is greater than your selected values, it will highlight the cell background color Yellow.  The selected value is stored in the “vFptsConst” variable.  You can see your selection populate in the “Variables” Multi Box object after you make your selection.
  4. Click the “Export” button
  5. You will be prompted to select a folder to save the exported Excel Document
  6. Once you see the “Successful Export!”, you can open the file to view your data!

Your Excel file should look like this:

 

Qlikview Export to Excel Tabs with Formatting

 

I hope this example was informative.  If there are any additional features you would like to add, or if you have any further questions about the example in this post, please post a comment.

Enjoy!

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

1 thought on “Qlikview Export to Excel Tabs with Formatting

  1. Thanks, this was exactly what I was looking for! Followed all instructions and got it working, then was able to tailor this to my own needs. Great post!

Leave a Comment