Avatar of Bunchil
Bunchil asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
Bunchil

8/22/2022 - Mon
Faustulus

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.
ASKER
Bunchil

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.
Faustulus

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!?)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Bunchil

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
Faustulus

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!
ASKER CERTIFIED SOLUTION
Bunchil

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Bunchil

Need to move on and have not been able to resolve the issue.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.