Hi guys

I wrote this program that interacts with Excel and it was working pretty darn well. I was off work sick for a week and came back to a fresh windows install and a program that now hangs on this line:

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

with the following error: Invalid cast exception was unhandled.

I have absolutely no idea why this is happening and have been fiddling for ages with no success. Any help would be greatly appreciated. Oh, the program is also not working on other pc's.

Here is a code snippet:

public void MainLoop()                                                                                   //Function to process the files
    {                 
         
          int count1 = 0;

          while (count1 < arraysize) 
          {

             
                  oWB = oXL.Workbooks.Open(openFileDialog1.FileNames[count1], Missing.Value, false, 4, Missing.Value, Missing.Value,             //Opens selected Excel File
                        true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                  oSheet = (Excel.Worksheet)oWB.ActiveSheet;                                                                                   //Makes oSheet the active sheet   

                  filename = openFileDialog1.FileNames[count1];                                                      //Acquires the filename and opens the 'open file' dialog box
                  ProgressUpdate();
                  LoadAllData();                                                                                    //Calls the function to load all the data into an array
                  ProgressUpdate();
              

             

              string files = System.IO.Path.GetFileName(openFileDialog1.FileNames[count1]);

              if ((float)(double)(cells.GetValue(50, 1)) > 0)                                                   //This implements a check to see if the data is in negative values
              {
                                  
                  MessageBox.Show("Data file incorrect. Positive values found in "+files);                      //Error box to tell you there are positive values in the file
                  
                  count1++;                                                                                     //Implements the count in the case statement under DrawChart() otherwise the chart headings will be wrong
                  goto Filerror;                                                                                //If file has positives, it jumps to the end of this proccess thus skipping this file

              }
             
                 
              
              //lowestvalue();
              Find2ndChange();                                                            //Loop to find second change
          
              LoadAllData();                                                              //Puts all the data into an array  
              ProgressUpdate();

              Find1stChange();                                                            //Loop to find first change
          
              DrawChart();                                                                //Function to draw the chart       

             try                                                 /////THIS WORKS LIKE THE FREAKING BOMB! Uncomment when you are done for awesome saving skills!
              {
                  
                  length = filename.Length;
                  length = length - 4;
                  filename2 = filename.Remove(length, 4);                                    //Takes the .csv off the filename

                 
                  oWB.SaveAs(filename2 + ".xls", Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlExclusive, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                   //Resaves the file as an .Xls
                  //oWB.Close(false, false, false);
              }

              catch (Exception)
              {
                  MessageBox.Show("Error: "+ files +" was not saved!" );
              }

              count1++;
          }
          killexcel();
              //Close1();
              count1++;
         
          
          //MessageBox.Show("Processing is complete");
      Filerror: ;

Thanks
Kevin

With your fresh install did you change versions of Microsoft Office? You could be referencing the wrong interop DLL libraries which is a very common problem so double check your files and version.

PS - I love your comments

Hey Sknake

Haha ya I decided to thoroughly comment so I would sort of know what each thing does because my job doesnt require me to program very much so I will forget.

No the Office version is the same- Office 2007.

It is really annoying because I need to get these graphs done as our client needs the report!

Thanks for your reply.

With your fresh install did you change versions of Microsoft Office? You could be referencing the wrong interop DLL libraries which is a very common problem so double check your files and version.

PS - I love your comments

On the line before the crash use the debugger to evaluate this line: oWB.ActiveSheet.GetType() to see what its trying to cast

Hi Sknake

Sorry it's taken so long I've been caught up in other things.

I think it is trying to cast System._ComObject. The Full error reads:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

It seems to me like it's having a problem with the Excel Interop but I dont understand why. I've loaded the Microsoft Interop Reference Library 12.0.


On the line before the crash use the debugger to evaluate this line: oWB.ActiveSheet.GetType() to see what its trying to cast

Take a look at:
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/3dc38dfe-f1d4-4987-9dc4-c0dbfadb137e

Encountered the same problem.
It appeared that not all elements Sheets collections have the type of Excel.Worksheet.
When access element must first check its type.

My guess is you need to:

if (oWB.ActiveSheet is Excel.Worksheet)
{
  //Do stuff
}

Do you have a complex spreadsheet that you are working with, or some kind of non-standard object(s) on it?

Sounds good

Please mark this thread as solved if you have found an answer to your issue and good luck!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.