none
Type Mismatch error when using Find method in excel

    Question

  • I know that I had posted this before, but no one responded and I still can't find the answer :(.... Can anyone help me out with this??? 

     

    I'm trying to open an existing excel file, search for some text, write some text to the document, and save the document.  Here is my code:

     

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    xlApp.Visible = false;

    String DocumentLink = "whatever.xls";

    if (!System.IO.File.Exists(@DocumentLink))
    {
        Console.Write("\t*DOES NOT EXIST*");
    }
    else
    {
        Workbook wb = xlApp.Workbooks.Add(@DocumentLink);
        Worksheet ws = (Worksheet)wb.Worksheets[1];

        Microsoft.Office.Interop.Excel.Range RangeOfText = (Range)ws.get_Range("A1", "I30");
        Microsoft.Office.Interop.Excel.Range found = (Range)RangeOfText.Find("TextToFind", xlApp.ActiveCell, XlFindLookIn.xlValues, XlLookAt.xlPart, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false, false, false);

        RangeOfText.set_Item(found.Row, "F", "xx");
        RangeOfText.set_Item(found.Row, "G", "xx");
        RangeOfText.set_Item(found.Row, "H", "xx");

        xlApp.DisplayAlerts = false;

        wb.SaveAs(@DocumentLink, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        wb.Close(false, Missing.Value, Missing.Value);

        xlApp.DisplayAlerts = true;
    }

     

    It says that there is an error type mismatch error with the Find method....

     

    Unhandled Exception: System.Runtime.InteropServices.COMException (0x80020005): T
    ype mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFla
    gs flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.Find(Object What, Object After, Objec
    t LookIn, Object LookAt, Object SearchOrder, XlSearchDirection SearchDirection,
    Object MatchCase, Object MatchByte, Object SearchFormat)

    Tuesday, November 07, 2006 3:49 PM

Answers

  • My guess is that the following is not your problem, but it *might* be... and I think it should be corrected in any case:

    For your 2nd argument (in the 'After' parameter) you are passing in 'xlApp.ActiveCell'. However, this is not guaranteed to be compatible with your previous setting for the 'RangeOfText' reference, which you are creating as follows:

    Worksheet ws = (Worksheet)wb.Worksheets[1];
    Microsoft.Office.Interop.Excel.Range RangeOfText = (Range)ws.get_Range("A1", "I30");

    The problem is that if the Workbook has more than one Worksheet on it, then wb.Worksheets[1] might not be the active sheet and so passing in 'xlApp.ActiveCell' as the 'After' cell will fail.

    You should generally pass in a starting cell that is explicitly set to be within the search range. In this case you could use something like:

       RangeOfText.get_Range("A1")

    Or simply omit this parameter by passing in 'Type.Missing' and it will default to starting in the top-left cell of the range.

    I can't say if this is your problem here or not. I'll actually guess that it is not, but well, if it is, this can be the sort of thing that can be tricky to track down (because it's so easily overlooked) and so I thought that I'd mention it...

    Mike

    Wednesday, November 08, 2006 1:20 AM

All replies

  •  Microsoft.Office.Interop.Excel.Range found = (Range)RangeOfText.Find("TextToFind", xlApp.ActiveCell, XlFindLookIn.xlValues, XlLookAt.xlPart, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false, false, false);

    You are passing false as the SearchFormat. I think that is the problem - just pass Type.Missing instead.

    Tuesday, November 07, 2006 5:30 PM
  • No, that still didn't work.  I tried...

    Microsoft.Office.Interop.Excel.Range found = (Range)RangeOfText.Find("TextToFind", xlApp.ActiveCell, XlFindLookIn.xlValues, XlLookAt.xlPart, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false, false, Type.Missing);

     

    and I tried this...

     

    Microsoft.Office.Interop.Excel.Range found = (Range)RangeOfText.Find("TextToFind", xlApp.ActiveCell, XlFindLookIn.xlValues, XlLookAt.xlPart, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);

     

    and I tried this...

     

    Microsoft.Office.Interop.Excel.Range found = (Range)RangeOfText.Find("TextToFind", xlApp.ActiveCell, XlFindLookIn.xlValues, XlLookAt.xlPart, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, Type.Missing, Type.Missing, Type.Missing);

     

    None of them seemed to work... I still got the same error.

    Tuesday, November 07, 2006 5:49 PM
  • Hmm, bizzare. Your original code (slightly modified) works fine for me.

    I have a question - are you running on all English US system?

    Tuesday, November 07, 2006 11:21 PM
  • My guess is that the following is not your problem, but it *might* be... and I think it should be corrected in any case:

    For your 2nd argument (in the 'After' parameter) you are passing in 'xlApp.ActiveCell'. However, this is not guaranteed to be compatible with your previous setting for the 'RangeOfText' reference, which you are creating as follows:

    Worksheet ws = (Worksheet)wb.Worksheets[1];
    Microsoft.Office.Interop.Excel.Range RangeOfText = (Range)ws.get_Range("A1", "I30");

    The problem is that if the Workbook has more than one Worksheet on it, then wb.Worksheets[1] might not be the active sheet and so passing in 'xlApp.ActiveCell' as the 'After' cell will fail.

    You should generally pass in a starting cell that is explicitly set to be within the search range. In this case you could use something like:

       RangeOfText.get_Range("A1")

    Or simply omit this parameter by passing in 'Type.Missing' and it will default to starting in the top-left cell of the range.

    I can't say if this is your problem here or not. I'll actually guess that it is not, but well, if it is, this can be the sort of thing that can be tricky to track down (because it's so easily overlooked) and so I thought that I'd mention it...

    Mike

    Wednesday, November 08, 2006 1:20 AM
  • WOW!  That actually worked!  I'm quite shocked, to be honest.... :)

     

    I'm happy that it is working, but I still can't figure out why that you throw a type mismatch error... whatever, as long as it works!

     

    Thanks :)

    Thursday, November 09, 2006 7:03 AM