topic Macro to export Caption in QlikView

Macro to export Caption

Wed, 17 Mar 2010 11:13:43 GMT

I have a macro which exports charts but I need it to also export the caption of the chart

sub export_actions

set v = ActiveDocument.Variables("QvWorkPath")

set XLApp = CreateObject("Excel.Application")

XLApp.Visible =true' false

set ExcelDoc = XLApp.Workbooks.Add

set obj1 = ActiveDocument.getsheetobject("CH_antalBreve")

obj1.CopyTableToClipboard true

ExcelDoc.Sheets(1).cells(1,1).select

ExcelDoc.Sheets(1).paste

ExcelDoc.Sheets(1).Rows("1:1").Font.Bold = True

set obj1 = nothing

ExcelDoc.Sheets(1).cells.select
ExcelDoc.Sheets(1).Cells.EntireRow.RowHeight = 12.75
ExcelDoc.Sheets(1).Cells.EntireColumn.AutoFit
ExcelDoc.Sheets(1).cells(1,1).select

XLApp.Visible = true

end sub

Anyone with a macro to export chart and who can incorporte it into the above ?

Macro to export Caption

Wed, 17 Mar 2010 11:14:26 GMT

Sorry - the last line should of course be

Anyone with a macro to export caption and who can incorporte it into the above ?

Macro to export Caption

Wed, 17 Mar 2010 12:57:37 GMT

This gets the title of the chart:
set chart = ActiveDocument.GetSheetObject("CH_antalBreve")
set p = chart.GetProperties
msgbox(p.GraphLayout.WindowTitle.v)

Only works though if you set the title explicitly.

/Fredrik

Macro to export Caption

Wed, 17 Mar 2010 13:03:35 GMT

Thanks Fredrik but unfortunately thats not what I'm looking for. I want the caption/title to be exported together with the chart . Your suggestion gives me a message box with the title.

Macro to export Caption

Wed, 17 Mar 2010 13:23:10 GMT

Ah, I misunderstood you.

I think what you're looking for is this:
Go to User preferences (Ctrl+Alt+U), the tab Export and make sure that the checkbox "Include caption and border" is checked for Charts.

Hope it helps
/Fredrik

Macro to export Caption

Wed, 17 Mar 2010 13:34:35 GMT

It is already checked but the title/caption isn't exported. Neither so when I export it by right clicking and export to Excel. I need to export via a macro as I will export 8 charts into Excel in specific places and don't want to right click 8 times and copy them all afterwards into one single Excel sheet. And I wan't to export the caption also to explain to the user what the chart contains.

Macro to export Caption

Wed, 17 Mar 2010 15:41:22 GMT

Ok, I was reading to fast.... I thought you were exporting the charts as images but I guess that's not the case.
I saw now in your macro that you use CopyTableToClipboard and that only copies the data in the table, as the name suggests.
Should you use CopyBitmapToClipboard instead it should work, but then of course you can't edit the data in Excel.
If you need to use CopyTableToClipboard then the following code should help. (you need to replace 'row' & 'column' with the values for the cell where you want the caption to appear:

set chart=ActiveDocument.GetSheetObject("CH_antalBreve")
set p = chart.GetProperties
ExcelDoc.Sheets(1).cells(row,column).Value = p.GraphLayout.WindowTitle.v

Hopefully we'll get it right this time 😉

/Fredrik

Macro to export Caption

Tue, 19 Apr 2011 11:55:56 GMT

Hello guys,

I have included these macro and works fine for charts caption but only if it is a text title.

Once I try to include a calculated title, I encouter an "unexpected error" from the macro.

NB / my expression is working very well as caption title:

='Investment (x 1.000 €) :' & date(v2date1) & ' to ' & date(v2date2) & ' VS ' & date(v1date1)& ' to ' & date(v1date2)

What's my problem.

Thanks for your help in advance.

Macro to export Caption

Tue, 24 May 2011 08:11:21 GMT

Hi Guys

This posted helped me out major!

Thanks