How to append excel sheet using win32::OLE

[poolboi]

hi all,

i've got the following program that needs yr help:
[CODE=perl]
use Win32::OLE;

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObjec t('Excel.Applic ation')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.App lication', 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_h our - $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(@parame ters)
{
$sheet->Cells(2,9)->{Value} = [$parameter_in_a rray{$_}];
$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:\\Docume nts and Settings\\clong \\Desktop\\perl \\$save_file_na me.xls") ;
undef $book;
undef $ex;
[/CODE]

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

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

[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..wou ld 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