Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

Macro to save file with specified Tab Name and File Name

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Hi Phalgun,

Have a look at the attached file.

View solution in original post

5 Replies
tamilarasu
Champion
Champion

Hi Phalgun,

Have a look at the attached file.

mrthomasshelby
Creator III
Creator III
Author

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

tamilarasu
Champion
Champion

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
Creator III
Creator III
Author

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
Champion
Champion

Hi Phalgun,

Have a look at the updated file.