Export objects to excel using macro.

[#1]

Hi ,

I have few text boxes, button and multibox which are taking care of navigation of large amount of pivot tables. I have successfully exported related pivot tables and parameter multibox to excel using their object Ids in macro code written below:-

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = TRUE
Set XLDoc = XLApp.Workbooks.Add
j = ""
j = "Sheet1"

ActiveDocument.GetSheetObject("MB40").CopyTableToClipboard true
XLDoc.Sheets(j).Range("A" & 1).Select
XLDoc.Sheets(j).Paste

ActiveDocument.GetSheetObject("CH452").CopyTableToClipboard true
XLDoc.Sheets(j).Range("D" & 17).Select
XLDoc.Sheets(j).Paste

Now I need to export report title to excel which is either in text box or button. When I try to export say TX31 or BU 31 in excel using same above code, it is exporting some other random object but not TX31 or BU 31. Can't we not export text box or button using this code or I am missing something in code. Please confirm.

Also, parameter multibox is exported properly to excel and working fine until we select multiple values in single parameter. In this case it does not show those multiple selected values in excel. Please confirm how to achieve this.

Thanks,

Kirti

[RSvebeck]

I think GetSheetObject only works for charts and tables. So either you must create a shart or table with your text, or use another method. See attatched example. Best Regards, Robert

[#3]

Hi,

Thanks for responding!!

Also, I can see macro code of your file attached but can't run it to see output and macro is enabled. Please confirm.

Can you please tell me which method should be used to export text box or button?

Regards,

Kirti

[RSvebeck]

Hi.

I have slimmed down the macro and made a new doucment for you that I think will do all you want to have done.

Try this new file instead.

Im using variables to store text for button and textboxes. Then in the macro I get the values from those variables and but them in cell A1 and A2. Next I copy the content of the chart and paste in in cell A10 (and down)

brg

/Robert

[#5]

Hi Robert,

Thanks for this. It is working now.

Can you please help me with other issue too which is mentioned above, let me re-write it for you:-

Parameter multibox is exported properly to excel and working fine until we select multiple values in single parameter. In this case it does not show any selected value for that parameter in excel. Please confirm how to achieve this.

Regards,

Kirti

[RSvebeck]

Great that it is working.

On your second topic, exporting multibox which has multiple values select, I get the same issue. Perhaps this is a bug....

A work around that you perhaps can live with is to add a "Current Selection Object" and export that instead. This I verified works and it will in excel display all your multiple selections.

Best Regards

Robert

[#7]

Hey Robert!!

Even I was thinking of using Current Selection Box , thanks for confirming it!!

But now problem is that if I select many values in single parameter then current selection box shows number of selected values like '8 of 92' instead of showing those 8 selected values comma separated. I don't want this.

Is there any limit of characters in CS box? Is there any setting through which I can remove these limitations? Please help!!

Regards,

Kirti

[RSvebeck]

Kirti, there is always a solution! 🙂

But I don't understand your problem. If I have for instance a dimension called "Months", and I select January, March and December, then my current selection box will display those months as comma separated. Is this not what you want?

/Robert

[#9]

Hi,

I got solution for that. Actually current selection box values were limited to 6, got the setting to be updated in User preferences , General tab.

Anyways, please confirm if current selection box will be exported to excel with the code I am using currently like charts and multibox or I have to use some other technique for the same.

Regards,

Kirti

[RSvebeck]

Kirti,

I'm not quite sure what you want to achieve, so I will explain what I think Qlikview is doing instead, and perhaps that is an answer that will help you...

1. The standard "current selection box" will always display all that is current selected in your document.

Example:

If you have a field called MonthID, and another field called MonthName, and you make selection "10" in the MonthID - current selection box will show MonthID=10, but it will not show MonthName="October", since you have not selected "October".

2. As far as I understand, the export to excel with GetSheetObject, will export exactly what you can see in Qlikview. So if you  do not see what you want in the current selection box, you will not be happy with the export.

It is possible to create your own "Current Selection Box" that will display whatever you like it to display, leaving out selections that you don't want to see etc. And you may then export that object instead.

This is however, much more complicated and I would recomend that you turn to a consultant for solving this - if you need it, or study the forum threads here and you will be able to figure out how to do it yourself, but you must know much more about macros, selections and variables to solve it.

(And I don't have the time to help you with this right now.)

Best Regards,

Robert