Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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