Excel Formatting and saving problems

[MeGustaXL]

Hi there,

I have a failry large (3Mb+) workbook created in XL 2K3. It has been extensively worked on in XL 2K7 (Compatibility Mode), but saved as an "Excel 07-2003 Workbook".

Now when it's opened in 2K7, all of the formatting is gone, even in Pivot Table sheets, all the formulas are missing - data just saved as values, and all the links are broken.

I can get the formulas and formats from an earlier version and paste them in, but as soon as I save it - plain text!

This represents several man-weeks of work, and I cannot spare the time to start from scratch. Apart from anything else, the Customer doesn't have Office 2007, so that route is not viable.

Please help me before I get fired!


Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[GlennUK]

Have you tried opening it in Excel 2003?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[MeGustaXL]

No, because I've only got The Spawn Of Satan (2007)

The Customer has 2003.

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[GlennUK]

More questions: You say:

Now when it's opened in 2K7, all of the formatting is gone, even in Pivot Table sheets, all the formulas are missing -...

Are all of the problems related to PivotTables? If I remember rightly from the last time that I used Excel 2007, there were massive problems with PivotTable compatibility.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[MeGustaXL]

Hi Glenn,

No, not all. The Pivot Table work, they've just lost their format, so they havent got borders, bold bits, etc.

The other sheets are similarly affected; no borders, text formatting gone, conditional format rules still there, but not working.

Other sheets' formulas have all been replaced by plain values.

 

[MeGustaXL]

Can anyone offer me a glimmer of hope with this problem?

The workbook is saved as Excel 97-2003, but whenevr I open it in 2007, dates show as serial numbers, borders are gone, word wrap is turned off, etc, etc. I can set these things how I like them and re-save as Excel 97-2003, but as soon as I open it - gone!

My Boss thinks it's my fault, and I almost believe that myself, because I can't see any logical reason for it.

Please, please help me

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[GlennUK]

Just a quick question ... you aren't transferring these files via Outlook before opening are you ( you know, mail to another id, and launch it in Excel from there )?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[MeGustaXL]

Hi Glenn,

Kind of. the file is saved in a shared area using Livelink, then edited from there.

[excited look on face]Do you think it could be that?

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[xlhelp]

Have you tried opening the file from Windows explorer rather than from within Excel? Silly as it may seem, it might be different.

A man has only two choices: He can be right or he can be happy.

 

[MeGustaXL]

Hi Geoff,

That's actually what happens. The file is stored in the shared area and owned by another department.
To work on it, I "Check it Out", rather like Sharepoint Portal, which allows me to edit it in an IE browser window.

The other way is to download it, work on it offline, then save it as a new issue/variant, but the owner doesn't want multiple versions flying round, like Chris_1.6, Charlie_2.8 and Master_12.8, because that's exactly the reason we created the single Master document.

Do you guys think it could be the online storage that's causing the problem?
Unfortunately all our PC's are on XL2007, so I can't even try opening/working/saving/opening in 2K3

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[GlennUK]

Hi Glenn,Kind of. the file is saved in a shared area using Livelink, then edited from there.[excited look on face]Do you think it could be that? Chris

.. I don't know, but I found a thread somewhere, and the guy was having the same trouble as you, and this is what he did to fix it:

... here's the solution that worked for me:

Execute services.Msc

Look for a service called : WebClient

Check wether the service is running, if not initialise it, and make sure its default settings are set to Automatic so it starts whenever you start your PC, Click Ok .

Now try to open & save Office documents, and see if it works, it did for me.

Like most services on WinXP the description for this service is so generic that it almost makes no sense:

Enables Windows-based programs to create, access, and modify Internet-based files. If this service is stopped, these functions will not be available. If this service is disabled, any services that explicitly depend on it will fail to start.

I can't vouch for this, but I thought you'd like to hear it.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[MeGustaXL]

Thanks Glenn, but it didn't help.

I checked WebClient, and it's running in Automatic, but the problem's still the same: changes to formatting are not saved when returning the document to the central area from LiveLink.

To add to this, the workbook has a spreadsheet containing plain vanilla text, except for a column of dates. The dates cannot be formatted as anything except date serial numbers. Copying rows and cells within this sheet is fine, but any attempt to alter the contents gets an error box saying "Too many formats"

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[GlennUK]

Too many formats? I've heard of this somewhere before. I think the solution was to save as xml and then reopen and save as Excel again ( assuming that you have no VBA in the workbook ). Again, I can't verify that this will work.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

error box saying "Too many formats"

I've been following this thread with interest for the past 2 weeks.

That message indicates to me that you do not have contiguous range formatting in your sheet -- rather cell formatting that does not follow a full row/column. If I am correct, Excel has some smarts that determines when a range has a format and stores the formatting information as such, so that you could have a complete column, 65,536 rows, formatted IDENTICALLY and that "information" is much smaller than 65,536 cells with DIFFERENT format information.

I have a sheet that demonstrates this behavior (ie the error message) I have not tried to open it in 2007. I may try this evening when I get home from work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Hi Skip, I did wonder if you'd peeked at this

...that "information" is much smaller than 65,536 cells with DIFFERENT format information

That's very interesting. Since my OP, the filesize has ballooned from about 3 Meg to 7.31Mb, with only a few changes to the content; a cell here, a row there, etc. No more than 100 cells total.

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[MeGustaXL]

Just an update.

The original problem still exists - open in browser window, edit, save, close browser window, reopen - wrecked.

However, the workaround we are using is to Zip the file, post it in Sharepoint, then check out the zip file, work on it, re-Zip it, and check back in. All's well doing it this way.

Microsoft, eh? Bless its cotton socks. Grrrr....

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson