Hi Gary, Thanks for writing in. About 8 months ago, I stumbled upon that link. It is a really good article. It was because of it that I started using Perl with Excel. But I didn't find in it what I'm presently looking for. However, just after reading your mail, while perusing the Excel object model in VBA, I got an idea that I should try using the Application object instead of the Workbook object in order to be able to access all the named cells in the workbook. So I used $Excel in the foreach clause. And it worked! I haven't undestood why, though.
On 1/29/07, Gary D Trosper <[EMAIL PROTECTED]> wrote:
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
