Upgrading .xls files to xlsx.
Hi there Experts,
Our office has Excel and Word files created with Office 2003 Suite.
One part of the computers has Excel 2007 while the other has Excel 2003.
When somebody saves an Excel 2003 file using Excel 2007 the below warning appears (it depends from file to file)
Upgrading all computers to Office 2007 and existing files (using save as) to .xlsx and .docx will cause any data loss or not?
Our office has Excel and Word files created with Office 2003 Suite.
One part of the computers has Excel 2007 while the other has Excel 2003.
When somebody saves an Excel 2003 file using Excel 2007 the below warning appears (it depends from file to file)
Upgrading all computers to Office 2007 and existing files (using save as) to .xlsx and .docx will cause any data loss or not?
ASKER
So, an Excel 2003 file with macros and functions will work correctly with Excel 2007 (using save as).
I mean, all functions, macros, common features of Excel 2003 are included in Excel 2007?
I mean, all functions, macros, common features of Excel 2003 are included in Excel 2007?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No I don't have many macros.
I have more functions and hyperlinks that are linked to other excel files.
So if I have for example a hyperlink that links to an exapmle.xls file if
I replace it with the example.xlsx file will the hyperlink work (automatically updated)?
I have more functions and hyperlinks that are linked to other excel files.
So if I have for example a hyperlink that links to an exapmle.xls file if
I replace it with the example.xlsx file will the hyperlink work (automatically updated)?
Mamelas,
Unfortunately, "saveas" may be well be a case that probably would need a (small) macro change. For example, suppose that you have a macro that does the following...
(1) Create a new (default) workbook.
(2) Copy in cumulative data from the previous period.
(3) Add this period's data.
(4) "SaveAs".
There are a few possible issues...
(1) If the "SaveAs" is using one of the dropped formats then it will fail.
(2) If the "SaveAs" is explicitly saving the file in pre-2007 and this month's entries pushed the total number of rows over 65,536 (or columns over 256) then it will fail.
(3) The filename may be specified as .xls rather than .xlsx.
The necessary changes to this macro would be small, but that doesn't get over the fact that they would be needed.
Regards,
Brian.
Unfortunately, "saveas" may be well be a case that probably would need a (small) macro change. For example, suppose that you have a macro that does the following...
(1) Create a new (default) workbook.
(2) Copy in cumulative data from the previous period.
(3) Add this period's data.
(4) "SaveAs".
There are a few possible issues...
(1) If the "SaveAs" is using one of the dropped formats then it will fail.
(2) If the "SaveAs" is explicitly saving the file in pre-2007 and this month's entries pushed the total number of rows over 65,536 (or columns over 256) then it will fail.
(3) The filename may be specified as .xls rather than .xlsx.
The necessary changes to this macro would be small, but that doesn't get over the fact that they would be needed.
Regards,
Brian.
Mamelas,
Again, I'm afraid not - an existing hyperlink will continue to point to the original file, unless you explicitly change it.
Unfortunately, there's no getting away from the fact that you're going to have to test before going live with a universal upgrade. The only comfort I can give you is that the testing will be far more effort than the actual changes required.
Unfortunately, I'll be gone for an hour or so, but if you need more information before then, I'm sure that others will be glad to help.
Regards,
Brian.
Again, I'm afraid not - an existing hyperlink will continue to point to the original file, unless you explicitly change it.
Unfortunately, there's no getting away from the fact that you're going to have to test before going live with a universal upgrade. The only comfort I can give you is that the testing will be far more effort than the actual changes required.
Unfortunately, I'll be gone for an hour or so, but if you need more information before then, I'm sure that others will be glad to help.
Regards,
Brian.
Oh, and your link formulas will need to be changed to replace ".xls" by ".xlsx" (or ".xlsm")
ASKER
Brian my apologies for the misunderstanding.
I understand that when somebody uses a macro it depends from various things if it will work or not.
So my main concern is not the macros.
In order to upgrade I will open all .xls files with Excel 2007 and using "save as" I will save them all as xlsx files.
Then I will delete the old .xls files Right?
So if I have a hyperlink inside a sheet that points to the example.xls, the upgraded file will become example.xlsx the hyperlink then will work or I should edit the hyperlink in order to point to the new type of file?
I understand that when somebody uses a macro it depends from various things if it will work or not.
So my main concern is not the macros.
In order to upgrade I will open all .xls files with Excel 2007 and using "save as" I will save them all as xlsx files.
Then I will delete the old .xls files Right?
So if I have a hyperlink inside a sheet that points to the example.xls, the upgraded file will become example.xlsx the hyperlink then will work or I should edit the hyperlink in order to point to the new type of file?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mamelas,
Think about this way - suppose you decided to rename all your existing spreadsheets by giving them a prefix of "New_". You can see how that would break a lot of your existing formulas. Well, because one of the effects of saving a .xls as a .xlsx (or .xlsm) is to change the filename (actually the extension) it will cause similar problems.
Regards,
Brian.
Think about this way - suppose you decided to rename all your existing spreadsheets by giving them a prefix of "New_". You can see how that would break a lot of your existing formulas. Well, because one of the effects of saving a .xls as a .xlsx (or .xlsm) is to change the filename (actually the extension) it will cause similar problems.
Regards,
Brian.
ASKER
Ok, I did the test.
Lets say that I have the Test.xls that points with a hyperlink to Example.xls.
Then you save as the Test.xls to Test.xlsx and Example.xls to Example.xlsx (and delete the old ones).
If then you open the Test.xlsx and click on the hyperlink it will not open the Exmaple.xlsx.
You have to edit the hyperlink and add at the end the "x" letter in order to link to the Example.xlsx
My problem is that I have many Excel files that are linked eachother and "Find & Replace" option is not working for my case.
Does anybody know how to edit hyperlinks massively using for example VBA routine or a macro or other feature of Excel that I don't know?
Lets say that I have the Test.xls that points with a hyperlink to Example.xls.
Then you save as the Test.xls to Test.xlsx and Example.xls to Example.xlsx (and delete the old ones).
If then you open the Test.xlsx and click on the hyperlink it will not open the Exmaple.xlsx.
You have to edit the hyperlink and add at the end the "x" letter in order to link to the Example.xlsx
My problem is that I have many Excel files that are linked eachother and "Find & Replace" option is not working for my case.
Does anybody know how to edit hyperlinks massively using for example VBA routine or a macro or other feature of Excel that I don't know?
mamelas, yes, of course this can be done, but it sounds like a new question?
ASKER
Ok thank you once again!
mamelas,
For example, the following will simplistically (e.g. ".xlsx" will become ".xlsxx") change the "non-formula" Hyperlinks in the active sheet...
Sub Change_HyperLink()
Dim xHyper As Hyperlink
For Each xHyper In ActiveSheet.Hyperlinks
xHyper.Address = Replace(xHyper.Address, ".xls", ".xlsx")
xHyper.TextToDisplay = Replace(xHyper.TextToDispl
Next
End Sub
You'd also need to check formulas (including Names). If the spreadsheets are not straightforward, a 100% solution could be very messy.
And then there's Word...!
There are converters that will do this (free, trial and pay), but I've not used any of them.
Suggestion: Close this (delete or allocate points to yourself) and ask a new question specifically about batch converters for .xls to .xlsx and .doc to .docx. That way, you'll get fresh input from people with specific experience in this area.
(BTW, I've mentioned .xlsm above. Are you aware that any .xls with macros will need to be saved as .xlsm to retain the macros? Similarly for Word and .docm.)
Regards,
Brian.
For example, the following will simplistically (e.g. ".xlsx" will become ".xlsxx") change the "non-formula" Hyperlinks in the active sheet...
Sub Change_HyperLink()
Dim xHyper As Hyperlink
For Each xHyper In ActiveSheet.Hyperlinks
xHyper.Address = Replace(xHyper.Address, ".xls", ".xlsx")
xHyper.TextToDisplay = Replace(xHyper.TextToDispl
Next
End Sub
You'd also need to check formulas (including Names). If the spreadsheets are not straightforward, a 100% solution could be very messy.
And then there's Word...!
There are converters that will do this (free, trial and pay), but I've not used any of them.
Suggestion: Close this (delete or allocate points to yourself) and ask a new question specifically about batch converters for .xls to .xlsx and .doc to .docx. That way, you'll get fresh input from people with specific experience in this area.
(BTW, I've mentioned .xlsm above. Are you aware that any .xls with macros will need to be saved as .xlsm to retain the macros? Similarly for Word and .docm.)
Regards,
Brian.
mamelas,
Sorry, my last post crossed with your close.
Thanks!
Regards,
Brian.
Sorry, my last post crossed with your close.
Thanks!
Regards,
Brian.
ASKER
Ok Brian, I am opening a new question now.
Once again Thanks!!!
Data loss may only occur in downgrading from from 2007 to earlier. You will not lose data in upgrading. (A small amount of functionality was dropped in 2007 (e.g. ability to open certain types of non-Office files), but these won't be relevant to most people.)
Regards,
Brian.