RE: Win32:OLE and Excel, problems writing from the web

[Hooten, Michael]

This works just fine for me. If you have "use strict;" in effect, you must
quote the following property names, ie:

   'FitToPagesWide'  => 1,   

-------------------------

with ($Sheet->PageSetup,
   FitToPagesWide  => 1,
   PrintGridlines  => 0,
   CenterHeader    => "Empty",
   RightHeader     => "Test Report",
   LeftFooter      => "Done",
   CenterFooter    => "Now",
   RightFooter     => "OK",
   PrintHeadings   => 0,
   FirstPageNumber => xlAutomatic,
   PrintTitleRows  => "1:1");

Remember that when setting an OLE property without the with procedure, the
property name must be enclosed in braces. The following also works:

$Sheet->PageSetup->{FitToPagesWide}  = 1;
$Sheet->PageSetup->{PrintGridlines}  = 0;
$Sheet->PageSetup->{PrintHeadings}   = 0;
$Sheet->PageSetup->{CenterHeader}    = "Empty";
$Sheet->PageSetup->{RightHeader}     = "Test Report";
$Sheet->PageSetup->{LeftFooter}      = "Done";
$Sheet->PageSetup->{CenterFooter}    = "Now";
$Sheet->PageSetup->{RightFooter}     = "OK";
$Sheet->PageSetup->{FirstPageNumber} = xlAutomatic;
$Sheet->PageSetup->{PrintTitleRows}  = "1:1";

-----Original Message-----
From: Murphy, Land [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 22, 2002 4:34 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Win32:OLE and Excel, problems writing from the web


Edit: Trying this again, I posted the incorrect script version. My
apologies.

I'm having problem getting Excel to format the page correctly. 
I've commented out the lines that don't work and the script runs perfectly.
Any idea why those particular (PageSetup) items are troublesome?
This is for an intranet reporting piece that I am trying to put together to
replace an IBI product 
called WebFocus.  

Any ideas appreciated.  Even with the code that does work. I'll be the first
to admit I'm a novice and a hack.
Thanks,
Land
 

#####Script Below##############
use CGI qw(:standard :netscape);
use DBI;
use Win32::OLE qw(in valof with);
use Win32::OLE::Const 'Microsoft Excel';

$date = '2002/04/01';
%attr = (PrintError => 0,RaiseError => 0);
$database='dbi:mysql:database=helpdesk:host=192.168.212.30';
$user='root';
$password='';
$dbh = DBI->connect($database, $user, $password) or die print $dbh->errstr;
$select = qq|Select
case_number,received_date,user_id,type,current_status,resolved_date,help_des
k_person
                        From hd_log Where resolved_date >= '$date' Order by
case_number|;   
$sth = $dbh->prepare($select);
$sth->execute or die print $dbh->errstr;
$sth->bind_columns(\$case,\$rec_date,\$id,\$type,\$status,\$end_date,\$staff
);
$Win32::OLE::Warn = 3;
my $Excel =  Win32::OLE->new('Excel.Application','Quit');
$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Add; 
my $Sheet = $Book->Worksheets(1);
my $Range = $Sheet->Range("A1:G1");
$Range->{Value} = [qw(Case Opened User Type Status Date Staff)];
$Range->Font->{Bold} = 1;
$row = 2;
#################
#PROBLEM AREA   #
#Won't Work!!!  #
#################
###############################################
#$Sheet->PageSetup->{FitToPagesWide} = 1; 
#with ($Sheet->PageSetup, 
#       FitToPagesWide=>1, 
#       PrintGridlines=>0,
#       CenterHeader => "Empty",
#       RightHeader => "Test Report",   
#       LeftFooter => "Done",
#       CenterFooter => "Now",
#       RightFooter => "OK",
#       PrintHeadings=>0,
#     FirstPageNumber=> xlAutomatic,
#       PrintTitleRows => "1:1");
##############################################

with($Excel->ActiveWindow,SplitRow => 1,FreezePanes=> 1);
$Excel->{Application}->{ActiveWindow}->{DisplayGridlines}=0;
while ($sth->fetch){
$Sheet->Cells($row,1)->{'Value'} = $case;
$Sheet->Cells($row,2)->{'Value'} = $rec_date;
$Sheet->Cells($row,3)->{'Value'} = $id;
$Sheet->Cells($row,4)->{'Value'} = $type;
$Sheet->Cells($row,5)->{'Value'} = $status;
$Sheet->Cells($row,6)->{'Value'} = $end_date;
$Sheet->Cells($row,7)->{'Value'} = $staff;
$row++;
}
$Sheet->Columns->EntireColumn->Autofit;
$Book->SaveAs("e:/dwh_web/temp.xls");
print header, start_html();
print "Success!";                                            

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]