Re: Win32::OLE to write macro to Excel workbook?

Lynn. Rickards Sun, 01 Jan 2006 22:16:12 -0800

Jan Dubois wrote:

On Thu, 29 Dec 2005, Lyndon Rickards wrote:

I'm looking to add some controls to a workbook, not possible using
Spreadsheet::WriteExcel, with which I create the workbook.

Advice, pointers anyone? Specifically Activate and deActivate routines
to be written to ThisWorkbook and each of it's worksheets.

Recording a macro adding code, just gives an empty sub :-(



I once wrote some code to add a macro to the normal.dot template in Word.
I assume the code for Excel is pretty similar, given that both use
VBA to manage the macros.  I remember that it took a lot of trial and
error to figure out how to use the VBA object model as not everything
works as you would expect it to work.

Anyways, here is the code from a talk I gave at The Perl Conference 3
in 1999 (link will probably get wrapped):

http://opensource.activestate.com/authors/jandubois/Perl/TPC3/fun.html#Using_PerlCOM_Babelfish_for_Mic

If you manage to get it to work with contemporary versions of Excel,
please post some working code back to the mailing list. :)

Cheers,
-Jan


You were right about the trial and error ;-)

With the inspiration from your example, some additional advice from
microsoft.public.excel, including another informative reference -
http://www.cpearson.com/excel/vbe.htm plus too much time poking thru
the Excel VB Object Browser I was able to achieve what I need and hack
out the following example which works at least with Excel 2000/2003

BTW pardon my terminology - component, module...this M$ and VB stuff
is confusing.

 - Lynn.

#!perl -w

use strict;

# This first loads constants from VBIDE - and is I think the
# only line that will wrap in posting...

use Win32::OLE::Const 'Microsoft Visual Basic for ApplicationsExtensibility';

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');

$Excel->{'Visible'} = 1;

# Note my test workbook has a number of worksheets that have
# been renamed. For writing VB code to their Component
# modules, they will nevertheless be addressed by their
# interbal names - 'Sheet1' etc.
my $excelFile = "c:\\devel\\devel.xls";

my $ThisWorkBook = $Excel->Workbooks->Open($excelFile);


# Routine to list all worksheets by name as labeled
# Does nothing else...
my $enumSheets = Win32::OLE::Enum->new($ThisWorkBook->Worksheets);

while (defined(my $Sheet = $enumSheets->Next))
        {
        print "Listing...      $Sheet->{Name}\n";
        }

# However when we run thru the VBComponents, we see the
# original sheet name... plus any additional modules
# ('Module1' etc) and the ThisWorkbook module.

my $enumC = Win32::OLE::Enum->new($ThisWorkBook->VBProject->VBComponents);

while (defined(my $Comp = $enumC->Next))
        {
        print "Working on " . $Comp->{Name} . "\n";
        my $VBCodeMod =
            $ThisWorkBook->VBProject->VBComponents($Comp->{Name})->CodeModule;

        # Blindly remove all code that may exist in Component...
        # This at least ensures no duplicate subs are written ;-)
        $VBCodeMod->DeleteLines(1, $VBCodeMod->CountOfLines);

        # Add really annoying VB routine to popup when any worksheet is
        # activated. Code string comes from sub worksheetActivateCode
        # below.
        # Only do this for regular worksheet components...
        $VBCodeMod->AddFromString(worksheetActivateCode())
                if($Comp->{Name} =~ /^Sheet[0-9]/);

        # Could use constant sheet types for test above -
        # 'vbext_ct_StdModule' etc.
        #
        # Add a different VB routine to ThisWorkBook...
        $VBCodeMod->AddFromString(workbookActivateCode())
                if($Comp->{Name} =~ /^ThisWorkBook/);
        }

# Finally add a Module and write some stuff in it. This
# is incidental since I'm not using Modules...
# ...but check for existance first
my $moduleName = "MyModule";
my $newModule;
unless(moduleExists($moduleName))
        {
        my $newModule =
            $ThisWorkBook->VBProject->VBComponents->Add(vbext_ct_StdModule);
        $newModule->{Name} = "MyModule";
        }

my $codeModule =$ThisWorkBook->VBProject->VBComponents($moduleName)->CodeModule;

$codeModule->AddFromString(myModuleCode());

sub moduleExists
{
my $module = shift;

my $enumC = Win32::OLE::Enum->new($ThisWorkBook->VBProject->VBComponents);

while (defined(my $Comp = $enumC->Next))
        {
        return 1 if($Comp->{Name} eq $module);
        }

return 0;
}

sub myModuleCode
{
return( <<'EndOfSub');
' Just a comment
' for myModule

EndOfSub
}

sub workbookActivateCode
{

return( <<'EndOfSub');
Private Sub Workbook_Activate()
        ActiveWorkbook.Worksheets(1).Activate
End Sub

EndOfSub
}

sub worksheetActivateCode
{
return( <<'EndOfSub');
Private Sub Worksheet_Activate()
    MsgBox "Welcome!!" & Chr(13)
End Sub

EndOfSub
}

_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

Re: Win32::OLE to write macro to Excel workbook?

Reply via email to