Log In
Register
Home
Excel Expert
March 6, 2023
Offline
I have a few plain vanilla excel files with 5 worksheets. I am trying to automate a process in ArcPro and it bombs out because my Excel files have a hidden sheet in xml format that it rejects. I tried removing them using a snippet of VBA, didn't work. How on earth do you get rid of these things?? 2 attachments, one is the file, the other is a view inside ArcPro of the pesky worksheet.
Thanks so much,
Stacy
See AnswerExcel Expert
March 6, 2023
Offline
I forgot this file too..
Stacy
Trusted Members
October 17, 2018
Offline
Which Excel version?
Excel Expert
March 6, 2023
Offline
Hans,
I'm using Office 365, (Build 16130.20218). And I've tried on Excel 2016.
Trusted Members
October 17, 2018
Offline
Hi, I'm using 2021 and I suppose ArcPro is some kind of compression and decompression like winzip.
If you look inside the files in Excel they're ALL xml files and that's the way Excel is built so if you 'remove' this 'persky file your Excel file will be corrupted
The xls file you attached (older version) does not contain xml files because that's not the new structure of Excel, the xml structure was introduced wirh 2007
Why do you need to get rid of it?
Excel Expert
March 6, 2023
Offline
Hans,
No, ArcPro is the internationally used GIS (Geographic Information System) software platform. It matters not what version of Excel file it is, I've done 365, 2016, and 2007; they all have this xml sheet IF you put a filter on any sheet. Even if you remove the filter, the xml remains. I can copy each individual sheet into a fresh new file, save it and there are no xml hidden sheets. See attached images. 1. is a problem, 2. is a problem, 3. is exactly as it should be.
Each version file with the problem gives it a different name: FilterDatabase or xmnm#FilterDatabase. So, my correction; they aren't xmls but they are xlnm# objects. But the point is, I need them removed and not in the file itself.
Trusted Members
Moderators
November 1, 2018
Offline
They are defined names. You can delete them with some code like this:
Sub ClearNames()
Dim nm as name
On Error Resume Next
for each nm in activeworkbook.Names
nm.delete
next nm
End Sub
Answers Post
1 Guest(s)
