Excel97 problem - how to copy sheets and change references
 

We have an old Excel97 solution with a .xls-File including two sheets "A"
and "B" where sheet A has a lot of references to sheet B, like "=B!K2". Due
to some circumstances we can't simply switch over to newer Excel versions.

Someone seems to have checked and maybe saved the xls file using a newer
Excel version or a Open Office version and now each time we want to save the
file we get a message box "This file was created using a later version..."

Because we found no way to suppress this annoying message box I tried to
copy the two sheets into a newly created Excel97 table and here comes the
problem:

If I open both files in the same Exvel97 window I can drag and drop both
sheets from one file into the other including formats and formulas, but the
formulas in sheet A of the NEW table still refer to sheet B in the OLD file
like ="D:\oldpath\[oldfile.xls]B'!K2

If I open two instances of Excel97 I can copy the sheets via clipboard but
without formulas.

So how can I copy the sheets while maintaining the RELATIVE reference to
sheet B only?

Or alternatively, how can I change all references to sheet B of the old
table into references to sheet B of the activ file for many cells in one
step? There are too many cells to edit the formulas manually cell by cell.

Of course, the simply way would be ti suppress the message box, then I
wouldn't have to copy anything, if possible.

Is there still an Excel97 professional out here who can help me?

Thanks, wuez.

Excel97 problem - how to copy sheets and change references
 

We have an old Excel97 solution with a .xls-File including two sheets "A" and
"B" where sheet A has a lot of references to sheet B, like "=B!K2". Due to
some circumstances we can't simply switch over to newer Excel versions.

Someone seems to have checked and maybe saved the xls file using a newer
Excel version or a Open Office version and now each time we want to save the
file we get a message box "This file was created using a later version..."

Because we found no way to suppress this annoying message box I tried to copy
the two sheets into a newly created Excel97 table and here comes the problem:

If I open both files in the same Exvel97 window I can drag and drop both
sheets from one file into the other including formats and formulas, but the
formulas in sheet A of the NEW table still refer to sheet B in the OLD file
like ="D:\oldpath\[oldfile.xls]B'!K2

If I open two instances of Excel97 I can copy the sheets via clipboard but
without formulas.

So how can I copy the sheets while maintaining the RELATIVE reference to
sheet B only?

Or alternatively, how can I change all references to sheet B of the old table
into references to sheet B of the activ file for many cells in one step?
There are too many cells to edit the formulas manually cell by cell.

Of course, the simply way would be ti suppress the message box, then I
wouldn't have to copy anything, if possible.

Is there still an Excel97 professional out here who can help me?

Thanks, wuez.

Did you already try deleting 'External References'?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Excel97 problem - how to copy sheets and change references
 

Did you already try deleting 'External References'?

I cannot find something concerning "External References" in Excel97 but it
sounds like that what I need.
How does it work?

The help file returns only some stanrd questions concerning linking to
external data but nothing helpful for me.

Thanks,
wuez

Excel97 problem - how to copy sheets and change references
 

Did you already try deleting 'External References'?

I cannot find something concerning "External References" in Excel97 but it
sounds like that what I need.
How does it work?

The help file returns only some stanrd questions concerning linking to
external data but nothing helpful for me.

Thanks,
wuez

Sorry.., the term I'm sure is *Break Link*!

Also, did you try group selectinng both sheets and 'Move' them to a new
workbook?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Excel97 problem - solution
 

Sorry.., the term I'm sure is *Break Link*!

Thank you. Because I'm German I had to translate this, and googling for a
propper term I found the solution:

If a table in the active Excel file contains an external link the menu item
"Edit - Links" is enabled and this function will allow to change references
to an Excel table in an external file to an Excel table with the same name
in another external or in the active Excel file.

So I could copy the sheets of the damaged .xls-file into a newly created
empty file very easy by Strg + dragging them into the new table, save it to
disk, and change the links in one step as described above, and the annoying
"file was created using a later version." has gone.

It's so simple if you know it...

Thanks,
wuez