Printing charts to excel from various QV sheets

[tinkerz1]

Hi,

I am trying to print charts and tables via vbscript to excel, the charts and tables are across various sheets in Qlikview.

As I understand it, from reading the forum, you activate the QV sheet before you print to excel.

The tables print but the charts don't, they print as tables from the last copy command.

Only if I have the active QV sheet open will those charts print, if I start from another sheet they wont print.

Where am I going wrong?

sub xport2xl1()
iRow = 4
set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

set xlWB = xlApp.Workbooks.Add
set xlSheet = xlWB.Worksheets(1)
set obj = ActiveDocument.getsheetobject(ChartName)

xlSheet.Activate
xlSheet.Cells.Clear

ActiveDocument.Sheets("SH04").Activate
ActiveDocument.GetApplication.WaitForIdle 100

'Print table
set txt1 = ActiveDocument.GetSheetObject("CH53")
set xlSheet = xlWB.Worksheets(2)
xlSheet.Activate
txt1.CopytableToClipboard TRUE
xlSheet.Cells(20,1).Select
xlSheet.Paste
xlWB.ActiveSheet.Range("A19").Wraptext= False
xlWB.ActiveSheet.Cells(19,1) = txt1.GetCaption.Name.v
'Print Chart
set txt1 = ActiveDocument.GetSheetObject("CH08")
txt1.CopyBitmapToClipboard()
xlWB.ActiveSheet.Cells(1,1).Select
xlWB.ActiveSheet.Paste
xlWB.ActiveSheet.Name = left("BCC New XS Major",31)
'Print table
set txt1 = ActiveDocument.GetSheetObject("CH54")
set xlSheet = xlWB.Worksheets(3)
xlSheet.Activate
txt1.CopytableToClipboard TRUE
xlSheet.Cells(20,1).Select
xlSheet.Paste
xlWB.ActiveSheet.Range("A19").Wraptext= False
xlWB.ActiveSheet.Cells(19,1) = txt1.GetCaption.Name.v 
  'Print Chart
set txt1 = ActiveDocument.GetSheetObject("CH38")
txt1.CopyBitmapToClipboard()
xlWB.ActiveSheet.Cells(1,1).Select
xlWB.ActiveSheet.Paste
xlWB.ActiveSheet.Name = left("BCC New XS Minor",31)

ActiveDocument.Sheets("SH02").Activate
ActiveDocument.GetApplication.WaitForIdle 100
'Print table
set txt1 = ActiveDocument.GetSheetObject("CH49")
txt1.CopytableToClipboard TRUE
xlWB.Worksheets.Add ,xlWB.Worksheets(xlWB.Worksheets.count)
xlWB.ActiveSheet.Cells(20,1).Select
xlWB.ActiveSheet.Paste
xlWB.ActiveSheet.Range("A19").Wraptext= False
xlWB.ActiveSheet.Cells(19,1) = txt1.GetCaption.Name.v
  'Print Chart
set txt1 = ActiveDocument.GetSheetObject("CH16")
txt1.CopyBitmapToClipboard()
xlWB.ActiveSheet.Cells(1,1).Select
xlWB.ActiveSheet.Paste
xlWB.ActiveSheet.Name = left("BCC Aged XS Major >1",31)
'Print table
set txt1 = ActiveDocument.GetSheetObject("CH50")
txt1.CopytableToClipboard TRUE
xlWB.Worksheets.Add ,xlWB.Worksheets(xlWB.Worksheets.count)
xlWB.ActiveSheet.Cells(20,1).Select
xlWB.ActiveSheet.Paste
  xlWB.ActiveSheet.Range("A19").Wraptext= False
xlWB.ActiveSheet.Cells(19,1) = txt1.GetCaption.Name.v 
'Print Chart
set txt1 = ActiveDocument.GetSheetObject("CH17")
txt1.CopyBitmapToClipboard()
xlWB.ActiveSheet.Cells(1,1).Select
xlWB.ActiveSheet.Paste
xlWB.ActiveSheet.Name = left("BCC Aged XS Major >2",31)


end sub

[marcus_sommer]

I think there is nothing wrong and it worked as designed. This meant if you copied a table you didn't need to activate the sheets and the objects because the table are just data. Instead of this need a chart to be rendered which required that the sheet is active and the chart is visible (not minimized) - you could imagine it (CopyBitmapToClipboard) as a kind of screenshot. Therefore these object must be visible to be able to be copied.

- Marcus

[tinkerz1]

yes which I why I wrote

ActiveDocument.Sheets("SH02").Activate
ActiveDocument.GetApplication.WaitForIdle 100

That should open the sheet then the rendering should work and can then copy.

The sheets are changing but it wont copy the chart.

[marcus_sommer]

Is the chart then visible when you activated the sheet? You might need to add:

set txt1 = ActiveDocument.GetSheetObject("CH16")

txt1.Activate

txt1.Restore

txt1.CopyBitmapToClipboard()

and if the object is within a container you need to active these container-tab.

Another method which I use most often is the use of a (for user) hidden print-sheet which contained (visible) copies of all my print- and export-objects - this simplified such tasks a lot.

- Marcus

[tinkerz1]

No the chart was not visable

I am using this is activate the sheet.

ActiveDocument.Sheets("SH04").Activate
ActiveDocument.GetApplication.WaitForIdle 100

[tinkerz1]

It looks like the code triggers at the end of the macro, not during the macro, so the chart wont render.

ActiveDocument.Sheets("SH04").Activate
ActiveDocument.GetApplication.WaitForIdle 100