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

Hello monks, i've a problem ungrouping the textboxes in Excel sheets(using OLE module) can anyone give my the expression to do so. waiting for the answer.. as an eg: consider a sheet consisting of many textboxes and some of them are grouped,i want to read the contents of this textboxes. But unless i ungroup these textboxes i cant read. As an example, im just writing the piece of code for printing all the textboxes: 

my $text_box_num = $ws->TextBoxes->Count()
 foreach my $number (1..$text_box_num)
    {
      my $text_box_name = $ws->TextBoxes($number)->{Name};
      my $extract = $ws->TextBoxes($number)->{'text'};
      print "$text_box_name---$extract\n\n";
    }

[download]
-regards aravind

Replies are listed 'Best First'.
Re: ungrouping the Textboxes in excell sheets.
by ELISHEVA (Prior) on Mar 19, 2009 at 12:19 UTC

    This is fundamentally an Excel question, not a Perl question. The best way to answer this is to search the Microsoft Excel documentation.

    The method names and properties for the Excel modules generally parallel the VB documentation, though you may need to do some syntax related translation. You are likely to get better help if you show you've done part of the work by posting the VB version and your best try at translating it to Perl.

    Best, beth

[reply]
      hi beth, thanks a lot for the reply, actually i dint explore much abt VB. ll look on that,, thanks nywayszz
[reply]
Re: ungrouping the Textboxes in excell sheets.
by bellaire (Hermit) on Mar 19, 2009 at 11:51 UTC
[reply]
Re: ungrouping the Textboxes in excell sheets.
by Nkuvu (Priest) on Mar 19, 2009 at 16:08 UTC

    More context, please. Is $ws a worksheet? If so, I'm wondering how this works -- I don't see a TextBoxes object in the Worksheet or Worksheets object (in the Object Browser).

    If you can get a handle to a Range object, though, there is an Unmerge function available to break up grouped cells. You can see this yourself by looking at the Object Browser -- press Alt-F11 in Excel to bring up the VB editor, then F2 to get the Object Browser. Then I usually restrict the search to Excel only (change where it says "all libraries" to "Excel").

    Edit to add: Note that it's very possible that the TextBoxes item is just not part of the version of Excel I have available to me at work (Excel 2000).

[reply]
[d/l]
      hello Nkuvu, yeah $ws is the worksheet im refering,,, this works,. I just did what u adviced, but even i coudn't see the TextBoxes as u said(excel 2003 in using) (sorry i dont know much abt VB too). anyways thanks for the reply you have given,as im new to the idea given by i ll explore a lot on it.
[reply]

        You might be able to just use the Ungroup function I referred to. For example, get the address of the cell you want, and do $ws->Range("A1")->Ungroup();

        Note that the range is a normal Perl string, so you can easily iterate over a number of cells:

        # Pardon the C-ish loop, it's late.
for (my $row = 1; $row <= 10; $row++) {
    $ws->Range("A$row")->Ungroup();
}
# or an alternate if you have an area:
# (cell addresses off the top of my head)
$ws->Range("A1:D46")->Ungroup();

        [download]

        Note that I haven't had to use this function myself, so I'm not 100% positive it's the function you need. But you should be able to do some quick tests -- and look at the help for the function to see what can unmerge cells.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom