Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
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.