topic Re: QlikView macro export to Excel percentage formatting in QlikView

QlikView macro export to Excel percentage formatting

Wed, 25 Nov 2020 16:16:04 GMT

Hello

I have exported a Straight Table to Excel via a macro script. The problem is, one of the fields is a percentage field

but I am having problems with the formatting.

Currently in QlikView it looks like this: 15.1% but as soon as I export the Straight Table the field percentage display as 15.10% in

Excel. I want the formatting in Excel also to be displayed as 15.1%. (As soos as Excel sees the field as percentage, it automatically

formats it to two decimal places).

I am unable to use the Copy to Clipboard function in QlikView because I am exporting more than one table onto one Excel Sheet.

I am looking for a way from within the QlikView macros to be able to set the Excel Decimal places for Percentage to 1.

Thanks very much.

adamdavi3s — Thu, 01 Dec 2016 10:17:34 GMT

Just code the necessary macro to reformat the column?

The code will be along the lines of

Columns(1).NumberFormat = "#,##0.0%"

If you cant get qlikview to do what you need, then you can create an excel template with thew macro in it.

When you export, you open the template and use that then just call the macro from Qlikview

Thu, 01 Dec 2016 13:07:38 GMT

Hello this was very helpful.

I tweaked it a little bit, but it definitely put me on the right track.

I used :

range(VarName). NumberFormat = "##,#0.0%"

Thank you

adamdavi3s — Thu, 01 Dec 2016 13:17:30 GMT

No worries at all, I am glad it put you on the right track