RFC: Macro support for Excel::Writer::XLSX

[jmcnamara]

Hi,

I am considering adding pseudo-macro support to Excel::Writer::XLSX.

The mechanism would be to extract one or more macro files from an
existing XLSM workbook and add them to a new Excel::Writer::XLSX file.
A sample application to extract the macros would be provided.
Something like:

./extract_vba somefile.xlsm
Extracting...
VBAProject.bin

Then in a Excel::Writer::XLSX program:

use strict;
use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new( 'sample.xlsx' );
my $worksheet = $workbook->add_worksheet();
...

$workbook->insert_vbaproject('VBAProject.bin');
...

It probably won't be possible to tie the macros to any functionality
in the Excel::Writer::XLSX workbook such as buttons or dropdowns. The
macros would just be in the file if the user needed them.

Comments, ideas, suggestions?

John.
--

[jmcnamara]

Hi,

I forgot to add.

The reason for doing it like this is that the macros are stored in an
undocumented binary format. I.e., it wouldn't be feasible to create
them programmatically.

John.
--

[Hiroshi, YOSHIDA]

Hi,


DBCS compatibility problem is still remained in 'vbaProject.bin'.

Kanji or any Double-byte charactors are written by MS-KANJI or
other LOCAL Code in this file, so when you load macro with the
future "The mechanism", you should open .XLSM with your EXCEL
at first and check whether an imcompatible chars would be found
or not.


--
Hiroshi, YOSHIDA

[jmcnamara]

On Nov 24, 5:45 am, "Hiroshi, YOSHIDA" <kann...@gmail.com> wrote:
> DBCS compatibility problem is still remained in 'vbaProject.bin'.

Hi,

Thanks for that. I'm sure there will be a lot of issues to work out.

Perhaps you can try the pre-release version and let me know how you
get on. See my other post for details.

John

[jmcnamara]

Hi,

I've uploaded a pre-release version of macro support in
Excel::Writer::XLSX to the vba branch of the Github repository.

    https://github.com/jmcnamara/excel-writer-xlsx/tree/vba

There is an example file the imports a VBA project to enable macros:

    https://raw.github.com/jmcnamara/excel-writer-xlsx/vba/examples/add_vba_project.pl

There is also a utility called, extract_vba, that extracts the
required vbaProject binary from an existing Excel xlsm file:

    https://raw.github.com/jmcnamara/excel-writer-xlsx/vba/bin/extract_vba

This utility is installed by default if you do a full "make install".

There are probably issues with this (for example codenames aren't
currently supported). If you try it out let me know how you get on.

John

[Richard Noble]

John;

As the user would have to tie the macro in any way, I don't see the need for this unless there is a method to automatically call macros that auto run on open. 

Keep up the great work.....

[jmcnamara]

On Nov 26, 4:32 am, Richard Noble <rpno...@ibksoftware.com> wrote:

> As the user would have to tie the macro in any way, I don't see the need
> for this unless there is a method to automatically call macros that auto
> run on open.

Hi Richard,

That is a fair point. As it stands there isn't much advantage to
embedding macros in an E::W::X file as opposed to calling them from
another workbook.

However, I think that once the basic mechanism is in place I should be
able to add some form controls to run the macros. The first pass will
be a button element and I may add dropdowns and checkboxes in time
(each of these represents quite a bit of work so I'll have to roll
them out bit by bit).

So, if anyone wants to comment on how they would use macros via form
controls please do.

John.
--

[Richard Noble]

I see that working if the Excel designer ties the macro to a button to compute a result or speed the process of data entry... Have fun....

[Francois Tremblay]

Embedding macros is great.  All I have to do now is copy the scroll bars from another workbook and paste and it works right away!

If you implemented insert_scrollbar() it would make my day!

Thanks,

Francois

On Monday, November 26, 2012 4:28:49 AM UTC-5, jmcnamara wrote:

[jmcnamara]

On Sunday, 25 May 2014 04:54:53 UTC+1, Francois Tremblay wrote:

Embedding macros is great.  All I have to do now is copy the scroll bars from another workbook and paste and it works right away!

If you implemented insert_scrollbar() it would make my day!

Hi Francois,

Unfortunately adding new VBA object types is a *lot* of work since they need to be implemented in VML which is an under-documented XML-ish format that somehow found its way into the Office OpenXML specification.

So it is unlikey that any VBA objects other than "Button" will be added to Excel::Writer::XLSX.

John