Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Excel VB Controls

by krazken (Scribe)
on May 02, 2006 at 18:27 UTC ( [id://546967]=perlquestion: print w/replies, xml ) Need Help??

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

Does anyone know an easy way to gain access to VB controls that are in a Excel spreadsheet? I have a spreadsheet I am trying to parse, and they have checkboxes all over the place and I am trying to figure out if the checkbox is checked or not. thanks

Replies are listed 'Best First'.
Re: Excel VB Controls
by marto (Cardinal) on May 02, 2006 at 19:37 UTC
Re: Excel VB Controls
by Unanimous Monk (Sexton) on May 03, 2006 at 03:39 UTC
    You'll want to check out the "Shapes" method in the Worksheet object using Marto's Where do I find documentation for the object models? reference.

    Each form control is a "Shape", and its values can be manipulated through the OLEFormat object.

    The following sample code should put you on the right track. It will create a worksheet with a check box, check it, and then uncheck it. (If you're using something other than Excel 2003, you'll need to update the "Microsoft Excel 11.0 Object Library" to the correct version)

    use strict; use Win32::OLE; use Win32::OLE::Const("Microsoft Excel 11.0 Object Library"); # Load Excel objects my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $book = $xl->Workbooks->Add; my $sheet = $book->Worksheets->Add; # Add a check box to manipulate $sheet->Shapes->AddFormControl(xlCheckBox, 0, 0, 250, 250); # Get the name of our checkbox my $chkBoxName = $sheet->Shapes(1)->Name; # Check the check box $sheet->Shapes($chkBoxName)->OLEFormat->Object->{Value} = 1; sleep 2; # Uncheck the check box $sheet->Shapes($chkBoxName)->OLEFormat->Object->{Value} = 0;

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://546967]
Approved by ikegami
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (3)
As of 2024-05-26 08:44 GMT
Find Nodes?
    Voting Booth?

    No recent polls found