Hello,
We are required at times to write macros in QlikView to create custom excel files. I run into issues where the I cannot get the syntax to work to do certain excel tasks.
For example commands that require := values sent as parameters where the parameter is not an excel constant.
A specific example I have is attempting to set the source data on an existing chart in the excel document.
The microsoft help shows this as (with PlotBy being optional- I am not passing it in):
Charts(1).SetSourceData Source:=Sheets("NA").Range("a1:a10"), PlotBy:=xlColumns
Doing this manually in excel and recording the macro provides:
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("NA").Range("E3:M3,E6:M6")
How do I handle the Source:=Sheets(1).Range("a1:a10") in the QlikView macro?
I have tried a variety of things to no avail.
1) assign the sheets("NA").Range... portion to a string, this does not work
vStr = "Sheets(" & CHR(34) & "NA" & CHR(34) & ").Range(" & CHR(34) & "E3:M3,E6:M6" & CHR(34) & ")"
xlApp.ActiveSheet.ChartObjects("Chart 2").Activate
xlApp.ActiveChart.PlotArea.Select
xlApp.ActiveChart.SetSourceData vStr
2) I've tried passing Source:=Sheets(1).Range("a1:a10") directly, this cause a macro parse issue due to the :=
3) Putting the range directly inline
xlApp.ActiveChart.SetSourceData Sheets("NA").Range("E3:M3,E6:M6")
Can someone please help and tell me how to use commands like this SetSourceData in the QlikView vbscript macro.
Thanks!
Eric
