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:

enter image description here

When I click yes, I get another message: enter image description here

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

score 20 · Answer 1 · answered Dec 11 '17 at 13:05

20

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.

answered Dec 11 '17 at 13:05

t_plusplus

4,079
5
45
60

2

Obligatory "me too" comment. Took forever to find. – ericb Feb 22 '18 at 22:48
1

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. – Matthew Haugen Sep 14 '18 at 15:12
Huge time saver! – MartinH Sep 04 '19 at 07:33

score 13 · Accepted Answer · answered Aug 30 '13 at 20:08

13

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.

answered Aug 30 '13 at 20:08

trailblazer

1,421
5
20
43

5

Thanks. For me, it was a `'` in the tab name. – Scott Stafford Apr 02 '14 at 02:24
Wow... caused by `'` in a tab name as well! I'd have never found it – Vland Jan 12 '15 at 16:09
Each sheet (tab) needs to have unique name. This error is also shown when 2 or more sheets have the same name. – Michal Minich Oct 24 '18 at 07:38
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. – Robbie Dee Oct 23 '19 at 11:10

score 4 · Answer 3 · answered Dec 11 '20 at 20:12

4

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.

answered Dec 11 '20 at 20:12

Jeremy Hodge

612
3
14

Indeed this worked for me too - I tried removing them via the formula bar as per the answer above, but was still erroring – Tom Folk Apr 18 '23 at 13:41

score 3 · Answer 4 · answered Apr 20 '15 at 18:11

3

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

answered Apr 20 '15 at 18:11

Michael Brennt

731
9
20

score 1 · Answer 5 · answered Nov 13 '16 at 02:54

1

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)"

answered Nov 13 '16 at 02:54

idthappy

31
2

score 1 · Answer 6 · answered Jun 07 '17 at 14:57

1

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

answered Jun 07 '17 at 14:57

George Grainger

172
2
15

score 1 · Answer 7 · answered Dec 17 '18 at 10:32

1

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

answered Dec 17 '18 at 10:32

Thomas

5,603
5
32
48

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

7 Answers7

Linked