Create filename using argument of dynamic variable

Mon, 19 May 2014 15:20:31 GMT

I'm writing a macro to export in excel objects from a qvw. The filename of the exported excel needs to be dynamic, using a variable calculated in the qvw. Here below is my macro.

Variable in qvw is defined as: varExportYear=maxstring({altYear} LOADYEAR)

The macro below creates filename as: "C:\filename_maxstring({altYear} LOADYEAR).xlsx"

I need the value of varExportYear to be applied instead. Say currently varExportYear=2014, the filename should be "C:\filename_2014.xlsx"

Thanks!

sub LaunchYearXL

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

NewFileName="C:\filename_"&varExportYear&".xlsx"

set oXL=CreateObject("Excel.Application")

oXL.visible=True

set XLDoc=oXL.Workbooks.Add

'---------------------------------------

aSheetObj=Array("CH88","CH92","IB04","CS01","CS15")

'---------------------------------------

for i=0 to UBound(aSheetObj)

oXL.Sheets.Add

Set oSH = oXL.ActiveSheet

oSH.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))

obj.CopyTableToClipboard True

oSH.Paste

sCaption=obj.GetCaption.Name.v

set obj=Nothing

oSH.Rows("1:1").Select

oXL.Selection.Font.Bold = True

oSH.Cells.Select

oXL.Selection.Columns.AutoFit

oSH.Range("A1").Select

oSH.Name=left(sCaption,30)

set oSH=Nothing

'---------------------------------------

XLDoc.SaveAs NewFileName

set oXL=Nothing

end sub

Re: Create filename using argument of dynamic variable

whiteline — Mon, 19 May 2014 19:04:50 GMT

Hi.

It seems that either you have declared variable with 'set' or without equal sign.

.GetContent usually returns the result of calculation if there is a formula.

Re: Create filename using argument of dynamic variable

Mon, 19 May 2014 19:38:50 GMT

Great catch

The variable in qvw was defined as "maxstring({altYear} LOADYEAR)" instead of "=maxstring({altYear} LOADYEAR)"

Apparently qvw can interpret there is a function, while the macro needs the "=" to perform the calculations

topic Re: Create filename using argument of dynamic variable in QlikView

Create filename using argument of dynamic variable

Re: Create filename using argument of dynamic variable

Re: Create filename using argument of dynamic variable