topic Re: Export to Excel via macro-number format in QlikView

Export to Excel via macro-number format

Tue, 28 Aug 2012 09:42:45 GMT

Hi All,

I have a macro that exports a straight table to excel but once exported, the number format is ignored. Is there extra code i need to add into the macro to force a full number format?

Attached is a portion of the macro that I'm sure extra code needs to be inserted into.

Thank you in advance

Re: Export to Excel via macro-number format

jagannalla — Tue, 28 Aug 2012 09:51:41 GMT

Hi,

To export the ST table to excel. Why don't you try simple code as per API guide.

Eg:

set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportBiff "C:\test.xls"

Hope it helps you

Cheers!!

Jagan

Re: Export to Excel via macro-number format

Tue, 28 Aug 2012 09:59:17 GMT

Hi Jagan,

The exporting is working fine. I am having an issue with number formatting. When the report is expoted to excel, the comma seperators and decimals are lost. I need to force format within the Macro script but I am unsure how.

Regards,

Ayron

Re: Export to Excel via macro-number format

jagannalla — Tue, 28 Aug 2012 10:08:18 GMT

Change your dimension as =num(F2, '#,##0.##', '.' , ',' ) . Please find the attached file for your reference.

Re: Export to Excel via macro-number format

Tue, 28 Aug 2012 11:20:50 GMT

Still not working. It looks like it has to be forced withing the macro script. Just need to correct code.

Re: Export to Excel via macro-number format

jagannalla — Tue, 28 Aug 2012 11:24:48 GMT

Have you seen my qvw file? Otherwise please provide sample qvw file.

Re: Export to Excel via macro-number format

Wed, 29 Aug 2012 05:24:16 GMT

Hi Ayron,

In the macro script it has to be something like

XLDoc.WorkSheets("Sheet1").Range("A:B").NumberFormatLocal = "#.##0"

decimal "#.##0,00"

percentage "#.##0,00%"

Regards

Vicky

Re: Export to Excel via macro-number format

Wed, 29 Aug 2012 07:17:49 GMT

Thank you very much Vicky! Solved my problem. Works perfectly!!

Regards,

Ayron

Re: Export to Excel via macro-number format

Thu, 18 Oct 2012 10:20:13 GMT

Hi All,

Using this macro we can send data to excel. But is it possible to add dropdown of filter in the exported excel using macro?

- Yojas