2 Replies Latest reply: May 19, 2014 3:38 PM by Patrizio Cernetti RSS

    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