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
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
ASKER
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.
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.
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!?)
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!?)
ASKER
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
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
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!
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Need to move on and have not been able to resolve the issue.
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.