topic Re: Macro to export variable value in Excel in QlikView

Macro to export variable value in Excel

Anonymous — Fri, 22 Sep 2017 06:49:51 GMT

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

Re: Macro to export variable value in Excel

Gysbert_Wassenaar — Fri, 22 Sep 2017 07:20:14 GMT

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)

Re: Macro to export variable value in Excel

Anonymous — Fri, 22 Sep 2017 08:35:49 GMT

no luck with it

Re: Macro to export variable value in Excel

Anonymous — Mon, 08 Jan 2018 12:34:31 GMT

Did you managed to find a solution to your problem? I'm having the same issue too

Re: Macro to export variable value in Excel

Anonymous — Mon, 05 Mar 2018 06:39:27 GMT

sorry for the late reply..i did as suggested by Gysbert. the variable needs to be evaluated first and then exported.