xlsx error: "Removed Records: Named range from /xl/workbook.xml part" when tried to resolve errors

Question

I have an xlsx file that I generate using SSIS. The data to this file is written through a data flow task where the xlsx file is the oledb destination. I have used the following connection string as an expression for the OLEDB connection:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::ExcelPath] +";
Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"

The file opens just fine before the data flow task. After the data flow task, I get a dialog as follows:

When I click yes, I get another message:

The contents of the xml file are

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">       <logFileName>error072840_02.xml</logFileName><summary>Errors were detected in file   'C:\output\documentId-1.xlsx'</summary><removedRecords summary="Following is a list of removed records:"><removedRecord>Removed Records: Named range from /xl/workbook.xml part (Workbook)</removedRecord></removedRecords></recoveryLog>

I am not able to figure out what exactly is causing this error. Any help will be appreciated. TIA

Clipper

Answer 1

Got it resolved. I don't really know the reason. Did some research and came to know that something related to tab names can cause this issue. I had to tabs with names like 'tab' and 'tab(2)'. May be possible that xlsx treats them as same names.

Clipper

Wow... caused by ' in a tab name as well! I'd have never found it – Beora

Each sheet (tab) needs to have unique name. This error is also shown when 2 or more sheets have the same name. – Titus

Yep - once I narrowed down the tab, I simply renamed it from an acronym to the full name and that solved the problem. As an aside - every time I work on a project with an Excel touchpoint, I know I can kiss goodbye to days/weeks sorting out these sorts of random issues. – Voletta

Answer 2

I had this issue when I was using EPPlus to customise an existing template. For me the issue was in the template itself as it contained invalid references to lookup tables. I found this in Formula -> Name Manager.

I suggest that you check the template if you face this issue.

Kenishakenison

Obligatory "me too" comment. Took forever to find. – Lynellelynett

Save a few hours of your life that I'll never get back from mine: double-check for hidden sheets when doing this, as they, understandably, don't show up in this list, and are probably a bit more likely to have errors anyway. – Deliverance

Huge time saver! – Pendentive

tks u, it's help me <3 – Leckie

Answer 3

In my case, there were over a dozen references to named ranges but they did not appear in the formulas->name manager window. I had to rename the xlsx file to a .zip file, open /xl/workbook.xml, manually remove the entire definedName node, then rename it back to .xlsx. VOILA!

That's two hours of my life I will never get back.

Glenine

Indeed this worked for me too - I tried removing them via the formula bar as per the answer above, but was still erroring – Thaumatology

Answer 4

In my case similar error was caused by sheet (tab) name longer than 30 characters.

Compressor

Answer 5

Maybe a special character in your tab name and you define name range with the tab name. such as tab name is "A（1）", just change it to "A(1)"

Serna

Answer 6

For me personally, spaces/special characters were irrelevant in the tab (Sheet) names. The named range itself cannot have spaces/special characters as it is treated like a variable

Mandamus

Answer 7

For me it was a button with a macro assigned with a value like : 'MyMacro("MyParam")'

Escapade