Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gireesh1216
Creator II
Creator II

how to export multiple container objects to excel?

Hi,

could somebody of you please help me?

I have multiple containers.Example Container1,Container2. Now I want to export them all  into Excel. Each Diagram should be exported (via XL-Button) to Excel but all in the same File just in different Sheets.

Thanks In Advance

Gireesh

1 Solution

Accepted Solutions
gireesh1216
Creator II
Creator II
Author

sub Export 

 

  set oXL = CreateObject("Excel.Application") 

  oXL.DisplayAlerts = False 

  oXL.visible=True 

  Dim oXLDoc 'as Excel.Workbook 

  Dim i 

 

  Set oXLDoc = oXL.Workbooks.Add 

 

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

  Set ContainerObj = ActiveDocument.GetSheetObject("CT01") 

    Set ContProp=ContainerObj.GetProperties 

  aSheetObj=Array("CH03","CH08") 

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

 

  for i=0 to UBound(aSheetObj) 

 

  '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(aSheetObj(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 

  '// Finally select the first sheet 

    oXLDoc.Sheets(1).Select 

 

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

  set oXL    =Nothing 

  set oXLDoc =Nothing 

end sub 

Private Sub Excel_DeleteBlankSheets(ByRef oXLDoc) 

 

 

  For Each ws In oXLDoc.Worksheets 

  If (not HasOtherObjects(ws)) then 

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

  On Error Resume Next 

     Call ws.Delete() 

  End If 

  End If 

  Next 

 

 

End Sub 

View solution in original post

8 Replies
avinashelite

we don't have any default feature for this in Qlikview, you need to write a macro for the same

Macro to export multiple charts to Excel

venkatasreekant
Specialist
Specialist

Hi Gireesh,


At a time you can able to export single Chart into excel.You cannot able to export container.

To export container into a Image format you have to develop a macro.


Thanks & Regards,

Venkata Sreekanth

gireesh1216
Creator II
Creator II
Author

I know.Please provide macro script

avinashelite

Did you checked the thread which I mentioned in the last response ??

gireesh1216
Creator II
Creator II
Author

I checked.But that script is not executing.

MK9885
Master II
Master II

There is a macro for taking screenshot for whole screen but for particular object, I don't know.

Why don't you allow export and copy that image to paint etc?

Just right click and you'll see

'Copy to Clipboard' click that, paste it in Paint...

'Macro to get Screenshot for Screen

Sub ShootScreen

ActiveDocument.ActiveSheet.CopyBitmapToClipboard

End Sub

gireesh1216
Creator II
Creator II
Author

sub Export 

 

  set oXL = CreateObject("Excel.Application") 

  oXL.DisplayAlerts = False 

  oXL.visible=True 

  Dim oXLDoc 'as Excel.Workbook 

  Dim i 

 

  Set oXLDoc = oXL.Workbooks.Add 

 

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

  Set ContainerObj = ActiveDocument.GetSheetObject("CT01") 

    Set ContProp=ContainerObj.GetProperties 

  aSheetObj=Array("CH03","CH08") 

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

 

  for i=0 to UBound(aSheetObj) 

 

  '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(aSheetObj(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 

  '// Finally select the first sheet 

    oXLDoc.Sheets(1).Select 

 

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

  set oXL    =Nothing 

  set oXLDoc =Nothing 

end sub 

Private Sub Excel_DeleteBlankSheets(ByRef oXLDoc) 

 

 

  For Each ws In oXLDoc.Worksheets 

  If (not HasOtherObjects(ws)) then 

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

  On Error Resume Next 

     Call ws.Delete() 

  End If 

  End If 

  Next 

 

 

End Sub 

View solution in original post

gireesh1216
Creator II
Creator II
Author

Sub ExportGraph()

Dim vSheet

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = TRUE

Set XLDoc = XLApp.Workbooks.Add

vSheet = ""

vSheet = "Sheet1"

ActiveDocument.GetSheetObject("CH03").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).Range("A" & 1).Select

XLDoc.Sheets(vSheet).Paste

Set XLDoc = Nothing

Set XLApp = Nothing

End Sub