Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Create filename using argument of dynamic variable

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

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: Create filename using argument of dynamic variable

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.

2 Replies
whiteline
Honored Contributor II

Re: Create filename using argument of dynamic variable

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.

Not applicable

Re: Create filename using argument of dynamic variable

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