save excel 2003 file as excel 2013 macro enabled file -range name problem

Hi All, I have a very large spreadsheet created in Excel 2003 (it has 20000 range names) that work very well. I recently upgraded to excel 2103 and opened the file in 2013 and saved it as a macro enabled 2013 file.

I closed it and reopened the new file in 2013 and I am tod there is an error and the file needs to be repaired to be opened. I click OK and the file opens and I am told that an object error was found and deleted on a sheet. I re saved the file, closed it and I get the same message each time I open the file.

I tried deleting the sheet that was described with the error message and no change in the result.

I printed a list of all range names (using the ASAP addin) and found three range names that I have not seen before but they refer to the sheet described as having an object error (see above).

I want to delete these range names to see if it fixes the problem but the names do not appear on the list shown in the excel 2013 Name Manager so I can't find them to delete them.

Any suggestions on this problem please
LVL 1
BunchilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
I came across this article which doesn't answer your question, but it may give you some fresh ideas on what the problem might be and how to tackle it.
In short, the idea would be to try and identify the names that might cause a problem and rename them before attempting conversion. This concept looks more promising to me than attempting repair of the converted workbook.
0
BunchilAuthor Commented:
Hi Faustulus, thanks for your response. When I create a list of range names using the ASAP addin I can see the range names that I assume are causing the problem.

My problem is that they are names I did not create and those names do not show in the Excel 2013 Name Manager so I cannot see them there to delete them.

I only know that they are there from my range name list created using the ASAP addin.
0
FaustulusCommented:
Hi Bunchil,
You know that Excel made changes, but it is a presumption that you know which changes were made and you don't know why. In other words, there is no guarantee that your workbook will be OK after you delete the ranges which shouldn't be there because you can't know if those are all the changes that were made, nor whether Excel actually needs those changes for some reason. Deleting them may open other holes.
This is the argument with which I advocate not to attempt repair. Instead, try to give the converter a workbook that doesn't pose problems to it.
I don't know how Excel converts your workbook. However, I can think of several potential problems. One is the range name (as pointed out in the article I quoted earlier). Another is the range referred to. With 20,000 names there might be some that aren't functional. The third would be the sheer quantity of names.
Accordingly, you could check all your names, check all the references, and try to convert sections of the workbook and re-assemble them after conversion. You might write code to replace all or some names with actual references and, after conversion, replace the references with names again. I'm not sure that this is possible. It would depend upon how the names are used in your project.
(Of course, you tried using code to delete the ones you don't like!?)
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

BunchilAuthor Commented:
Hi Faustulus, thanks again for your response. When I try to resave the file in excel 2003 format the compatibility checker tells me the problem is in the Name Manager so is a named range problem.

From the ASAP named ranges report I found three ranges that I did not know so but could not see in the Name Manage. I used those names to create ranges in the workbook and then deleted those three range names.

Using ASAP again I could see that those ranges were now gone but I still have the same problem except the compatibility report suggests there is one range name that is a problem.

Using the ASAP report I have checked the reference for every name in the workbook and all appear OK.

The work book is very large and works extremely well in Excel 2003. I am reluctant to consider a significant work load to make it operational in 2013 - not a good advert for Excel 2013.

Thanks again
0
FaustulusCommented:
Hi,
I had a similar problem once, not with named ranges but conditional formatting. I never got it fixed until I removed all CF from the converted workbook. Every time I thought it was finally repaired the same mistake turned up again.
Not a good advert for Excel, but if your next Excel version will be 2023 perhaps it is bearable. I wish you the best of luck!
0
BunchilAuthor Commented:
Thank you for your comments. I have not resolved the issue despite deleting the sheet that nominally gives rise to the problem so I continue to save the program in 2003 to avoid the problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BunchilAuthor Commented:
Need to move on and have not been able to resolve the issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.