I can not give a final answer but stumbled upon this some years ago too. The following is what I can extract from my C++ source code comments.
OLE DB (and ODBC) are database interfaces that treat Excel files like databases. They use two kind of table types that are of interest here: System tables and normal tables. With Excel drivers system tables are mapped to Excel sheets (with dollar sign) and normal tables are mapped to named ranges (without dollar sign).
When creating sheets with Excel, there will be only a sheet (system table). But when using the ODBC driver to create a sheet, both types are created (this may also apply to OLE DB but I'm not sure).
You don't have specified how you get the sheet names. But I guess you are using something similar to my method
[EDIT:
GetOleDbSchemaTable()
mentioned meanwhile in a comment] You are using something similar to my method for getting the table names:[/EDIT]
I'm enumerating the tables using
ADODB::adSchemaTables
and getting the names from the
TABLE_NAME
field. But this will return all tables (system tables and normal tables) so that there may be duplicate entries. There is also the
TABLE_TYPE
field. But this field always returns
TABLE
(normal table) and never
SYSTEM_TABLE
; even for system tables (sheets). It seems to be a bug. Reference:
OleDbSchemaGuid.Tables Field (System.Data.OleDb)[
^].
So you might get a name without dollar sign when the sheet has been created using ODBC (or maybe also with OLE DB). You might ask your customer if this applies to the failing file to prove it.
However, the name may also refer to a named range instead of a sheet.
The solution that I have used:
I'm enumerating the tables and store the results in a list. If the passed name does not have a trailing dollar sign, it is appended. If that name does not exist in the list, it is added. So I avoid duplicate entries and treat normal table names as sheets too. But this will ignore the existance of named ranges that have names that are not used for sheets.