Home » Developer & Programmer » Forms » deleting an Excel row using an ole2 call
deleting an Excel row using an ole2 call [message #126560] Tue, 05 July 2005 10:57 Go to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I have written a 6i form application that will scan an excel spreadsheet and validate the info in it against an oracle database. The application currently sets a cell in the spreadsheet on each row to indicate if the information is found or missing. What I want to do is delete the found rows so that only the rows for missing data is retained. I wrote the following procedure that I am calling from the main procedure that should delete the specified row. However, the routine is skipping rows and is not deleting all the found rows. I know that the routine should work because when the cell was being set to "found", no cells were skipped. The body of the delete procedure is below. The version of excel is excel 2000. Any help would be appreciated.

PROCEDURE drop_row  (row_num in number,
                     worksheet in ole2.obj_type) is
args ole2.obj_type;
range ole2.obj_type;
hold_range varchar2(20);
  begin
    hold_range := to_char(row_num)||':'||to_char(row_num);
    args := ole2.create_arglist;
    ole2.add_arg(args,hold_range);
    range := ole2.get_obj_property(worksheet,'Range',args);
    ole2.destroy_arglist(args);
    ole2.invoke (RANGE, 'Delete');
    ole2.release_obj (range);
  end;

[Updated on: Tue, 05 July 2005 10:58]

Report message to a moderator

Re: deleting an Excel row using an ole2 call [message #126650 is a reply to message #126560] Wed, 06 July 2005 01:59 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Search this forum for 'ole2' to give you some hints and sample code.

Should you be destroying the arglist before the invoke? I thought you would do it after.

David
Re: deleting an Excel row using an ole2 call [message #126727 is a reply to message #126650] Wed, 06 July 2005 07:12 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Once the range ole object is created, you no longer need the argument list.
Re: deleting an Excel row using an ole2 call [message #127602 is a reply to message #126560] Tue, 12 July 2005 08:26 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I found out how to make it work. Start at the last row in the spreadsheet and go backwards. Every thing is deleted correctly.
Re: deleting an Excel row using an ole2 call [message #127681 is a reply to message #127602] Tue, 12 July 2005 18:05 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Maybe you needed to delete the 'same line' multiple times because each time the line was deleted all the following lines moved 'up'? Jsut an observation.

David
Re: deleting an Excel row using an ole2 call [message #127800 is a reply to message #126560] Wed, 13 July 2005 07:40 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
That was the first thing that I though of. I rewrite the code to not increment the row pointer if I deleted a line, and it didn't work. I am sure that the problem was with the push up in the spreadsheet when I deleted a line, but I couldn't make it work. If I ignored the entire issue by working from the last row to the first, everything worked great. Thanks for the suggestion thought, I appreciate it.
Previous Topic: ifrun60.exe application error
Next Topic: Is Forms6i/Reports6i compatible with Oracle9i
Goto Forum:
  


Current Time: Thu Sep 19 20:20:42 CDT 2024