I have a binary excel file that does run a macro on the “worksheet open” event copying a range of cells for a few thousend times and eliminating those rows at the “before worksheet save” event (in order to keep the saved file small). When I compiled this file without the XLSPAD generated password this file has a sixe of 7,5 MB. However when using the XLSPAD generated password the compilation of the file takes a lot more time and the file size goes up to 42,5 MB. Any ideas what is going on ?
It’s possible that saving the workbook with a password doesn’t store it as binary format. We’ll check that.
It is not about a save secure file but the actual compiled file that becomes so much bigger. And as far as I can see the compiled file is still indicated in windows explorer as a binary excel file.
When you specify a password, XLS Padlock makes Excel save the workbook with the password in a temporary folder and then uses this workbook for EXE conversion. We don’t touch the original file. I’m not sure whether this temporary save is in binary format, even if the original workbook is.
Before compilation the “original” not binary excel file (.XLSM) is 5,8 MB. This file saved as a binary excel file (.XLSB) then is being reduced to 2,9MB and after being compiled without a password in XLSPAD becomes 7,5MB (.EXE) and when adding an automatically generated password becomes 42,5 MB (.EXE). As you can see there is no proportion in it which makes me wonder what is happening
Just for a test: try to disable the macro that is executed in the save event (the one that populates the workbook)
I thought the same thing and therefore I deleted the macro. Now, as I said the macro does reduce the workbook file size (to be specific from 22 MB to 2,9MB in XLSB format) and deleting the macro brings the original size back to 22 MB in binary XLSB format (and 36MB in XLSM format). After compiling without password the new compiled file is around 28MB (.EXE) which means that compiling added 6MB.
Compiled WITH Password the file becomes 42 MB. Now assuming, that compiling keeps adding around 6MB of file size the numbers do add up correctly ONLY IF the compiled file is as you indicated not a binary file (42 = 36 + 6)
Beyond that, using the password in compiling would also mean that in the file WITH macro actually the macro is NOT allowed to be executed and that the file is not being saved as a binary ?
I hope this helps understanding what is happening when the password compilation is selected.
Now maybe macros associated to events are not executed when you run Excel through COM automation interface (which is what XLS Padlock does when creating the EXE file). So if your cleanup macro is not executed, then this would explain the large size.
Agreed, but what does the password do ? … without password it works perfect, and with password it looks like nothing happens.
It’s the password that causes XLS Padlock to save the temporary workbook. To sum up, if a password is specified, the workbook is saved as a password-protected workbook temporarily and compiled. If no password, XLS Padlock uses the original file (since we don’t modify it, we can use it directly).
OK, thanks. You might indicate this issue in the user manual for future reference. It will save a lot of time to know this.
By the way I have just saved the binary file with a password in ordinary excel save as mode and then compiled it without an automatic generated XLSPAD password. The compiled file opens up asking for the password, so I can use that method to provide one extra security loop to my clients.
Thanks for the follow-up and workaround