How to append excel sheet using win32::OLE

[poolboi]

hi all,

i've got the following program that needs yr help:

use Win32::OLE;
 
        # use existing instance if Excel is already running
        eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
        die "Excel not installed" if $@;
        unless (defined $ex) {
            $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
                    or die "Oops, cannot start Excel";
        }
 
 
        # get a new workbook
        $book = $ex->Workbooks->Add;
    $sheet = $book->Worksheets(1);
 
        # write a 2 rows by 3 columns range
        $sheet->Range("A1:J2")->{Value} = [['Date','Total (IN)','Succ (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ (OUT)','MO(IN)','MO(OUT)'],
                                           [$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];
 
    $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],
                      [$max_pk_msg,$pk_msg]];
 
 
    foreach(@parameters)
    {
    $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];
    $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];
    }
 
        # print "XyzzyPerl"
        $array = $sheet->Range("A2:I1")->{Value};
        for (@$array) {
            for (@$_) {
                print defined($_) ? "$_|" : "<undef>|";
            }
            print "\n";
        }
 
        # save and exit
 
        $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;
        undef $book;
        undef $ex;
 

this code outputs everything to an excel sheet
however i need to append the data
how will i go about trying to determine the last row and insert it?
i considered using a counter but then i still need to determine the new cells of that the new data will go in
tough problem here, anyone pls advise?
thanks

[poolboi]

hm...anyone who can help? thanks alot

[KevinADC]

Sorry, I can't help. I never do anything with Excel and I have no experience with any Excel modules.

[poolboi]

hm...thanks anyway
anyone else who might know by chance?
:)

[nithinpes]

I didn't understand your problem quite well. But if you are just checking if data exists in a given cell and appending to next cell, a counter variable and test loop should work. For ex:

    $j=1;
    foreach(@parameters)
    {
    while(defined $sheet->Cells($j,9)->{Value}) { $j++;}
    $sheet->Cells($j,9)->{Value} = [$parameter_in_array{$_}];
   #####
   } 
 

Here, I'm using fixed column value, since you may want a particular data into particular column. You can use a variable too if required.
Not sure if I understood your requirement properly :(

[WinblowsME]

Try

my $last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};

[poolboi]

hm...basically it's like say for my 1st row my data is:

Car A SHE3442F

then when i wanna insert data of car B, i just append it like below:

Car A SHE3442F
Car B SHA2324A

hm....i try if the last row code given by windblowsME work..
another problem i see is i need to open up my saved file to add in the infomation..would it be workable still if say my first entry of data i save it in "test.xls", then at a later date if i wanna append data at "test.xls", i open the file "test.xls", does the code still works? i'm gonna try it anyway but if u got any suggestions would appreciate it!
thanks dude for the help