Using certain excel functions via macro - setsourcedata

Wed, 29 Oct 2014 19:10:00 GMT

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

topic Using certain excel functions via macro - setsourcedata in QlikView

Using certain excel functions via macro - setsourcedata