Macro to save file with specified Tab Name and File Name

[mrthomasshelby]

Hello,

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:

Sub ExportExcel


'Report Month
'// 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"


Dim aryExport(0,3)




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"

End sub

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‌

[tamilarasu · Accepted solution]

Hi Phalgun,

Have a look at the attached file.

[mrthomasshelby]

Works like a dream! Thanks a lot for your time and help Tamil! Have a good day!

[tamilarasu]

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.

[mrthomasshelby]

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!

[tamilarasu]

Hi Phalgun,

Have a look at the updated file.