topic Re: Export objects to excel using macro. in QlikView

Export objects to excel using macro.

Tue, 24 Jul 2012 13:44:13 GMT

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

Re: Export objects to excel using macro.

RSvebeck — Tue, 24 Jul 2012 13:50:14 GMT

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

Re: Export objects to excel using macro.

Tue, 24 Jul 2012 14:11:24 GMT

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

Re: Export objects to excel using macro.

RSvebeck — Wed, 25 Jul 2012 06:39:54 GMT

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

Re: Export objects to excel using macro.

Wed, 25 Jul 2012 12:14:28 GMT

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

Re: Export objects to excel using macro.

RSvebeck — Wed, 25 Jul 2012 12:35:54 GMT

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

Re: Export objects to excel using macro.

Thu, 26 Jul 2012 07:12:30 GMT

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

Re: Export objects to excel using macro.

RSvebeck — Thu, 26 Jul 2012 07:27:43 GMT

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

Re: Export objects to excel using macro.

Thu, 26 Jul 2012 08:50:27 GMT

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

Re: Export objects to excel using macro.

RSvebeck — Thu, 26 Jul 2012 09:10:08 GMT

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

Re: Export objects to excel using macro.

Thu, 26 Jul 2012 13:40:46 GMT

Hey,

Thanks for tips!!

Can you tell me if we can update the field names in current selection box at UI level or only option of renaming fields is through script?

Regards,

Kirti

Re: Export objects to excel using macro.

RSvebeck — Thu, 26 Jul 2012 13:49:46 GMT

Hi!

If you mean renaming fields to be shown in the "current selection box", you must do this in the script part.

Write like this at the very end of your script:

RENAME FIELD OldFieldName to NewFieldName;

You may repeat that line for all fields you want to rename.

You can have space in the field name, but use bracets like this:

RENAME FIELD MonthID to [Month Number];

Best Regards

Robert

Re: Export objects to excel using macro.

Thu, 26 Jul 2012 14:33:01 GMT

Hey,

I tried this renaming thing, script was good with this syntax.

But after reloading I could not find my new field name in fields. Can you please tell me where can I find these new names and how to use them in my multibox?

Thanks,

Kirti

Re: Export objects to excel using macro.

RSvebeck — Mon, 30 Jul 2012 06:51:12 GMT

Should work, remember that field names are case sensitive. You may post you document here and I can have a look at it if you like.

Best Regards, Robert

Re: Export objects to excel using macro.

Thu, 24 Jan 2013 07:04:02 GMT

Hi Robert,

Is there a way to replace some text in an exported excel worksheet via a macro ?

Sheet.Columns("C:C").Replace What="qmem", Replacement="",LookAt=1

The above code always throws an error that "Microsoft excel cannot find any data to replace. Check if your search formatting criteria are defined correctly. If you are sure that matching data exists in the workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."

Re: Export objects to excel using macro.

RSvebeck — Thu, 24 Jan 2013 09:44:30 GMT

Hi!

This should do it:

sub ReplaceInExcel()

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = true

Set xlDoc = xlApp.Workbooks.open("c:\replace.xlsx")

Set objWorkSheet = xlDoc.Worksheets(1)

objWorkSheet.Cells(1,1).Value = replace(objWorkSheet.Cells(1,1).Value,"AAA","BBB") 'Replace AAA with BBB in the cell A1

xlDoc.Save

xlApp.Quit

End sub