in reply to Help inserting page break in Microsoft Excel using OLE

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

Replies are listed 'Best First'.
Re^2: excel page breaks???
by jobs_ron (Acolyte) on Mar 01, 2005 at 17:40 UTC
    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]
      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]
        He has (5). He's asking for help with (6).
[reply]

      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]
        Thank you for that advice. My code is now: 
        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(Before => $sheet -> ActiveCell);

        [download]
        I now receive an error for the last line which reads "Can't call method 'Add' on an undefined value." Did I not read your advice correctly>
[reply]
[d/l]

      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]

In Section Seekers of Perl Wisdom