topic Re: Macros issue in QlikView

Macros issue

appi_priya — Thu, 14 Apr 2016 07:22:17 GMT

Hi Experts,

I need a help for one a report. There's an export button on my report which should send the current selection to excel if clicking on it. Not required to do a send to excel. But the issue is it's excluding 3 of the values from a field and then giving the excel sheet saved directly on the desktop which shouldn't happen.

the macros code is as follows:

sub exportToExcel

dim o
ExportPath = ActiveDocument.Variables("vExportPath").GetContent.String
ObjectID = ActiveDocument.Variables("vObjectID").GetContent.String
ChartName = ActiveDocument.GetSheetObject(ObjectID).GetCaption.Name.v
Set o = ActiveDocument.GetSheetObject(ObjectID)
o.ExportBiff ExportPath & ChartName & ".xls"

Set o = nothing

end sub

where the value for vExportPath = =if(vExportPhysicalFolder = 'My Desktop', vExportPath1, vExportPath2)

and the value for vObjectID = 12Month , then to further drill down vExportPath1 = ='C:\Users\'&Subfield(OSUser(), '\', 2)&'\Desktop\'

and vExportPath2 = ='C:\Users\'&Subfield(OSUser(), '\', 2)&'\Documents'

Request you to please provide some inputs on it.

Re: Macros issue

Chanty4u — Thu, 14 Apr 2016 08:46:08 GMT

chk dis

Export to Excel

Re: Macros issue

marcus_sommer — Thu, 14 Apr 2016 09:05:25 GMT

It's not quite clear what you mean with:

But the issue is it's excluding 3 of the values from a field and then giving the excel sheet saved directly on the desktop which shouldn't happen.

Did it mean that the macro generally worked unless for certain objects or didn't it work?

- Marcus

Re: Macros issue

appi_priya — Thu, 14 Apr 2016 09:41:34 GMT

Hi Marcus,

Yes the macros worked but one of the field say Field A has 10 values in it and out of 10 its excluding 3 of the values and giving me the details of just 7 values from the Field A.

Re: Macros issue

avinashelite — Thu, 14 Apr 2016 09:45:47 GMT

could you demonstrate the issue with sample outputs or snapshots ?? so that it will be helpful for us to understand

Re: Macros issue

marcus_sommer — Thu, 14 Apr 2016 10:59:32 GMT

You mean if you looked on the chart in qv it has 10 rows and if it's exported without any further selections or actions it has only 7 rows? If yes, I think you exported the wrong object. This meant you need to check and compare the object-id against your (possible) variable-values.

- Marcus

Re: Macros issue

appi_priya — Thu, 14 Apr 2016 11:36:15 GMT

Hi Marcus,

The macros code has a variable vObjectID set to "12 month" but the data from where I am exporting is a straight table and has the ObjectID as CH90. So do you mean to say that since the object ids differ as 12 month and CH90 it's filtering out the 3 values out of 10 and giving me just 7.

In such a case I need to figure out the ObjectID which is set as 12 month and should ensure that it has all the 10 values because in macros its referring to vObjectID set to 12 month.

Just as an info I also tried to change the vObjectID set to 12 month as CH90 but it didn't work.

Re: Macros issue

marcus_sommer — Thu, 14 Apr 2016 12:10:28 GMT

Have also a look on the caption name of these table then if it's empty or contained space or other special chars your:

ExportPath & ChartName & ".xls"

wouldn't return a valid path.

- Marcus

Re: Macros issue

appi_priya — Fri, 15 Apr 2016 06:10:17 GMT

Hi Marcus,

After changing the ObjectID in the macros code to the table I wanted to extract the data from I got the excel reading all the 10 values from the table. earlier it was reading the ObjectID as 12Month which was no where in my report.

Thanks for the guidance.