I found this link http://www.perlmonks.org/?node_id=153486 to be very
useful for working with Excel spreadsheets.
Thans to cacharbe from perlmonks for posting it.
Gary Trosper
RTSC
"jagdish eashwar" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/28/2007 01:39 AM
To
perl-win32-users@listserv.ActiveState.com
cc
Subject
OLE : transferring data from Oracle to Excel
Hi,
I wish to transfer data from an Oracle table to an Excel workbook. The
Excel cells in which the data is to be input are all named. I
connected to Oracle through DBI and picked up the required data into a
hash called "data", which has the Excel cell names as its keys. I then
connected to Excel through Win32::OLE and was able to insert the data
into the named cells with the following piece of code:
my $Excel = Win32::OLE->GetActiveObject('Excel.Application','Quit')
|| die "\n Please start Excel first and then run the program.\n";
my $Excelfile ="E:/test.xls";
my $Book = $Excel->Workbooks->Open($Excelfile);
my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate();
foreach $key (keys (%data)) {
$Sheet->Range($key)->{Value} = $data{$key}
}
The prerequisite for the above code to work is that all the named
cells should be on a single worksheet. How can I transfer the data if
the named cells are on several worksheets in the same workbook? I
tried using $Book instead of $Sheet, but that gave me a 'member not
found' error. In VBA, if I refer to a cell by its name in a macro on
one of the worksheet objects, I get an error if the named cell is not
on the same worksheet. But if I put the same piece of code in a macro
in the ThisWorkbook object, it works fine. So what I need is the Perl
Win32::OLE syntax for addressing the workbook as a whole and then
accessing the named cells from there.
Thanks,
jagdish eashwar
_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
