Create filename using argument of dynamic variable

[#1]

Hi

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

    next

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

    XLDoc.SaveAs NewFileName

    set oXL=Nothing

end sub

[whiteline · Accepted solution]

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.

[#3]

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