Geek asks and answers

Excel Automation: Range.Find

I want to implement this method in my C # program. But I am having problems filling in the relevant parameters in a line, for example

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 corresponding parameter objects.

Update           Range Excel.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 "COMException was unhandled: enter mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"

Update # 2 Here is this method. 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;
    }
+3
source share
2 answers

Not tested, but this gives you a 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;

VB.NET # v4, . null , , Type.Missing - . , .

#:

+3

- LookIn, LookAt SearchOrder. , SearchDirection:

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
);
+1

More articles:


All Articles