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

Could someone please help me with inserting page breaks in Microsoft Excel? I want to insert a page break at row 6. Here is my code so far: 

use warnings;
use strict;
use OLE;
use Win32::OLE::Const 'Microsoft Excel';

my $excel      = CreateObject OLE 'Excel.Application';
my $workbook   = $excel    -> Workbooks -> Add; 
my $sheet      = $workbook -> Worksheets("Sheet1");

$sheet -> Activate();
$sheet -> Range("6:6")  -> Activate();
$sheet ->  HPPageBreaks -> Add();

[download]
The problem is the last line. I receieve the error message "Can't call method 'HPPageBreaks' on an undefined value." Now, the VB code to do this is:
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

I don't know how to translate this. How do I define "ActiveWindow" and "SelectedSheets?" What parameters do I give "Add()?" I'm sorry if my questions are being phrased incorrectly. I am fairly new to Perl. Any help would be greatly appreciated.

Replies are listed 'Best First'.
Re: excel page breaks???
by Joost (Canon) on Mar 01, 2005 at 17:11 UTC
[reply]
Re: excel page breaks???
by talexb (Chancellor) on Mar 01, 2005 at 17:13 UTC
      I've tried everything, and I can't figure it out.

    Then you should list everything, enumerating what kind of response you got with each approach. That way we won't suggest something that you already tried.

    And, speaking philosophically, if you already tried everything, then how can there be a solution? ;)

    Alex / talexb / Toronto

    "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

[reply]
      I apologize for shouting, incorrect phrasing of questions, etc. This is my first jaunt into the monastery, and I am unaccustomed to its procedures. Now that I have read the rules more thoroughly, I shall phrase my question in a more appropriate manner:

      Here is my code so far: 

      
use warnings;
use strict;
use OLE;
use Win32::OLE::Const 'Microsoft Excel';

my $excel      = CreateObject OLE 'Excel.Application';
my $workbook   = $excel    -> Workbooks -> Add; 
my $sheet      = $workbook -> Worksheets("Sheet1");

$sheet -> Activate();
$sheet -> Range("6:6") -> Activate();

      [download]
      I want to insert a page break at row 6. Now, the VB code to do this is:
      ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

      I don't know how to translate this. How do I define "ActiveWindow" and "SelectedSheets?" What parameters do I give "Add()?" I'm sorry if my questions are being phrased incorrectly. I am fairly new to Perl. Any help would be greatly appreciated.
[reply]
[d/l]

        Given $sheet is
        $workbook->Worksheets("Sheet1")
        or
        $workbook->ActiveSheet

        and given $cell is
        $sheet->Range("6:6")
        or
        $sheet->ActiveCell

        I think it's
        $sheet->HPageBreaks->Add(...)

        For arguments, you could try
        Before => $cell

        I'm really just guessing. MSDN is unusually horrid on this subject.

[reply]
[d/l]
[select]
        Hollis rule for automating Office No. 1
        1. Start your Office-program.
        2. Activate the makro-recorder.
        3. Do what you want to automate manually.
        4. Stop the makro-recorder.
        5. Inspect the new makro and pick out the relevant code.
        6. Translate that code to perl.


        holli, /regexed monk/
[reply]

        All I can suggest is to look at Spreadsheet::WriteExcel for help. There appears to be a set_h_pagebreaks(@breaks) routine there. If you can get that to work with OLE, terrific.

        Alex / talexb / Toronto

        "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

[reply]
[d/l]
[select]
Re: Help inserting page break in Microsoft Excel using OLE
by jdtoronto (Prior) on Mar 01, 2005 at 19:19 UTC
    jobs_ron

    I have no idea what I am talking about, Visual Basic is not permitted here in my shop, even Excel is only barely tolerated!

    ActiveState Perl Dev Kit 6.0 has a little thing call 'VB Script convertor' which is said to turn VB into Perl. Well, I input your line of VB code and here is what it came back with: 

    $ActiveWindow->SelectedSheets->HPageBreaks->Add({Before => $ActiveCell
+});

    [download]
    Sincerely jdtoronto or "The Piano Player"
[reply]
[d/l]
      What would $ActiveWindow be defined as?
[reply]
[reply]

Back to Seekers of Perl Wisdom