Preserving VBA code in .xls?

Topics: Developer Forum
Oct 22, 2008 at 11:24 AM
Hi there, first off - thanks for PHPExcel, was very pleased to find it and have been enjoying playing with it.

I have a .xls spreadsheet that acts as a "template", but which contains Visual Basic code and a button on one of the sheets to run this code. When I read it in, fill in the required values, and save it out, the code and this button have gone.

Is there any way to keep the VB code and button in the output file, either by copying it across, or by inserting the values "directly" into the binary of the original template, perhaps?

Many thanks, and keep up the good work.
Graham.
Developer
Oct 23, 2008 at 3:00 AM
I see what you mean. I recall there was a somewhat similar question by tepperware who needed textboxes to survive a read/write. He did some tricks and was apparently able to achieve that. This was for Excel 2007.
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=6516

Your question arises because you are using PHPExcel to generate Excel file from template. That this kind of use would start spreading may or may not have come as a surprise. I don't know. Anyhow, PHPExcel is not optimized for this kind of use. Ideally, a plain read/write should take 0 seconds when no manipulation is done, but this is not the case because all data is always parsed twice.

You have a macro sheet that needs to survive a plain read/write. I am not sure whether it is as simple as just injecting a binary macro sheet. I have not seen anyone doing this, but maybe we should give it a try. If you upload or send me the xls file you are working with, then I may try to do some experiments and report back whether there is success or not.