OleDbConnection.GetSchema provides duplicates of sheet from within Addin

Question

OleDbConnection.GetSchema provides duplicates of sheet from within Addin

Sascha Neffe 5

We provide a excel addin to our customers which allows to re-import data from the workbbook back into a database. Apart from some surrounding stuff to manage the link to our core application it is basically a button that opens up OleDbConnection to the current file, get the sheet names and column within each sheet (by using the GetSchema method) and then decides how to re-import based on this knowledge.

This worked fine up until Excel Version 2508 (Build 19127.20264). Beginning from Version 2509 (Build 19231.20156) upwards (tested up to the current preview Version 2510 (Build 19328.20010) the GetSchema method returns each sheet and column twice, which right now breaks our logic afterwards.

The core logic to determine the sheet names is as follows:

public void PublishAll_OnClick(IRibbonControl control)
{
    using var connection = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{Globals.ThisAddIn.Application.ActiveWorkbook.FullName}\";Persist Security Info=False;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"");
    connection.Open();

    var tables = new List<string>();
    using var tableSchemas = connection.GetSchema("TABLES");
    foreach (DataRow row in tableSchemas.Rows)
    {
        var tableType = (string)row["TABLE_TYPE"];
        if (!string.Equals(tableType, "TABLE"))
            continue;

        var tableName = (string)row["TABLE_NAME"];
        tables.Add(tableName);
    }
}

tables will then contain each sheet name twice.

I wanted to ask if there was an intentional change or if this is a bug? As of right now, the Addin is no longer usable for our customers.

Let me know if you need any additional information.

Thank you

3 answers

Your answer

Answer 1

Hello Sascha Neffe,

Thank you for reaching out to Microsoft Q&A forum and providing detailed information about the issue you're experiencing with your Excel add-in. From what I've looked into and tested, this seems to be related to how the OLEDB provider handles Excel files, it's not just grabbing the actual worksheets, but also picking up on named ranges that Excel creates behind the scenes for things like tables, filters, or print areas. Newer Excel updates might be generating more of these automatically, which could explain why you're seeing duplicates now. It's not listed as a specific change in that version, but it's a common quirk we've heard about in dev forums, especially with the older 12.0 provider. To resolve this and restore functionality to your add-in, here's what I'd suggest trying:

Update Your Schema Filtering Logic: Modify your code to target only true worksheet names, which typically end with a $ (e.g., "Sheet1$"). Exclude internal ranges (often containing _). Here's an adjusted version of your provided code:

   public void PublishAll_OnClick(IRibbonControl control)
   {
       using var connection = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{Globals.ThisAddIn.Application.ActiveWorkbook.FullName}\";Persist Security Info=False;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"");
       connection.Open();
       var tables = new List<string>();
       using var tableSchemas = connection.GetSchema("TABLES");
       foreach (DataRow row in tableSchemas.Rows)
       {
           var tableType = (string)row["TABLE_TYPE"];
           if (!string.Equals(tableType, "TABLE"))
               continue;
           var tableName = (string)row["TABLE_NAME"];
           if (tableName.EndsWith("$") && !tableName.Contains("_"))  // Targets actual sheets, skips internals
           {
               tables.Add(tableName.TrimEnd('\'').TrimEnd('$'));  // Clean name for use (e.g., "Sheet1")
           }
       }
       // Proceed with unique tables list
   }

This should cut out the duplicates. Do the same kind of filtering for columns if needed.

Upgrade to a Newer OLEDB Provider: Switch to Microsoft.ACE.OLEDB.16.0, which is more compatible with recent Excel builds. Download the Microsoft Access Database Engine 2016 Redistributable (match 32-bit/64-bit to your Office installation) Update your connection string accordingly:
```
   Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\"{path}\";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";
```

You can also submit feedback directly through Excel's Help > Feedback menu to help prioritize potential fixes.

I hope this information will be helpful to your situation, in case you have any additional concern, feel free to comment below. I be more than happy to assist.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Sascha Neffe 5

Hi,

thanks for your response.

Sadly, your suggestions do not fix the problem. I used OleDb16, but it had the same behavior.

In our actual generated file we have three sheets (Master data, MeDaPro, MeDaPro_ChangeDetection). I looked at the actual underlying XML. In the workbook.xml there is a block

<definedNames>
  <definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'Master data'!$A$1:$O$10</definedName>
</definedNames>

I guess this would be an example of something generated through Excel itself?

Only content from Master data should be imported, so the other two sheets are filtered out already later on.

And I guess I didn't write it out specifically enough. So to make it hopefully a bit clearer:

The code I presented returns 6 DataRow elements. Entries with TABLE_NAME=Master data$, TABLE_NAME=MeDaPro$ and TABLE_NAME=MeDaPro_ChangeDetection$ would each come up twice.

The definedName actually element doesn't show up.

I also created a simple workbook with just one sheet (Sheet1). Executing the code within this workbooks also brings up two table entries with TABLE_NAME=Sheet1$.

The code you suggested would not filter out the duplicates. We could de-duplicate the names, but it would be better to know why a second entry even comes up only after the update and if has a special meaning.

Jack-Bu 4,590 Microsoft External Staff Moderator

Hi Sascha Neffe,

Thanks for getting back to me with more details. I took a deeper dive, and it seems like this is a fresh oddity tied to the latest Excel updates. Unlike the usual named range or filter issues that add unique extras (think "Sheet1$_"), this is just plain identical rows per sheet, which wasn't a thing pre-2509. Nothing in our release notes calls it out as a planned change, and I couldn't spot any hidden meaning to that second entry, it feels more like an accidental repeat than anything deliberates. In the meantime, the most straightforward fix is to de-duplicate the table names after pulling the schema. You can use a HashSet or LINQ's Distinct() to ensure uniqueness. Here's an updated code snippet incorporating that:

public void PublishAll_OnClick(IRibbonControl control)
{
    using var connection = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\"{Globals.ThisAddIn.Application.ActiveWorkbook.FullName}\";Persist Security Info=False;Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\"");
    connection.Open();
    var tables = new HashSet<string>();  // HashSet takes care of duplicates automatically
    using var tableSchemas = connection.GetSchema("TABLES");
    foreach (DataRow row in tableSchemas.Rows)
    {
        var tableType = (string)row["TABLE_TYPE"];
        if (!string.Equals(tableType, "TABLE"))
            continue;
        var tableName = (string)row["TABLE_NAME"];
        if (tableName.EndsWith("$") && !tableName.Contains("_"))  // Keeps the focus on real sheets
        {
            tables.Add(tableName.TrimEnd('\'').TrimEnd('$'));  // Adds the cleaned-up name, skips any dupes
        }
    }
    // If you need a list: var tableList = tables.ToList();
}

This should give you a clean, unique list without breaking your downstream logic.

Hope this help!

Jack-Bu 4,590 Reputation points Microsoft External Staff Moderator

2025-10-20T05:45:19.4566667+00:00

Hello Sascha Neffe,

Could you please confirm if my latest response resolved your concerns, or if you require any additional information?
Jack-Bu 4,590 Reputation points Microsoft External Staff Moderator

2025-10-23T06:25:45.1+00:00

Hi Sascha Neffe,

It’s been a while since our last interaction.

I wanted check if you’ve had a chance to review my latest response. I hope it addressed your questions thoroughly.

If you have any further questions or need additional clarification, please feel free to return and ask at any time.

Answer 3

Boris 25

I also can reproduce that oleDbConnection.GetSchema("TABLES") returns every Schema twice since Office Version 2509 (Build 19231.20156) from October 1st. Prior Office versions work correctly.

This is a serious bug in OleDb or Excel and needs to be fixed! It breaks loading Excel documents in add-ons and external software, and it surely will come up for others, too.

Can you please pass it up to Microsoft development and expedite it?

Share via

OleDbConnection.GetSchema provides duplicates of sheet from within Addin

3 answers

Your answer