Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a macro in QV to export a variable value in Excel.
Below is my code. When I export the variable value in Excel I am getting string value i.e. formula of the variable instead of value of the variable.
Variable name is vAging_WeeklyReport_LastWeek. Excel sheet gets the "{"$(=max(Last8WeeksReferencedateOpened))"}>}Aggr($(vAging_Incidents_weekly),Last8WeeksReferencedateOpened)),'#0.00')" as a text instead of a value. Can somebody tell me whats going wrong and how do I get this as a value instead of a string?
private sub macro_exportagingvalues(expSelection)
dim xlapplication
dim xlsheet
dim Filename
Filename = "C:\Users\43862460\Desktop\Macros test to export object.xlsm"
set xlapplication = createobject ("Excel.Application")
xlapplication.Visible = True
xlapplication.Workbooks.Open(Filename)
set xlsheet = xlapplication.worksheets(1)
set vAging_WeeklyReport_LastWeek = ActiveDocument.Variables("vAging_WeeklyReport_LastWeek")
vAging_WeeklyReport_LastWeek = vAging_WeeklyReport_LastWeek.getcontent.string
for j = 0 to expSelection.count-1
Select case expSelection(j).Text
Case "HSBCNET"
xlsheet.range("B2").value = vAging_WeeklyReport_LastWeek
Case "Connect Heritage"
xlsheet.range("B3").value = 3
End Select
next
xlsheet.Saveas FileName
xlsheet.Application.quit
set xlapplication = nothing
set xlsheet = nothing
end sub
You want the string evaluated so use the Evaluate method of the Document object.
Perhaps something like this:
vAging_WeeklyReport_LastWeek = ActiveDocument.Evaluate(vAging_WeeklyReport_LastWeek.getcontent.string)
no luck with it
Did you managed to find a solution to your problem? I'm having the same issue too
sorry for the late reply..i did as suggested by Gysbert. the variable needs to be evaluated first and then exported.