DELPHI: Automated EXCEL failure to run FIND on second worksheet

Hi,

I'm having problems using the EXCEL automation method FIND on worksheets other than the first.
Ideally I'd like a routine that searches for a specified string across all worksheets and also the ability to find the next entry. Moving and displaying the appropriate cells as and when found.

I'm using a third party OCX - MSOfficeOCX which allows me to access the xls document via a GetIDispatch call. I've had no success with FIND on this at all. I have however managed to cobble together through trail and error a routine perform the search but it will not work on anything but the first worksheet. Selection other worksheets give me a TYPE MISMATCH error

Belwo is the code. the type mismatch occurs on the FIND

thanks


procedure TOfficeViewerFrame.OnXlsFind(AWorksheet:String; WorksheetIdx:Integer);
var  AExcelApplication: TExcelApplication;
     AExcelWorkbook: TExcelWorkbook;
     AExcelWorksheet: TExcelWorksheet;
     LCID,i: Integer;
     vFindText,vMatchCase: OleVariant;
     Result : Integer;
     AExcelRange: OleVariant;
     iCol,iRow,iLastCol,iLastRow: Integer;
     strWorksheet: String;
     iCnt,icols: Integer;
     bFound,bExit: Boolean;
begin
     Result := -1;
     iCnt   :=  0;

     if(Lowercase(FindText) <> Lowercase(eFind.Text))then
     begin
          FindCount := 1;
          FindText  := eFind.Text;
     end;

     vFindText := eFind.Text;
     vMatchCase := False;

     CoInitialize(nil);
     try
          AExcelApplication := TExcelApplication.Create(nil);
          AExcelApplication.ConnectKind := ckNewInstance;
          AExcelApplication.Connect;
          try
               LCID := LOCALE_USER_DEFAULT;

               AExcelWorkbook := TExcelWorkbook.Create(nil);
               try
                    AExcelWorkbook.ConnectTo( AExcelApplication.Workbooks.Open(ffName,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,LCID) );

                    try
                         bExit    := False;

                         // Check each worksheet
                         for i := 1 to AExcelWorkbook.Worksheets.Count do
                         begin
                              if(Lowercase((AExcelWorkbook.Worksheets[i] as _worksheet).Name) = Lowercase(AWorksheet))then
                              begin
                                   iCol     := 1;
                                   iRow     := 1;
                                   iLastCol := 0;
                                   iLastRow := 0;

                                   if(not bExit)then
                                   begin
                                        strWorksheet := (AExcelWorkbook.Worksheets[i] as _worksheet).Name;
                                        showmessage(format('Worksheet:%s',[strWorksheet]));

                                        repeat

                                             if((AExcelWorkbook.Worksheets[i] as _worksheet).UsedRange[LCID].Find(vFindText,(AExcelWorkbook.Worksheets[i] as _worksheet).Cells.Item[irow,icol]{EmptyParam},Integer(xlValues),xlPart,xlByColumns,xlNext,vMatchCase,EmptyParam,EmptyParam) <> nil)then
                                             begin
                                                  //bFound       := True;
                                                  iCol         := (AExcelWorkbook.Worksheets[i] as _worksheet).UsedRange[LCID].Find(vFindText,(AExcelWorkbook.Worksheets[i] as _worksheet).Cells.Item[irow,icol]{EmptyParam},Integer(xlValues),xlPart,xlByColumns,xlNext,vMatchCase,EmptyParam,EmptyParam).Column;
                                                  iRow         := (AExcelWorkbook.Worksheets[i] as _worksheet).UsedRange[LCID].Find(vFindText,(AExcelWorkbook.Worksheets[i] as _worksheet).Cells.Item[irow,icol]{EmptyParam},Integer(xlValues),xlPart,xlByColumns,xlNext,vMatchCase,EmptyParam,EmptyParam).Row;
                                                  strWorksheet := (AExcelWorkbook.Worksheets[i] as _worksheet).Name;

                                                  if(iRow > iLastRow)then
                                                  begin
                                                       bFound   := True;
                                                       iLastCol := iCol;
                                                       iLastRow := iRow;
                                                       Inc(iCnt);
                                                  end
                                                  else
                                                  begin
                                                       if((iRow = iLastRow) and (iCol > iLastCol))then
                                                       begin
                                                            bFound   := True;
                                                            iLastCol := iCol;
                                                            iLastRow := iRow;
                                                            Inc(iCnt);
                                                       end
                                                       else bFound := False;
                                                  end;

                                                  showmessage(format('Worksheet:%s Row:%d (%d) Col:%d (%d) FindNo:%d (%d)',[strWorksheet,irow,ilastrow,icol,ilastcol,FindCount,iCnt]));
                                                  if((bFound) and (iCnt >= FindCount))then
                                                  begin
                                                       showmessage('focus');
                                                       OnFieldFocusXLS(strWorksheet,iRow,iCol);
                                                       bExit := True; //break; // no need to process the other worksheets
                                                  end;
                                             end
                                             else bFound := False;

                                        until((not bfound) or (iCnt >= FindCount));

                                        if(not bFound)then FindCount := 1;
                                   end;

                              end;
                         end;
                    finally
                         AExcelWorkbook.Close;
                         AExcelWorkbook.Disconnect;
                    end;
               finally
                    AExcelWorkbook.Free;
               end;
          finally
               AExcelApplication.Quit;
               AExcelApplication.Free;
          end;
     finally
          CoUninitialize;
     end;
end;

Open in new window

APE47Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
I know zilch about Delphi, but maybe it helps to activate the worksheet before invoking the Find method?
0
APE47Author Commented:
Hi,

I've found some code that solves the issue for me

Thanks
0
aikimarkCommented:
@APE47

What did you find?
0
APE47Author Commented:
I found the following site that provided me with all the routines I needed

http://www.advdelphisys.com/Code_Lib/ads_Excel.html#ExcelFindValue

Andrew
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.