topic Using ExportBIFF but to same XLS in QlikView

Using ExportBIFF but to same XLS

Fri, 27 Aug 2010 16:00:15 GMT

Hi Folks

I am using the ExportBIFF function to output formatted tables to Excel, however, I would like to be able to do this to multiple worksheets in a single XLS file.

Anybody out there know a way to do it?

Cheers,

Using ExportBIFF but to same XLS

llauses243 — Fri, 27 Aug 2010 17:55:57 GMT

Hi Nigel,

I think the exist ...

Multiple sheets -> a folder with multiple .xls (many books)

Then with a .vbs join multiple .xls (many books) to a book (.xls) with multiple sheet

Good luck, Luis

Using ExportBIFF but to same XLS

stevedark — Sun, 29 Aug 2010 22:46:30 GMT

Hi Nigel,

To have multiple sheets in Excel you will need to use a macro in QlikView. In here you can instantiate an instance of an Excel object and call all of the methods within it. I think it should then be possible to call Copy To Clipboard from the QlikView object model and then a Paste within the Excel object model. You can then call the Add Sheet method within Excel and repeat the copy and paste. With the whole excel model at your disposal you can then also tidy up your output by setting column widths and setting the first row to bold etc.

Hope that helps.

Regards,
Steve

Using ExportBIFF but to same XLS

Wed, 01 Sep 2010 08:03:26 GMT

Hi Steve

This sounds like exactly what I'm after, I don't suppose you have a brief example of a macro that does this do you?

Cheers,

Nigel.

Using ExportBIFF but to same XLS

stevedark — Wed, 01 Sep 2010 22:09:50 GMT

Hi Nigel,

There are a few examples already on QlikCommunity of automating Excel:

http://community.qlik.com/forums/p/26622/113073.aspx

http://community.qlik.com/forums/t/31837.aspx

http://community.qlik.com/forums/p/16523/64532.aspx

The last link has some code from Micahel Slomovich that is the most succinct example I have seen.

Hope that helps.

Regards,
Steve

Using ExportBIFF but to same XLS

Stefan_Walther — Sat, 12 Mar 2011 00:10:01 GMT

... also this may help:

http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

Regards
Stefan