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

 

3 thoughts on “Qlikview Export to Excel Tabs with Formatting”

  1. That’s great! Exactly what I was looking for.
    However, I’ve got a problem with the ‘Saveas’ I guess.
    Firstly, I use your function ‘SelectFolder’ and the input to get the Filename.
    Then, I export my data with some formatting.
    Macro stops at ‘XLApp.ActiveSheet.Range(“A2”).Select’.
    The file is not saved and I haven’t got the message box.
    Any ideas?
    Thanking you in advance.

    Here is my code

    Set XLApp = CreateObject(“Excel.Application”)
    XLApp.Visible = True
    set XLDoc = XLApp.Workbooks.Add
    ActiveDocument.GetSheetObject(“HiddenData”).CopyTableToClipboard true
    XLDoc.Sheets(1).Paste()

    ‘************** Format EXCEL ***********************
    ‘Freeze Panes on Worksheet
    XLApp.ActiveSheet.Range(“D2”).Select
    XLApp.ActiveWindow.FreezePanes = True

    For c=1 to XLApp.ActiveSheet.UsedRange.Columns.Count
    ‘Format column so it does not wrap text
    XLApp.ActiveSheet.Columns(c).Wraptext = false
    ‘Auto fit the excel column witdth
    XLApp.ActiveSheet.Columns(c).AutoFit
    Next

    ‘format cell background color to Yellow
    XLApp.ActiveSheet.Range(“AO1:AS1”).Interior.ColorIndex = Yellow
    ‘format cell background color to Orange
    XLApp.ActiveSheet.Range(“AT1”).Interior.ColorIndex = Orange
    ‘format cell background color to Green
    XLApp.ActiveSheet.Range(“AU1:BB1”).Interior.ColorIndex= Green
    ‘format cell background color to Blue
    XLApp.ActiveSheet.Range(“BC1:BD1”).Interior.ColorIndex= Blue

    XLApp.ActiveSheet.Range(“A2”).Select

    ‘ To Export to “.xlsx”
    XLDoc.SaveAs strPath & “\” & FileName & “.xlsx”, 51

    MsgBox “Successful Export!”, 64, “Your data has been exported to Excel.”

    • David, sorry for the delay. I took the code snippet you posted above and worked with it using some of my own data. The code below is working now. I did my best to keep as much of your code “as is”, but you will need to tweak some of the excel column ranges where you format cell background colors. See if this works for you……..

      Sub ExportToExcel

      Const Yellow = 36
      Const Green = 50
      Const Blue = 33
      Const Orange = 45

      Dim XLApp, XLDoc, FileName, strPath

      strPath = SelectFolder( “” )
      FileName = “ExportExcelFile”

      ActiveDocument.GetSheetObject(“TB05”).CopyTableToClipboard true

      Set XLApp = CreateObject(“Excel.Application”)
      Set XLDoc = XLApp.Workbooks.Add
      XLApp.DisplayAlerts = False
      XLApp.Visible = True

      XLApp.Sheets(1).Paste()

      XLApp.ActiveSheet.Range(“D2”).Select
      XLApp.ActiveWindow.FreezePanes = True

      For c=1 to XLApp.ActiveSheet.UsedRange.Columns.Count
      ‘Format column so it does not wrap text
      XLApp.ActiveSheet.Columns(c).Wraptext = false
      ‘Auto fit the excel column witdth
      XLApp.ActiveSheet.Columns(c).AutoFit
      Next

      ‘format cell background color to Yellow
      XLApp.ActiveSheet.Range(“A1:B1”).Interior.ColorIndex = Yellow
      ‘format cell background color to Orange
      XLApp.ActiveSheet.Range(“C1”).Interior.ColorIndex = Orange
      ‘format cell background color to Green
      XLApp.ActiveSheet.Range(“D1:E1”).Interior.ColorIndex= Green
      ‘format cell background color to Blue
      XLApp.ActiveSheet.Range(“F1:G1”).Interior.ColorIndex= Blue

      XLApp.ActiveSheet.Range(“A2”).Select

      ‘ To Export to “.xlsx”
      XLDoc.SaveAs strPath & “\” & FileName & “.xlsx”, 51
      MsgBox “Successful Export!”, 64, “Your data has been exported to Excel.”

      End Sub

      Function SelectFolder( myStartFolder )
      ‘ Standard housekeeping
      Dim objFolder, objItem, objShell

      ‘ Custom error handling
      On Error Resume Next
      SelectFolder = vbNull

      ‘ Create a dialog object
      Set objShell = CreateObject( “Shell.Application” )
      Set objFolder = objShell.BrowseForFolder( 0, “Select Folder”, 0, “Computer” )

      ‘ Return the path of the selected folder
      If IsObject( objfolder ) Then SelectFolder = objFolder.Self.Path

      ‘ Standard housekeeping
      Set objFolder = Nothing
      Set objshell = Nothing
      On Error Goto 0

      End Function

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