Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
sanjyotpatkar
Creator III
Creator III

Macro to export variable value in Excel

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

4 Replies
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
sanjyotpatkar
Creator III
Creator III
Author

no luck with it

mihai_iov
Creator II
Creator II

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

sanjyotpatkar
Creator III
Creator III
Author

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