CODEWITHSUNDEEP
c#excel
Kasper Hansen
Kasper Hansen

Reputation: 6557

Excel automation: Range.Find

I want to implement this method in my c# program. But I am having trouble filling in the appropriate parameters in a line like

long FirstRow = myWorksheet.Cells.Find(
  What:="*", 
  After:=Range("IV65536"), 
  LookIn:=xlValues,
  LookAt:= xlPart, 
  SearchOrder:=xlByRows,
  SearchDirection:=xlNext).Row

Here is the documentation for the Range.Find method.

Range Find(
    [In] object What, 
    [In, Optional] object After, 
    [In, Optional] object LookIn, 
    [In, Optional] object LookAt, 
    [In, Optional] object SearchOrder, 
    [In, Optional] XlSearchDirection SearchDirection, 
    [In, Optional] object MatchCase, 
    [In, Optional] object MatchByte, 
    [In, Optional] object SearchFormat
);

So basically I don't know how to make the appropriate parameter objects.

Update Excel.Range range;

        object What = "*";
        object After = xlWorkSheet.get_Range("A1", "IV65536");
        object LookIn = "xlValues";
        object LookAt = "xlPart";
        object SearchOrder = "xlByRows";
        Excel.XlSearchDirection SearchDirection = Excel.XlSearchDirection.xlNext;
        object MatchCase = System.Reflection.Missing.Value;
        object MatchByte = System.Reflection.Missing.Value;
        object SearchFormat = System.Reflection.Missing.Value;

        range = xlWorkSheet.Cells.Find(
            What,
            After,
            LookIn,
            LookAt,
            SearchOrder,
            SearchDirection,
            MatchCase,
            MatchByte,
            SearchFormat
            );

Gives a "COMException was unhandled: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"

Update #2 Here is the method so far. The only thing missing is to set and return the range.

    public void RealUsedRange()
    {
        int FirstRow = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows,
            Excel.XlSearchDirection.xlNext,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Row;

        int FirstColumn = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByColumns,
            Excel.XlSearchDirection.xlNext,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Column;

        int LastRow = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows,
            Excel.XlSearchDirection.xlPrevious,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Row;

        int LastColumn = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByColumns,
            Excel.XlSearchDirection.xlPrevious,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Column;
    }

Upvotes: 3

Views: 10557

Answers (2)

Codo
Codo

Reputation: 78835

Your next problem are the LookIn, LookAt and SearchOrder parameters. They shouldn't be a string, rather they are similar to the SearchDirection parameter:

object What = "*";
object After = xlWorkSheet.get_Range("A1", "IV65536");
object LookIn = Excel.XlFindLookIn.xlValues;
object LookAt = Excel.XlLookAt.xlPart;
object SearchOrder = Excel.XlSearchOrder.xlByRows;
Excel.XlSearchDirection SearchDirection = Excel.XlSearchDirection.xlNext;
object MatchCase = System.Reflection.Missing.Value;
object MatchByte = System.Reflection.Missing.Value;
object SearchFormat = System.Reflection.Missing.Value;

range = xlWorkSheet.Cells.Find(
    What,
    After,
    LookIn,
    LookAt,
    SearchOrder,
    SearchDirection,
    MatchCase,
    MatchByte,
    SearchFormat
);

Upvotes: 1

Rick Sladkey
Rick Sladkey

Reputation: 34240

Not tested but this gives you the general idea:

long firstRow = myWorkSheet.Cells.Find(
    "*", /* What */
    Range("IV65536"), /* After */
    Excel.XlFindLookIn.xlValues, /* LookIn */
    Excel.XlLookAt.xlPart, /* LookAt */
    Excel.XlSearchOrder.xlByRows, /* SearchOrder */
    Excel.XlSearchDirection.xlNext, /* SearchDirection */
    Type.Missing, /* MatchCase */
    Type.Missing, /* MatchByte */
    Type.Missing /* SearchFormat */
    ).Row;

Since you can't use the optional argument syntax of VB.NET without C# v4, you need to supply all the arguments in order. Supplying null might work for missing args but I'm pretty sure Type.Missing is the right filler. Other than that it's just calling it like you would expect.

Here are some complete C# examples:

Upvotes: 4

Related Questions