?
Solved

DELPHI: Automated EXCEL failure to run FIND on second worksheet

Posted on 2010-03-31
5
Medium Priority
?
817 Views
Last Modified: 2013-11-10
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

0
Comment
Question by:APE47
  • 2
4 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 29315202
I know zilch about Delphi, but maybe it helps to activate the worksheet before invoking the Find method?
0
 

Author Comment

by:APE47
ID: 29349675
Hi,

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

Thanks
0
 
LVL 46

Expert Comment

by:aikimark
ID: 29365405
@APE47

What did you find?
0
 

Accepted Solution

by:
APE47 earned 0 total points
ID: 29887236
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Six Sigma Control Plans

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question