C# Excel interop: Exception from HRESULT (DISP_E_BADINDEX)
I spoke too soon! This is just a really dumb error. I thought I'd give the solution so others might not fall into the same trap as I did ;-)
To analyse the problem further, I added following code to the constructor:
List<XLS.Worksheet> sheets = new List<XLS.Worksheet>()
foreach(XLS.Worksheet sh in _WSs)
{
sheets.Add(sh);
}
if(_OnXLSEvent != null) _OnXLSEvent(String.Format("\n\tSheets in WB: {0}\n\tFirst Sheet index: {1}, \n\tLast Sheet index: {2}",
_WSs.Count,
sheets[0].Index,
sheets.Last().Index));
This resulted in following log on my machine:
Sheets in WB: 3
First Sheet index: 1,
Last Sheet index: 3
But in following log on the target machine:
Sheets in WB: 1
First Sheet index: 1,
Last Sheet index: 1
Conclusion: the amount of worksheets that are standard added to a new workbook differ from user to user. Something to keep in mind!
Recipe
Updated on June 04, 2022Comments
-
Recipe almost 2 years
I'm trying to deploy an application that works fine on my development pc and some other workstations. However, some users receive an error that I can't seem to grasp.
The program is a C# dotNet app with Excel.Interop functionality (Office 2003).
I seem to be getting a problem with 'indexes'. The weird thing is that this part works perfect on some machines but throws a fatal exception on others... All machines are Windows 7 with Office 2003.
This is the relevant code:
//Change sheet code (index is 1, 2, 3) -> errors at #2 public void ChangeWorksheet(int sheetIndex) { if (_OnXLSEvent != null) _OnXLSEvent(string.Format("TEMP: working on page {0}", sheetIndex)); _WS = _WSs[sheetIndex]; _Shapes = _WS.Shapes; _PageSetup = _WS.PageSetup; if (_OnXLSEvent != null) _OnXLSEvent(string.Format("TEMP: working on page {0}", _WS.Name)); } //Constructor (_App and _WBs are static) public ExcelProcessor(bool SaveAutomatically = false, string SavePath = "") { if (_App == null) _App = new XLS.Application(); if (_WBs == null) _WBs = _App.Workbooks; _WB = _WBs.Add(); _WSs = _WB.Sheets; _WS = _WSs[1]; _Shapes = _WS.Shapes; _PageSetup = _WS.PageSetup; _SavePath = SavePath; _SaveOnDispose = SaveAutomatically; _App.DisplayAlerts = false; ApplyPageSetup(); }
This is the log that I'm receiving:
... Irrelevant 8:52: TEMP: working on page 1 8:52: TEMP: working on page Sheet1 8:52: TEMP: working on page 2 8:52: Error occurred: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) at Microsoft.Office.Interop.Excel.Sheets.get__Default(Object Index) at Classes.XLSInterop.ExcelProcessor.ChangeWorksheet(Int32 sheetIndex) in c:\Users\panjaj\Documents\VS Projects\Projects\Client Projects\ProFormaCreator\ProFormaCreator\Classes\XLSInterop\ExcelProcessor.cs:line 74 at Classes.ApplicationManager.Manager.ProcessSingleDocument(InFileDocument doc) in c:\Users\panjaj\Documents\VS Projects\Projects\Client Projects\ProFormaCreator\ProFormaCreator\Classes\ApplicationManager\ApplicationManager.cs:line 327 at Classes.ApplicationManager.Manager.ConvertFile(String File) in c:\Users\panjaj\Documents\VS Projects\Projects\Client Projects\ProFormaCreator\ProFormaCreator\Classes\ApplicationManager\ApplicationManager.cs:line 172
-
QuickDanger about 8 yearsThank you! Office upgrades caused this in my company.