Win32::OLE and EXCEL

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

I have a perl script which creates an EXCEL spreadsheet and I am having problems with formatting. I need to have two rows designated as title rows and repeat on each page. I also need to manuallty insert a page break. I have been using the EXCEL VB Object browser, but I have had no success. Any help would be greatly appreciated. Thanks.

   
   my $ex = Win32::OLE->GetActiveObject'Excel.Application')
      || Win32::OLE->new('Excel.Application', 'Quit');

   $book = $ex->Workbooks->Add;
   $sheet = $book->Worksheets(1);
# designate row 1 and 2 as header rows 
   $sheet->PageSetup->{PrintTitleRows} => "1:2";
# add a page break
   $sheet->HPageBreaks->Add({After=>ActiveCell});
[download]

Comment on Win32::OLE and EXCEL Download Code

Replies are listed 'Best First'.
Re: Win32::OLE and EXCEL by holli (Abbot) on Aug 08, 2005 at 17:52 UTC
As I always say when it comes to such Office/OLE questions... Use the makro recorder and convert the resulting code! holli, /regexed monk/	[reply] [d/l]
Re^2: Win32::OLE and EXCEL by dcutcher (Initiate) on Aug 09, 2005 at 10:11 UTC
I finally got it. The correct code is `$sheet->PageSetup->{PrintTitleRows} = ($sheet->Rows('$1:$2')->Address) +;` [download] Thanks for the help. Now on to adding page breaks. Janitored by Corion: Added closing code tag, as per Writeup Formatting Tips	[reply] [d/l]
Re: Win32::OLE and EXCEL by marto (Cardinal) on Aug 08, 2005 at 16:30 UTC
Hi, By 'title row' do you mean "Window"->"Split" in Excel? Martin	[reply]
Re^2: Win32::OLE and EXCEL by dcutcher (Initiate) on Aug 08, 2005 at 16:41 UTC
Sorry, I mean column headings. I am trying to replicate the Excel feature under File, Page Setup, Sheet, Print Title, Rows to Repeat at Top. In Excel, if I enter $1:$2 in the Rows to Repeat at top, on each page I get row 1 and 2 as column headers.	[reply]
Re^3: Win32::OLE and EXCEL by marto (Cardinal) on Aug 08, 2005 at 17:42 UTC
Hi, Thanks for clearing that up. You can see from this example how to set up the column headings: `# Insert column titles my $Range = $Sheet->Range("A1:E1"); $Range->{Value} = [qw(Time Open High Low Close)]; $Range->Font->{Bold} = 1;` [download] Hope this helps. Martin	[reply] [d/l]
Re: Win32::OLE and EXCEL by davidrw (Prior) on Aug 08, 2005 at 17:28 UTC
shouldn't the value be `'$1:$2' # or "\$1:\$2"` instead of `"1:2"` ? Also, is `$sheet->PageSetup` a hashref or an object? I'm wondering if you need to do `$sheet->PageSetup->PrintTitleRows('$1:$2');` (or maybe a `->set()` method of some kind)?	[reply] [d/l] [select]