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.

VB Macro to Loop Field (Dynamic) and Export Chart (Dynamic) to Excel & Name Sheets by Field Value

autopilot
Contributor III

VB Macro to Loop Field (Dynamic) and Export Chart (Dynamic) to Excel & Name Sheets by Field Value

This is an updated version of http://community.qlik.com/docs/DOC-3481

This is a VBScript macro intended to provide extended, dynamic export-to-excel capabilities.

Set up:

1. Copy/Paste macro script into application and set security setting appropriately

2. Create variable in QV app called - vfname. This will be used to control the field to loop when exporting.

3. Create variable in QV app called - vMacroChartId. This will be used to identify the chart to export.

4. Create button in QV app - property settings below:

     - actions > add > external > set variable > vfname > [desired field]

     - actions > add > external > set variable > vMacroChartId > [desired chart id]

     - actions > add > external > run macro > AdHocExport

The script will loop the field provided as the value in the variable (vfname) and export to Excel the object id provided as the value in the variable (vMacroChartId) - i.e. input box object in the QV app; button action property settings.

In Excel, this will result in the copied object being pasted into each sheet for each of the possible values available in the field (set by variable). In addition, the Excel worksheet will be named according to the field value set at the time of export.

This has potential in various applicable contexts to add more flexibility and extend the user's abilities to administer self-service BI as compared to the standard Excel export options within QV currently.

Below are the list of updates applied based on common issues I observed:

Updates:

- added function to clean and replace the field value string of invalid characters when naming sheets in Excel to prevent macro from failing

- updated the way sheets are created to add after the last vs previous that would start reversing order at the fourth sheet addition

- added code that will standardize the default sheets in Excel on open regardless of user settings to mitigate issues when default was set to < 3

NEW UPDATES:

- added new file (attached: qv_vbs_autoSave_delWksh_v2_customFileName.txt) that includes an updated version which will also remove excess worksheets, auto save the file to the designated location, and set the file name dynamically depending on the selections within the qlikview app.

- thanks to SurynnChin for his feedback in creating these revisions.

'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

FUNCTION strClean (strtoclean)
tempstr = strtoclean
charArray = ARRAY("?", "/", "\", ":", "*", """", "<", ">", ",", "&", "#", "~", "%", "{", "}", "+", "_", ".","[","]")
FOR EACH tmpChar in charArray
SELECT CASE tmpChar
  CASE "&"
  changeTo = " and "
  CASE ELSE
  changeTo = ""
END SELECT
tempstr = REPLACE( tempstr, tmpChar, changeTo )
NEXT
'msgbox tempstr
strClean = tempstr
END FUNCTION

'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SUB AdHocExport
confirmation = MSGBOX ("Ad hoc Excel export has been initiated." & vbCrLf & "Do you wish to continue?"& vbCrLf &"",  36, "Export Confirmation")
  IF confirmation = 7 THEN
   EXIT SUB
  END IF
DIM xlApp
DIM xlBook
DIM xlSheet, xlNewSheet, intSheetCount
DIM strSheetName
DIM var
DIM fname, value
SET f = ActiveDocument.Variables("vfname")
fname = f.GetContent.STRING
SET v = ActiveDocument.Variables("vMacroChartId")
var = v.GetContent.STRING
SET xlApp = CREATEOBJECT("Excel.Application")
xlApp.Visible = TRUE
SET xlBook = xlApp.Workbooks.Add
SET xlSheet = xlBook.Worksheets("Sheet1")
' ActiveDocument.Fields(fname).Clear
FOR i=1 to 3
intSheetCount = xlBook.Application.Worksheets.Count
IF intSheetCount < 3 THEN
  SET xlNewSheet = xlApp.Application.Worksheets.Add(, xlApp.Worksheets(xlApp.Worksheets.Count))
  xlApp.Worksheets(1).SELECT
END IF
NEXT
SET Doc = ActiveDocument
SET Field = Doc.Fields(fname).GetPossibleValues
FOR i=0 to Field.Count-1
Doc.Fields(fname).Clear
Doc.FIelds(fname).SELECT Field.Item(i).Text
Doc.GetApplication.WaitForIdle
Doc.GetSheetObject(var).CopyTableToClipBoard TRUE
xlApp.ActiveSheet.Paste
xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireColumn.AutoFit
xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireRow.AutoFit
' strSheetName = Field.Item(i).Text
value = Field.Item(i).Text
strSheetName = strClean(value)
xlApp.ActiveSheet.Name = strSheetName
   IF(i<Field.Count-1)THEN
    IF(i>=2)THEN
'     xlApp.ActiveWorkbook.Worksheets.Add
     SET xlNewSheet = xlApp.Application.Worksheets.Add(, xlApp.Worksheets(xlApp.Worksheets.Count))
    END IF
    IF(i<2) THEN
     xlApp.Worksheets(xlApp.ActiveSheet.Index +1).SELECT
    END IF
   END IF
NEXT
Doc.Fields(fname).Clear
MSGBOX "Ad hoc Excel export is complete!",64,"Task Completion Notification"
END SUB

'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Labels (1)
Comments
Partner
Partner

Hi there, thanks very much for the above post. I have used it (and amended it a little) to provide a solution for a client. However, I am having an issue running this through a scheduled task? It seems to be falling over at the part SET xlapp or there abouts. Do you have any ideas why this might be happening? Should this process be able to run as a background process as it would be doing on a server through a task scheduler?

Many thanks

Ben

0 Likes
autopilot
Contributor III

Thanks for your feedback.

Do you have excel installed on the machine executing the macro?

Are the security rights set accordingly in the macro module? in the document? in QMC (if applicable)?

How are you triggering the task? Are you using a .bat program and windows task scheduler ...or publisher, etc.?

If you can use the above questions to identify the cause or provide me more information about your environment, I would be glad to help as much as I can.

Alternatively, you may find helpful my updated version of this utility here http://community.qlik.com/docs/DOC-4841

0 Likes
Partner
Partner

Hi there,

Thanks for the message,

Yes we do have excel on the machine.

The security on the macro is set correctly (system) and we are running it from a .bat file through windows task scheduler. It all runs fine apart from when we put it through the task scheduler. Hence, I am thinking that maybe some of the use of the excel objects is a problem when it is running as a background process? (Just a wild stab in the dark really!!)

I have actually managed to achieve what I am after using a biff export instead which is working ok through the scheduled task. Still if you have any ideas as to why your code didn’t run this way I would be interested to hear them.

Many thanks

Ben

0 Likes
autopilot
Contributor III

Interesting.....that is strange...

So what I'm understanding is that if you run the macro in the document - it works; if you run the macro manually using the .bat file - it works(?); and, if you run the macro using the .bat file and windows task scheduler - it fails.

Are you able to provide the .bat file and/or the modified macro so I can review? Also, just a thought, when you run the .bat file, I assume it does a reload and the document is set to trigger the macro on post reload - correct? If so, are the appropriate variables being set prior to macro execution? Another thought, is the task scheduler running with enough rights?

Otherwise, can you provide which steps in the process succeed up to where it fails?

0 Likes
autopilot
Contributor III

Another thing that you could test is whether the routine will successfully execute any macro in lieu of the export one to possibly identify where the source of the problem is occurring.

0 Likes
surynnchin
New Contributor III

Thanks Autopilot for sharing this.

Im totally new to this vbscript and i need your advice here. Is it possible for me to save the excel into a particular folder and name the excel with a dynamic name according to my selection of year and month? May be something like this ExcelExport_2014_Jan.xlxs

0 Likes
autopilot
Contributor III

Here is a modified script that illustrates how to integrate saving the file with the extraction. Let me know your thoughts...

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SUB AdHocExport

  DIM vFilePath

  confirmation = MSGBOX ("Ad hoc Excel export has been initiated." & vbCrLf & "Do you wish to continue?"& vbCrLf &"",  36, "Export Confirmation")

  IF confirmation = 7 THEN

  EXIT SUB

  END IF

  vFilePath = BrowseFolder(value)

  DIM xlApp

  DIM xlBook

  DIM xlSheet, xlNewSheet, intSheetCount

  DIM strSheetName

  DIM var

  DIM fname, value

  SET f = ActiveDocument.Variables("vfname")

  fname = f.GetContent.STRING

  SET v = ActiveDocument.Variables("vMacroChartId")

  var = v.GetContent.STRING

  SET xlApp = CREATEOBJECT("Excel.Application")

  xlApp.Visible = TRUE

  SET xlBook = xlApp.Workbooks.Add

  SET xlSheet = xlBook.Worksheets("Sheet1")

  FOR i=1 to 3

  intSheetCount = xlBook.Application.Worksheets.Count

  IF intSheetCount < 3 THEN

  SET xlNewSheet = xlApp.Application.Worksheets.Add(, xlApp.Worksheets(xlApp.Worksheets.Count))

  xlApp.Worksheets(1).SELECT

  END IF

  NEXT

  SET Doc = ActiveDocument

  SET Field = Doc.Fields(fname).GetPossibleValues

  FOR i=0 to Field.Count-1

  Doc.Fields(fname).Clear

  Doc.FIelds(fname).SELECT Field.Item(i).Text

  Doc.GetApplication.WaitForIdle

  Doc.GetSheetObject(var).CopyTableToClipBoard TRUE

  xlApp.ActiveSheet.Paste

  xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireColumn.AutoFit

  xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireRow.AutoFit

  value = Field.Item(i).Text

  strSheetName = strClean(value)

  xlApp.ActiveSheet.Name = strSheetName

  IF(i<Field.Count-1)THEN

  IF(i>=2)THEN

  SET xlNewSheet = xlApp.Application.Worksheets.Add(, xlApp.Worksheets(xlApp.Worksheets.Count))

  END IF

  IF(i<2) THEN

  xlApp.Worksheets(xlApp.ActiveSheet.Index +1).SELECT

  END IF

  END IF

  'this will save the file many times - 1 per value in field

  xlApp.DisplayAlerts = FALSE

  xlBook.SaveAs vFilePath & "ExcelExport_" & strSheetName & ".xlsx"

  NEXT

' this will create the file once using the last value in field

' xlApp.DisplayAlerts = FALSE

' xlBook.SaveAs vFilePath & "ExcelExport_" & strSheetName & ".xlsx"

' xlApp.Quit

  Doc.Fields(fname).Clear

  MSGBOX "Ad hoc Excel export is complete!",64,"Task Completion Notification"

END SUB

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

FUNCTION BrowseFolder (vpath)

  CONST OverWrite = TRUE

  DIM destpath,  srcFiles

  SET objShell = CreateObject("Shell.Application")

  SET objFolder = objShell.BrowseForFolder(0, "Example", 16384,"Computer")

  IF objFolder IS NOTHING THEN

  Wscript.Quit

  END IF

  SET Doc = ActiveDocument

  Doc.CreateVariable("vFolderName")

  Doc.Variables("vFolderName").SetContent objFolder.self.path &"\", TRUE   

  BrowseFolder = Doc.Variables("vFolderName").GetContent.STRING

END FUNCTION

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

FUNCTION strClean (strtoclean)

  tempstr = strtoclean

  charArray = ARRAY("?", "/", "\", ":", "*", """", "<", ">", ",", "&", "#", "~", "%", "{", "}", "+", "_", ".","[","]")

  FOR EACH tmpChar in charArray

  SELECT CASE tmpChar

  CASE "&"

  changeTo = " and "

  CASE ELSE

  changeTo = ""

  END SELECT

  tempstr = REPLACE( tempstr, tmpChar, changeTo )

  NEXT

  strClean = tempstr

END FUNCTION

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0 Likes
autopilot
Contributor III

I also attached the script in a text file.

0 Likes
surynnchin
New Contributor III

Hi autopilot,

Thanks for reply. Here are few questions for you

1. Can i straight away export and save it into vFilePath without open the excel worksheet?

2. Before i press on the button "RunThroughChartsList" to export, i will make a few selections like one Year, one Month and one Country. My loop field is Product.

So i would like my vFilePath= "Export_vCountry_vYear_vMonth" based on my selection?

3. There is a country which only got 2 products: ProductA and ProductB, so the 1st excel sheet will be named "Product A", 2nd will be "Product B", can i remove the empty sheet -> Sheet3?

Thanks in advance

0 Likes
autopilot
Contributor III

No problem....I've attached a file of the modified script - "adhocexport..."

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-07-17 12:55 PM
Updated by: