Importing a macro into an Excel export

Tue, 01 Nov 2011 20:26:03 GMT

I'm using Stefan Walther's script from http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/ to export a chart into an Excel document (because the built-in export cuts off my cell values at 255 characters). Once I get it into Excel I have a separate Excel macro that I import and run in order to format the document so that it is more presentable.

I was wondering if anyone had any code that would automatically import and run the Excel macro within the newly created document so that I can just distribute the QV document without having to show end-users how to work with Excel macros.

We can assume that the Excel macro is stored in C:\macro.bas and that I know nothing about VBScript because that wouldn't be too far from the truth.

Thanks!

Importing a macro into an Excel export

Wed, 09 Nov 2011 20:37:38 GMT

Figured it out.

objExcelWorkbook.Application.VBE.ActiveVBProject.VBComponents.Import "C:\macro.bas"

objExcelWorkbook.Application.Run "macroname"

I got that pretty early on, but I kept thinking it wasn't right because my macro settings weren't correct in Excel.

See step 7 in the link below for the settings that fixed it for me.

http://support.microsoft.com/kb/219905

topic Importing a macro into an Excel export in QlikView

Importing a macro into an Excel export

Importing a macro into an Excel export