topic Re: variable in macro to set path in QlikView

variable in macro to set path

omyahamburg — Tue, 16 Jul 2013 06:53:45 GMT

I am a real newbie in macros.

I have a variable in my qvw named vVar with the value "2013-06".

I like to use this variable in my export to excel macro to set the file name under which the excel file will be stored.

After extensive searching I am still struggling and don't get it worked.

This is my macro code:

'///////////////////////////

sub Export

Dim aryExport(0,3)

aryExport(0,0) = "CH01"
aryExport(0,1) = "Amount per country"
aryExport(0,2) = "A3"
aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'filePath = "C:\test\test "&vVar&”.xlsx" '// this is not working

filePath = "C:\test\test 2013-06.xlsx" '// manually setting the file name is working

objExcelWorkbook.SaveAs filePath

end sub

'///////////////////////////

Additionally I would like to put the caption into cell "A1" in the excel workbook.

Can somebody help me out ?

Thanks in advance.

rgds

Joerg

Re: variable in macro to set path

Gysbert_Wassenaar — Tue, 16 Jul 2013 07:04:34 GMT

You need to get that value of the variable first and assign it to vVar:

vVar = ActiveDocument.Variables("vVar").GetContent.String

Re: variable in macro to set path

omyahamburg — Tue, 16 Jul 2013 07:11:20 GMT

When I try this code, it gives me the error

”.xlsx" is unknown string

vVar = ActiveDocument.Variables("vVar").GetContent.String

filePath = "C:\test\test "&vVar&”.xlsx"

objExcelWorkbook.SaveAs filePath

end sub

Re: variable in macro to set path

Tue, 16 Jul 2013 07:18:08 GMT

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).Cells(1,1).Value='Title'

And Save as the file.

Regards,

Kabilan K

Re: variable in macro to set path

Gysbert_Wassenaar — Tue, 16 Jul 2013 09:13:51 GMT

You've got a wrong quote in there: filePath = "C:\test\test "&vVar&”.xlsx". Make sure you use the double quotes character. Also, if that's not the problem try using msgbox statements to see what vVar and filepath contain:

msgbox vVar

msgbox filepath

Re: variable in macro to set path

omyahamburg — Tue, 16 Jul 2013 09:20:33 GMT

I got it working (unfortunately still without export of the caption):

sub exportToExcel_Variant1

Dim aryExport(0,3)

aryExport(0,0) = "CH02"
aryExport(0,1) = "Amount by country"
aryExport(0,2) = "A3"
aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

set v = ActiveDocument.Variables("vVar")

filePath = "C:\test "&(v.GetContent.String)&".xlsx"

objExcelWorkbook.SaveAs filePath
'objExcelWorkbook.Close

end sub