EPPlus Invalid Address Format Error

Question

When trying to read an excel worksheet I receive the error Invalid Address format TABLE_QUERY_FROM_MS_ACCESS_DATABASE[BACKORDERS] It happens on this line...

 If package.Workbook.Worksheets.Count = 0 Then

Matter of fact, anytime I access the .Workbook I receive this error which is a complete show stopper for me right now. Now I've read the excel file (and I'm not an excel expert) but when I click on the Formulas tab, then I click on the Name Manager button, a dialog comes up with five columns. "Name, Value, Refers To, Scope, Comment" BackOrders has multiple entries and on all of the records under Refers To is the value =Table_Query_from_MS_Access_Database[BackOrders]

When I click on the referring location I am brought to another Worksheet in the same Workbook and the column BackOrders is highlighted. Does anyone have any idea whatsoever how I can correct this?

Answer 1

Your column name as a space in it. The bug is here: https://epplus.codeplex.com/workitem/14779

A fast work around that worked for me was to just do the failing call twice like so:

var ws;
try{
    ws = package.Workbook.Worksheets[1];
}catch( Exception ){
    ws = package.Workbook.Worksheets[1];
}

In my simple case it sufficed to keep me going.

Answer 2

I believe the real reason is that EPPlus cannot have a named range that has a scope of 'Workbook'. You can use workbook-scoped names using the technique shown in EPPlus - Named Range is not populated, but to just look up a value given a field name, make sure the name is scoped to the sheet.