I'm trying to replace an old Windows XP office computer with Office 2007 with a new Win 7 computer with Office 2013 but I've run into a major problem.

This computer is used to make reports for the company via Excel, such as the top 100 selling items, weekly performance, labor to money, etc... boring stuff. But there are hundreds of reports, covering all of the stores plus corporate-wide going back to 2008. This wouldn't be a problem to just move the files over but every report has links to at least eight other reports and the files are all in Win XP format (C:\Documents and Settings\[USERNAME]\...) instead of Win 7 (C:\Users\[USERNAME]\...) I'll give an example.

We have a Store Score Card report (which basically shows sales, labor, new customers and other info) for each store and one for corporate. The corporate one pulls numbers from every store's individual report and adds them together for each column/category. So each cell has a long equation for it, like so (basically)... Cell C,32 is: ='C:\Documents and Settings\Bob\Reports\Store1\2014\Store Score Card'+'C:\Documents and Settings\Bob\Reports\Store2\2014\Store Score Card'+'C:\Documents and Settings...' ...and on and on including all the stores. And then the next column will be a different category, and pulls numbers from different reports for each store using the same method. Super complicated.

The real problem is that I updated the links of two reports to the Win 7 format, and that took hours just for those two... But there are hundreds of reports, some of them very large (only updating one link took over 20 minutes on a really big report because it has to update every cell over pages and pages and pages.) It's unrealistic undoable to change every single link by hand.

Does anyone know of a way to migrate or automatically update the workbook links from WinXP file format to Win7 format?

tl;dr... How to change links in Excel from XP to 7 format? This is probably confusing so if you need any clarification just ask... I'm really hoping someone has an answer to this ridiculous problem.

You're not going to like reading this post.

I can tell you're updating the links without having tested them, because if you had, you would have realized you can't access other Users folders. Windows 7 was designed this way for a reason.

In short: do not use the Users folder.

If you can, copy the entire directory structure from WinXP to Win 7 under the root C:. Windows 7 doesn't use a Documents and Settings folder, so a direct copy should be easy.

More bad news: since you're jumping significant versions of Excel, if you didn't use the Compatibility Wizards to upgrade the 2007 version files, there's a good chance the current setup isn't going to work. 2007 still uses the old "xls" format while 2013 uses the "xlsx", where "x" stands for "XML" as Office 2010+ started using XML as its foundation.

I would try the copy/paste first, and give it a test run. If it doesn't work, I highly recommend you keep the WinXP machine running (but don't allow it connected to the internet due to the lack of security updates) and rebuild the entire reporting system using a database.

Excel should be used as a spreadsheet, not a database, and what you've described is a classic example of why a database is needed.

It's not going to be easy, but I can say if you update this process now, it'll save you headaches when you have to go to Windows 8/9 down the road.

Good luck, because it sounds to me like you're going to need it.

Thanks for the reply.

It's not accessing other users' folders, just the one user. So Bob (the guy doing the reports) is logged in as Bob, and all the reports are in a folder in Bob's desktop (C:\Users\Bob\Desktop). Is that what you're saying?

I actually tried making a folder under C: called Documents and Settings on the Win7 machine, and it said there was already a folder called Documents and Settings. I tried it on a Win8.1 computer too and same thing... but there actually isn't a folder called that... I made sure it wasn't hidden either. Really weird. Maybe MS made it so you couldn't make a folder called that under C:?

I understand what you're saying. I know the guy who made the reports years ago, and he's kind of a dumb ass. Unfortunately so am I when it comes to databases/Excel/Access. I'll have to do some research on making an appropriate database.

C:\Documents and Settings\ in Windows Vista and beyond actually maps to C:\Users\ This is done for backwards compatibility.

So the only thing you need to do is create the user account.... Which you don't even really need to do, instead in C:\Users just create a symbolic link from Bob to and it'll work

I just hope you aren't using Excel to store data. Leave excel as a toy to keep the accountants happy and distracted and use SQL for real work. This is a big project but one worth doing.

2007 actually does use OOXML by default and there's no major changes from 2007 to 2013 that I can think of that would break compatibility. Microsoft generally doesn't drop backwards compatibility.

Indeed. Though it is possible the excel reports are, at the root, retrieving their data from a database, just the person who created the reports didn't know what the heck they were doing and instead read data from other reports.

Of course excel wouldn't be my first pick for a report generating frontend, unfortunately it is a common one. Accountants are so addicted to it


*this post wasn't meant to offend accountants, it's just that excel is a financial tool first and foremost, and unfortunately gets heavily abused