Cannot open 2003 in 2007

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Cannot open 2003 in 2007

Post by agibsonsw »

Hello.

I have an Excel 2003 workbook which has links to other workbooks, many sheets and vba modules.
If I attempt to open it in 2007 I receieve 'File error: data may have been lost' and it doesn't open.
If I 'Open and Repair' it, it can open but without any formatting, links or modules. Help please:-)
Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cannot open 2003 in 2007

Post by HansV »

There are many complaints about similar problems. Apparently compatibility mode doesn't work as well as Microsoft claims.

Does the workbook use functions from add-ins? If so, you might try the following:
- Create a copy of the workbook.
- Open it in Excel 2003.
- Edit the formulas so that they don't use add-in functions.
- Save the workbook.
- Open it in Excel 2007.
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Cannot open 2003 in 2007

Post by agibsonsw »

Hi.
I think they use add-ins (Analysis Toolpak), so I'll check this. There is also an Add-In called Hyperion which does a lot of work.
Have you heard of this? It seems to draw data from another system.
Thanks for the continued assistance. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cannot open 2003 in 2007

Post by HansV »

The Analysis ToolPak shouldn't cause problems (in theory) because the functions from this add-in have been incorporated into Excel 2007.

The Hyperion add-in works with Oracle Essbase, an "Online Analytical Processing Server", whatever that may be :flee:
Do you have a version of Hyperion that is compatible with Excel 2007? And does it work correctly?
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Cannot open 2003 in 2007

Post by agibsonsw »

Hi. Trying to get it added in to 2007.
I tried removing the add-in references from the 2003 file, saving a copy,
and then opening it (without the references) in 2007, but no joy. Andy
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cannot open 2003 in 2007

Post by HansV »

Did you disable the add-ins in Excel itself, or did you remove the references in the Visual Basic Explorer?
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Cannot open 2003 in 2007

Post by agibsonsw »

Hi.
There weren't references within VB explorer.
The 2003 file also has a load of range names \A, \B, etc., which refer to server and file locations.
Might this be the cause of the problems? Thanks for your continued assistance. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cannot open 2003 in 2007

Post by HansV »

Disabling an add-in in Excel won't help - that is an application-level setting, not a workbook-level setting. In other words, when you save a workbook, you don't save any information about which add-ins are installed.
The 2003 file also has a load of range names \A, \B, etc., which refer to server and file locations.
That might well cause problems - if I try to create a name such as \A in Excel 2007, I get an error message that this name is not allowed. How much work would it be to change these names in Excel 2003? You'd have to make sure that all formulas and VBA code using these names are modified accordingly.
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Cannot open 2003 in 2007

Post by agibsonsw »

Hi. At the moment I'm just copying the sheets individually between 2003 and 2007..
Is there a quick way to count the number of chart (object) in the workbook? Ta, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cannot open 2003 in 2007

Post by HansV »

You could run this macro:

Code: Select all

Sub CountChartObjects()
  Dim wsh As Worksheet
  Dim lngCount As Long
  For Each wsh In ActiveWorkbook.Worksheets
    lngCount = lngCount + wsh.ChartObjects.Count
  Next wsh
  MsgBox "The active workbook contains " & lngCount & " chart object(s).", vbInformation
End Sub
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Cannot open 2003 in 2007

Post by agibsonsw »

Thanks again.
I started to copy the worksheets individually between 2003 and 7 but the charts wouldn't copy, even though the
appeared to be based on standard Excel Series.
In the end it was decided that it would take longer to rebuild than starting from scratch. They also have 2010 beta which
they've been able to open similar files in, so they'll probably keep the 2003 version running for the moment.
Thanks for your assistance, but beaten by Excel 2007 in the end! Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Cannot open 2003 in 2007

Post by agibsonsw »

To be drafted into something at the last minute, and to feel such pressure - that you will be able to solve a (serious) problem is very stressfull!
What can you say? I will do 'my best' to solve the problem, but I cannot guarantee a solution..?
I was confronted with a 2003 file that had 100+ worksheets, 50+ charts, numerous (badly formed) range names, links to many files (some of which were no longer alive/relevant), autoshapes, graphics and code - and it had been constructed by means of an add-in. And it failed to open in 2007.
However, when I said, after a number of hours, that it would not be possible to 'save/convert' the file - the guy said 'I didn't think it would be'..
(It would have taken longer to save parts of the file, and reconstruct the rest, than it would to build it anew). Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cannot open 2003 in 2007

Post by HansV »

I agree it's stressful!
Best wishes,
Hans

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Cannot open 2003 in 2007

Post by Jan Karel Pieterse »

The /a range names indicate this file has ancient history which goes back as far as one of the older Lotus 123 versions which used range names like that to refer to keyboard macros which use that key as a shortcut key to start that macro.
Models like these can be infuriatingly hard to troubleshoot.
I'd warn your "guy" his file is only that close to falling over and -worse- has the potential of returning wrong results due to the many incorrect links.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com