How can I export title Caption in Excel?

[#1]

Hi,

i need to insert Chart/Report Caption Title when I extract to excel.

I see that I can use a macro for that, but when I test the code I'm getting error like

"ActiveX component can't create object: 'Excel.Application' "

I Use this code:

     Sub ExcelExpwCaption

     'Set the path where the excel will be saved

     filePath = "C:\"

     'Create the Excel spreadsheet

     Set excelFile = CreateObject("Excel.Application")

     excelFile.Visible = true

     'Create the WorkBook

     Set WorkBook = excelFile.WorkBooks.Add

     'Create the Sheet

     Set Sheet = WorkBook.WorkSheets(1)

     'Get the chart we want to export

     Set tableToExport = ActiveDocument.GetSheetObject("LB02")

     Set chartProperties = tableToExport.GetProperties

     tableToExport.CopyTableToClipboard true

     'Get the caption

     chartCaption = tableToExport.GetCaption.Name.v

     'MsgBox chartCaption

     'Set the first cell with the caption

     Sheet.Range("A1") = chartCaption

     'Paste the rest of the chart

     Sheet.Paste Sheet.Range("A2")

     excelFile.Visible = true

     'Save the file and quit excel

     WorkBook.SaveAs filePath

     WorkBook.Close

     excelFile.Quit

     'Cleanup

     Set WorkBook = nothing

     Set excelFile = nothing

End Sub

Is there another way to see title in exported file?

Thanks a lot

regards

Francesca

[#2]

Hello. Your script is written is true. Problem in configuring security levels for macros. In the macro editor, adjust the desired level or press Ctrl-Shift-M.

[#3]

This Code is only for one Report.

I want to Export Caption Title for every report in my dashboards.

How can I do?

[#4]

This is not a trivial task. For each report, write a separate piece of code, or use a loop to retrieve all names and ID reports. It's not easy. In this I will not be able to help you.

[jonathandienst]

If you are running this on a server, then it is possible (even likely) that Excel is not installed on the server. that call requires that Excel is installed and accessible to the user (or service account) running the script.

[#6]

Thanks a lot!

How can i see report name when i export to excel?

[#7]

What do you mean? For example "LB02".

[#8]

LB02 is che object ID.  I need to see Title of the object when export to excel

[#9 · Accepted solution]

In this string from you macros

chartCaption = tableToExport.GetCaption.Name.v ----> you get title

Sheet.Range("A1") = chartCaption -----> put title into excel file