Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
appi_priya
Contributor III
Contributor III

Macros issue

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.

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

8 Replies
Chanty4u
MVP
MVP

marcus_sommer

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

appi_priya
Contributor III
Contributor III
Author

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.

avinashelite

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

marcus_sommer

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

appi_priya
Contributor III
Contributor III
Author

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.

marcus_sommer

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

appi_priya
Contributor III
Contributor III
Author

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.