# How to avoid losing macros in PHPExcel?

I'm using PHPExcel to read data from a database and put it into a spreadsheet. I also have a template (.xlsm file) to be followed. As everybody knows, PHPExcel doesn't support macros. But I don't want to edit or create the macros. I just wanna make sure they remain where they are. Don't even want to touch them.

So, does anybody know any workaround for that?

I've been working on this for a week now. I already read this and this. The problem with the first solution is that my server is a Debian so I can't try Libxl approch.

In codeplex I've found the following solution on Chads2000's comment, but I aint seem to be able to make it work. I'm desperate.

Thanks all for your attention, in antecipation.

44

Well, I figured this out quite a long time ago: here's the answer. A guy made a workaround. First, he separate the macros files (VBA) from the sheet, then he left the PHPExcel "doing his thing", e then put it back (the macros!). It was enough for me! :D

Wednesday, March 31, 2021

48
excel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);  Should set the row height to 'auto' for row 1. Wednesday, March 31, 2021 answered 9 Months ago 17 if you want to align only this cells, you can do something like this: style = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
)
);

$sheet->getStyle("A1:B1")->applyFromArray($style);


But, if you want to apply this style to all cells, try this:

    style = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) );sheet->getDefaultStyle()->applyFromArray(\$style);

Sunday, August 8, 2021

97

I used to see this a lot with Visual Basic and Crystal Reports; either due to a bad deployment or DLL Hell.

So, my guess is that it's not the code but perhaps some 3rd party controls (OCXs, DLLs) you're using.

Check your References (In VBA IDE, click Tools and References) and to see where each DLL is pointing.

See if any of the dates on the libraries are different than a working machine and you may find the culprit.

Edit

The OP found the problem by following the steps above.

The fix for this particular issue is to unregister mscomctl.ocx:

regsvr32 /u mscomctl.ocx


Then register it:

regsvr32 mscomctl.ocx

Wednesday, August 11, 2021

20

The solution to this issue came down to data types. If you know that your destination Excel, as I did, then you have to use a data type that Excel can convert. I had been using nvarchar(max) which wasn't being brought over to Excel, When I changed the fields to text and char, I was good. I found this answer from Microsoft once I knew what to look for: Microsoft Excel Data Types. There was also a page on limitations: Data Type Limitations. The other piece was that I was using a stored procedure rather than pure SQL although there was also a problem with selecting directly from the table. I tried to load a table rather than relying on the stored procedure with similiar failures. No errors were returned in any of this, it was just no data. Through my testing, here are the text/character type conversions and their success:

text - works
ntext - works
char - works
nchar - works
varchar - failed
nvarchar - failed

Saturday, August 28, 2021