Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
mrthomasshelby
Contributor 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

Re: Macro to save file with specified Tab Name and File Name

Hi Phalgun,

Have a look at the attached file.

5 Replies

Re: Macro to save file with specified Tab Name and File Name

Hi Phalgun,

Have a look at the attached file.

mrthomasshelby
Contributor III

Re: Macro to save file with specified Tab Name and File Name

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

Re: Macro to save file with specified Tab Name and File Name

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
Contributor III

Re: Macro to save file with specified Tab Name and File Name

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!

Re: Macro to save file with specified Tab Name and File Name

Hi Phalgun,

Have a look at the updated file.