Problem Writing macros in an Excel File

Striker has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Problem Writing macros in an Excel File by hdb (Monsignor) on Feb 03, 2014 at 10:19 UTC
If you are in a Windows environment, then Win32::OLE can do what you want. There is some learning curve though but a lot of material can be found here on PerlMonks, e.g. Using Win32::OLE and Excel - Tips and Tricks.	[reply]
Re^2: Problem Writing macros in an Excel File by Striker (Novice) on Feb 03, 2014 at 11:28 UTC
No I am using CentOS	[reply]
Re: Problem Writing macros in an Excel File by davies (Monsignor) on Feb 03, 2014 at 11:32 UTC
Spreadsheet:ParseExcel and its comrades in arms can't access the macro facilities of Excel, so macros cannot be written when saving, whether with SaveAs or anything else. Win32::OLE works with an installed copy of Excel and can do pretty well everything that Excel can, including saving with existing macros or even editing them. Regards, John Davies Update: the comment in the OP about using CentOS is itself an update made after I had posted the above.	[reply]
Re^2: Problem Writing macros in an Excel File by Striker (Novice) on Feb 03, 2014 at 11:34 UTC
But i am using CentOS.	[reply]
Re^3: Problem Writing macros in an Excel File by marto (Cardinal) on Feb 03, 2014 at 11:39 UTC
Then you may want to investigate the feasibility of automating Open/Libre Office.	[reply]
Re^3: Problem Writing macros in an Excel File by davies (Monsignor) on Feb 03, 2014 at 11:49 UTC
If, as you seem to be implying, centos precludes Excel (I've heard of centos vaguely but don't know what it is or does), then you can't run Excel macros anyway, so why would you worry about them being removed? If you want to run Excel macros, you need a copy of Excel (and that includes user-defined functions, which are a species of macro). If you have that, you can do what you like with Win32::OLE, but not with Spreadsheet::whatever. If you are generating Excel files for other people and want to save macros, you must have Excel, which you can automate with Win32::OLE. Either that, or write your own module to save Excel files with macros. You will have to understand the Excel file format extremely well and have quite a bit of time. I have discussed this with John McNamara, but I don't know enough Perl and, like him, am working on other projects. It's not impossible, but it's likely to be far easier to get yourself a dual boot system with Excel. Another option is one I have used in several places. Get hold of a gash machine that's going to the skip and, provided it has Losedows on it, commandeer it. Put Excel on if it's not already there, plus Perl. Then use that machine for stuff you need Excel to do and your main machine for everything else. If space is tight, get a KVM switch so you can put the gash machine on the floor & use the same peripherals. Regards, John Davies	[reply]
Re^4: Problem Writing macros in an Excel File by Striker (Novice) on Feb 03, 2014 at 12:00 UTC
Re^4: Problem Writing macros in an Excel File by Striker (Novice) on Feb 03, 2014 at 13:27 UTC
Re^3: Problem Writing macros in an Excel File by Anonymous Monk on Feb 03, 2014 at 12:50 UTC
There exists WINE/winelib (How to install Win32::OLE under Linux?) Also lkundrak says OpenOffice::UNO works. Apparently with UNO you can set a Formula in a Cell\|Formula in a Cell among other things	[reply]
Re: Problem Writing macros in an Excel File by Mr. Muskrat (Canon) on Feb 03, 2014 at 16:03 UTC
Have you considered exporting the macros and including them in a new spreadsheet? I haven't used it yet but Excel::Writer::XLSX has a tool, extract_vba, to extract a binary macro file and then you use the add_vba_project method to add your macro file to a spreadsheet.	[reply]