I'm trying to use a macro to export a chart object to an excel file. The restrictions however are that the file name should be 'Manpower_Project Name.xlsx' where Project Name is derived from a variable ("vProjectName") within the QV dashboard.The tab (Sheet) name should be 'Manpower'. Also, the file should get saved in the same folder location as the QV file. I tried combining some macros I found online as I'm not very familiar with VBscript. Here's the macro I tried:
'// Array for export definitions
set Name1 = ActiveDocument.Variables("vProjectName")
ProjectName = replace(Name1.GetContent.String," ","_") ' This will be used in file name with _
mProjectName = Name1.GetContent.String ' This will be used in Excel Title
tmpPath = ActiveDocument.GetPathName
lastBackSlash = InStrRev( tmpPath , "\" )
path = Left( tmpPath , (lastBackSlash - 1) )
set obj = ActiveDocument.GetSheetObject("CH511")
Name1 = "vProjectName"
aryExport(0,0) = "CH511"
aryExport(0,1) = "MANPOWER"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.DisplayAlerts = False
Dim XLAppgg 'as Excel.Workbook
Set XLAppgg = XLApp.Workbooks.Add
Set XLAppgg = copyObjectsToExcelSheet(ActiveDocument, aryExport)
XLApp.ActiveWorkbook.Saveas ""& path &"\Manpower Benchmark_"& ProjectName &".xlsx"
Msgbox("Export to Excel is completed and saved as Manpower Benchmark_"& ProjectName &".xlsx") ',vbSystemModal + vbInformation,"Export Status"
But it doesn't seem to be correct as it doesn't work. Please help me make the required changes or suggest a script. Thanks in advance! Attaching the QV file for your reference. tamilarasu
Solved! Go to Solution.
You are welcome Phalgun. Have a fantastic day!!
Update: I forgot to keep the cursor in Range "A1" after exporting the file. Please find the updated file.
Hi tamilarasu I have one last question. Let's say my chart is a different pivot table instead which has some columns merged. Is there any way I can modify the macro to export these columns as unmerged cells instead of merged ones? Attaching a sample for your reference. In this pivot table the first 2 columns are merged. Is there a way to export them as unmerged columns? Thank you so much again!