Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

add_vba_project - Events #60

Closed
mchappell67 opened this issue Mar 31, 2013 · 7 comments
Closed

add_vba_project - Events #60

mchappell67 opened this issue Mar 31, 2013 · 7 comments

Comments

@mchappell67
Copy link

Hi -

First, thanks for writing this - it's great!

Second, I've started using the add_vba_project functionality, and I've successfully used with the insert_button call.

I've created a spreadsheet with a double-click event - see double_click_event.xlsm. I've used extract_vba to get the vbaProject.bin file. I've then used add_vba_project call to import the .bin file and create a new file, import_event.xlsm.

If you view the code in the import_event.xlsm file, you'll see that the objects are duplicated/doubled up. The double-click code is in the 'ThisWorkbook' object, and the event does NOT fire. If I cut/paste the code into the 'ThisWorkbook1' object, the event does fire.

I've tried a few different things such that the code is in the 'ThisWorkbook' object, but it still doesn't work.

My gut tells me that since the vbaProject.bin file is being added to the .xlsm from outside of Excel, that whatever object ID's are in the .bin file are being retained, and when we create a new workbook/worksheet via Perl, a new set of object ID's are also being created, resulting in what appears to be duplicate/extra objects. Since the code is attached to the objects defined in the .bin file, they are not executed since the 'real' objects don't know about the events.

Just wondering if you're aware of any way to create the VBA-source xlsm file such that the object ID's will match the object ID's that are created via the Perl module, or if there is a way to modify the .bin file (prior to importing it) so that the object names/ID's will match what is generate by the Perl module.

Thanks,

Mark Chappell

@mchappell67
Copy link
Author

Sorry - I thought I'd be able to attach the sample .xlsm files.

Let me know if my description is clear enough, or if I can get the files to you some other way.

Mark

@jmcnamara
Copy link
Owner

Hi Mark,

Send the files to jmcnamara@cpan.org and I'll have a look.

John

@jmcnamara
Copy link
Owner

Hi,

Sorry to the delay in getting around to looking at this.

I haven't an exact answer yet but the issue may (or may not) relate to macros that are extracted from Excel 2010. Or it may just be macros that need a codeName reference to the workbook/worksheet that they are using.

Adding a codeName to the workbook/worksheet seems to resolve the issue. Currently there is a workaround using direct access to the has object keys but I will look into it in more detail and add a proper methods and docs when I know a bit more.

Anyway, the lines with the _vba_codename assignments should fix the issue that you are encountering:

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

# Note the file extension should be .xlsm.
my $workbook  = Excel::Writer::XLSX->new( 'import_event.xlsm' );
my $worksheet = $workbook->add_worksheet();


# Add these to link the workbook and worksheet(s) to their VBA names.
$workbook->{_vba_codename}  = 'ThisWorkbook';
$worksheet->{_vba_codename} = 'Sheet1';


$worksheet->set_column( 'A:A', 30 );

# Add the VBA project binary.
$workbook->add_vba_project( './vbaProject1.bin' );

# Show text for the end user.
$worksheet->write( 'A3', 'Double-click a cell' );

I tried this with the example that you sent and it works.

If you have other sheets then add appropriate codeNames.

Let me know how you get on.

John

@mchappell67
Copy link
Author

Works perfectly!

Thanks so much for looking into this and getting back to me. Adds a great bit of functionality by being able to handle events!

Mark

@jmcnamara
Copy link
Owner

Hi Mark,

Thanks for verifying the fix. I still have to figure out a way to have this work in a more transparent way to the end user. So I'll leave the issue open until then.

John

@poyntesm
Copy link

Hi John,

I appeared to be having same issue and the workaround for me was not working. I have Excel 2013 as original source of the VBA code. The extraction appeared to work and I got the vbaProject1.bin file.

When I got the error on pop the error it pointed to "<filename>.xlsm!<macro>", if I tried use the assign marco I could see my macro available as "ThisWorkbook.<macro>" and I could assign this then to the button.

so for me the following also helped.

$worksheet->insert_button('A1', { macro  => 'ThisWorkbook.<macro>', 
                                 caption => 'Press Me', 
                                 width   => 80, 
                                 height  => 40 });

Just as a helper for anyone else who gets to this bug report :)

jmcnamara added a commit that referenced this issue Mar 2, 2015
Added explicit methods to set the vba workbook/worksheet code
names. Also added default names if not specified.

Fix for issue #60.
@jmcnamara
Copy link
Owner

Fixed and documented in version 0.82.

Thanks for the report and input.

Regards,

John

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants