[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

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)
 Excel 2007 warning messageUpgrading all computers to Office 2007 and existing files (using save as) to .xlsx and .docx will cause any data loss or not?
0
mamelas
Asked:
mamelas
  • 11
  • 6
3 Solutions
 
redmondbCommented:
mamelas,

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.
0
 
mamelasAuthor Commented:
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?
0
 
redmondbCommented:
Mamelas,

If you want more details, please see the "What's removed" sections in the following...

Excel - http://technet.microsoft.com/en-us/library/cc179167(office.12).aspx

Word - http://technet.microsoft.com/en-us/library/cc179199(office.12).aspx

Regards,
Brian.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
redmondbCommented:
Mamelas,

Sorry, crossing posts.

I'm afraid you can't be given a 100% assurance that an earlier macro will run perfectly in Excel 2007. The vast majority will, but not necessarily all.

Do you have many macros?

Regards,
Brian.
0
 
mamelasAuthor Commented:
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)?
0
 
redmondbCommented:
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.
0
 
redmondbCommented:
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.
0
 
redmondbCommented:
Oh, and your link formulas will need to be changed to replace ".xls" by ".xlsx" (or ".xlsm")
0
 
mamelasAuthor Commented:
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?
0
 
redmondbCommented:
mamelas,

No, the hyperlink will fail as it's still looking for the .xls. You would need to change all hyperlinks (and formulas with VLookUp, Match etc.).

Regards,
Brian.
0
 
redmondbCommented:
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.
0
 
mamelasAuthor Commented:
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?
0
 
redmondbCommented:
mamelas, yes, of course this can be done, but it sounds like a new question?
0
 
mamelasAuthor Commented:
Ok thank you once again!
0
 
redmondbCommented:
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.TextToDisplay, ".xls", ".xlsx")
    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.
0
 
redmondbCommented:
mamelas,

Sorry, my last post crossed with your close.

Thanks!

Regards,
Brian.
0
 
mamelasAuthor Commented:

Ok Brian, I am opening a new question now.
Once again Thanks!!!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now