Qlik Community

QlikView Documents

Documents for QlikView related information.

Macro - Export Multiple Charts From Container To Excel

Macro - Export Multiple Charts From Container To Excel

Hello All,

Once I struggled to export to all the charts in a container to excel file. I could not find correct code in the community. So I would like to share the below code which exports the charts into excel.

Sub Export

  set oXL = CreateObject("Excel.Application")

  oXL.DisplayAlerts = False

  oXL.visible=True 'False ti hide the excel

  Dim oXLDoc 'as Excel.Workbook

  Dim i

   Set oXLDoc  = oXL.Workbooks.Add

    FilePath =  ActiveDocument.Variables("vPath").GetContent.String

    FileName =  oXLDoc.Name

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

  Set ContainerObj = ActiveDocument.GetSheetObject("CT01") 'Container ID

    Set ContProp=ContainerObj.GetProperties

  SheetObj=Array("CH01","CH02","CH03")                    'Chart ID's

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

  for i=0 to UBound(SheetObj)

  'ActiveDocument.GetApplication.WaitForIdle

  oXL.Sheets.Add

  oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )

     ContProp.SingleObjectActiveIndex = i

        ContainerObj.SetProperties ContProp

  Set oSH = oXL.ActiveSheet

     oSH.Range("A1").Select

     Set obj = ActiveDocument.GetSheetObject(SheetObj(i))

     obj.CopyTableToClipboard True

     oSH.Paste

     sCaption=obj.GetCaption.Name.v

     Set obj=Nothing

  oSH.Rows("1:1").Select

  oXL.Selection.Font.Bold = True

     oSH.Cells.Select

     oXL.Selection.Columns.AutoFit

     oSH.Range("A1").Select

  oSH.Name=left(sCaption,30)

  Set oSH=Nothing

  Next

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

  Call Excel_DeleteBlankSheets(oXLDoc)

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

  oXL.DisplayAlerts = True

    oXLDoc.Sheets(1).Select

   If FilePath <>"" then

  oXLDoc.SaveAs FilePath & "\" & FileName & ".xlsx"

  Else

  Msgbox "Folder path can not be empty. Enter Valid path"

   End If

   oXLDoc.Close FALSE

   oXL.Quit

  Set oXL    =Nothing

  Set oXLDoc =Nothing

End Sub

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

Private Sub Excel_DeleteBlankSheets(ByRef oXLDoc)

  For Each ws In oXLDoc.Worksheets

  If oXLDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then

  On Error Resume Next

     Call ws.Delete()

  End If

  Next

End Sub

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

Press Ctrl-M to see Macro window, and make sure "System Access" and "Allow System Access" drop down boxes are selected at the left hand side. Change the container Id and chart Id's according to your requirement.

Capture.PNG

That's all.

scriptina.jpg

Attachments
Comments
haymarketpaul
Contributor III

Any idea how to export a line chart as a line chart to Excel? 

When i use something similar to yours to export a line chart it seems to convert it to a straight table.

Not applicable

Hi Nagaraj, Thank you for your wonderful work. I'm new to Qlikview. I'm getting error while using your script in line 12. Can you please through some light on Line 12 of your script? What is vPath here?? Is it the path where we want to save our exported excel file?

Hello Manoj,

Yes. You are right. Here, vPath is a variable which I used to store the file path.

Capture1.PNG

Capture.PNG

You can see the path in variable overview window. Later, we can use this path to store the exported excel. You need to change the path to your desktop or somewhere else. Hope this helps. If you have any issues, let me know.

Not applicable

Hey Tamil Nagaraj, Thank you for your reply. I got that but still I am getting error in line 13,

FileName =  oXLDoc.Name 


I'm getting this error message while testing the macro, "Object required: 'oXLDoc'". can you please help me with this? Thanks in advance.

Manoj,

I just found some typo error in the below line.

Typo.png

Correct line should be

Corrected.png

I have updated the same in above document. But I have not changed anything in the sample qvw file. It's working fine and there is no typo error. Thanks for pointing out.

Not applicable

Thanks a ton for your kind help.

Currently, the excel will be saved as "Book1.xlsx". If you want to include date along with the time stamp, you can replace the below code (Line Number 13).

FileName =  oXLDoc.Name 

FileName =  ActiveDocument.Evaluate("date(Now(), 'DDMMYYYY hhmmss')")


Excel will be saved as "05072016 182808.xlsx"


Or you can use


FileName =  "Export_" & ActiveDocument.Evaluate("date(Now(), 'DDMMYYYY hhmmss')") 


Excel will be saved as "Export_05072016 182808.xlsx"


If you still face any problem, let me know.

Version history
Revision #:
1 of 1
Last update:
‎03-30-2016 12:38 AM
Updated by: