Macro to export Caption

[#1]

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 ?

[#2]

Sorry - the last line should of course be

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

[#3]

Hi

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

[#4]

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.

[#5]

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

[#6]

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.

[#7]

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

[#8]

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.

[#9]

Hi Guys

This posted helped me out major!

Thanks