Excel fails on Save/Save As

[DanG]

I have an Excel application that I took over writing from another
programmer who left the company. It's a full app -- not just a bunch
of spreadsheets.

Excel will frequently fail on a Save or Save As. So I go back to the
previously-saved version of the file, and now it fails, too.

For example, I start with version "A".

1. Open "A", and immediately Save As "B". That works fine.

2. Close Excel. Open "B", and immediately Save As "C". That works
fine.

3. Close Excel. Open "C", change the code or a form, then Save As
"D". That works fine.

4. Close Excel. Open "D", change the code or a form, then Save As
"E". Fails! So now I can't save the changes I made in "D".

5. Close Excel. Re-open "C", and immediately Save As "F". This
worked before, but now fails! Now I've also lost the changes I made
in "C".

6. Close Excel. Re-open"B", and immediately Save As "G". This worked
before, but now fails!

7. Close Excel. Re-open"A", and immediately Save As "H". This worked
before, but now fails!

Over the past few days, I have written the same code again and again.
I don't find out until after many hours of work, and many saves, that
all of it is lost. I've tried three different machines, and they all
fail one way or another. Rebooting doesn't change anything.

I've seen some posts about converting to HTML and back, but that would
lose all forms, wouldn't it?

Suggestions welcome.

Thanks
Dan

 

[JoeSpareBedroom]

How do you know it's failing? Is there an error message of some sort?

 

[DanG]

On that machine, it gets the "Send error to Microsoft" popup. Not
much help other than a "Save Failed." message. Excel closes after
clicking Okay on the popup.

On the other machines, it behaves as though the save worked, and the
new file exists. But if I close Excel and attempt to open the new
file, it's corrupted. I had done a lot of work thinking it was
saving, until I couldn't open ANY of my saved files. I had to go back
to the original file from six hours earlier.

Dan

 

[JoeSpareBedroom]

On that machine, it gets the "Send error to Microsoft" popup. Not
much help other than a "Save Failed." message. Excel closes after
clicking Okay on the popup.

On the other machines, it behaves as though the save worked, and the
new file exists. But if I close Excel and attempt to open the new
file, it's corrupted. I had done a lot of work thinking it was
saving, until I couldn't open ANY of my saved files. I had to go back
to the original file from six hours earlier.

Dan

If this was happening to one of my files, I'd copy absolutely everything to
a totally new file and test it. Sounds like there could be something
seriously wrong with the one you're working with.

 

[DanG]

Is there an easy way of doing that? I poked around in Excel to see if
there was an Import function for worksheets or code, but didn't see
any. All I saw was an Export on the worksheet tab for the data. My
guess would be that the problem is buried in the forms/data, and not
in the VBA code. Would there be a way to copy over the code en masse?

I was picked to work on this app because I've done a little VBA coding
in Access before (which is more experience than anyone else here).
Never done any actual Excel programming before. From what I've seen,
I hate it. I probably could have written the entire app in .net in
the time I wasted here.

Thanks

 

[JoeSpareBedroom]

Is there an easy way of doing that? I poked around in Excel to see if
there was an Import function for worksheets or code, but didn't see
any. All I saw was an Export on the worksheet tab for the data. My
guess would be that the problem is buried in the forms/data, and not
in the VBA code. Would there be a way to copy over the code en masse?

I was picked to work on this app because I've done a little VBA coding
in Access before (which is more experience than anyone else here).
Never done any actual Excel programming before. From what I've seen,
I hate it. I probably could have written the entire app in .net in
the time I wasted here.

Thanks

If there's a way, it's unknown to me. I would just deal with it being a real
pain in the neck, followed by a cocktail or a cake overdose.

 

[DanG]

Okay. Thanks Joe.

The good thing about banging your head against a wall is how great it
feels when you stop.

 

[JoeSpareBedroom]

Okay. Thanks Joe.

The good thing about banging your head against a wall is how great it
feels when you stop.

Actually, it might be a good idea to copy over one suspect "thing" at a
time, rather than multiple suspect "things". That could help isolate the
problem. I'm not even close to being a VBA expert, but this is how I debug
Paradox database code. Old Indian trick learned from some excellent generic
programming books, like "Code Complete" (Microsoft Press).

 

[DanG]

My first pass will just be to copy it all in. If it works after that,
fine. If not, I'll have to find which
#*)$@&^! form/sheet is causing the problem. I'll use the old Indian
trick for that.

I didn't realize the old Indians did that much programming.

 

[JoeSpareBedroom]

My first pass will just be to copy it all in. If it works after that,
fine. If not, I'll have to find which
#*)$@&^! form/sheet is causing the problem. I'll use the old Indian
trick for that.

I didn't realize the old Indians did that much programming.

Please post your findings. This sounds interesting.

 

[DanG]

Not sure why you'd find this so interesting, but here's the
I created a new Excel file, and opened one of the previous files.
Right-clicking on a worksheet tab allowed a "Move or Copy", and pops
up a small dialog box to indicate the name of the receiving file,
where in the list of existing tabs you want this sheet placed, and
whether you want a Move (which deletes from the curent file) or a Copy
(doesn't deleted).

I first attempted to move each tab, one at a time. After they were
all moved, I tried a Save As, which failed (same as before). I
figured one of the tabs has something corrupted in it. So I started
again, moving each tab, and doing a Save As after each.
Interestingly, I got through the whole list (there's about 20 tabs),
without a failure. This took a bit longer because many tabs surpassed
a 255 character limit, and were truncated. (Don't know why this
didn't happen in the first go-around.) So I had to edit each tab with
truncated data, delete the contents, select and copy the originating
tab's data, and paste into the new tab. Getting to that point took
about 2 hours. That's as far as I got yesterday.

Later today, I'll see if I can copy/paste the code for each form and
module to get a working app again -- and try another Save As.

 

[JoeSpareBedroom]

Not sure why you'd find this so interesting, but here's the news:

I'm strange that way.

I created a new Excel file, and opened one of the previous files.
Right-clicking on a worksheet tab allowed a "Move or Copy", and pops
up a small dialog box to indicate the name of the receiving file,
where in the list of existing tabs you want this sheet placed, and
whether you want a Move (which deletes from the curent file) or a Copy
(doesn't deleted).

I first attempted to move each tab, one at a time. After they were
all moved, I tried a Save As, which failed (same as before). I
figured one of the tabs has something corrupted in it. So I started
again, moving each tab, and doing a Save As after each.
Interestingly, I got through the whole list (there's about 20 tabs),
without a failure. This took a bit longer because many tabs surpassed
a 255 character limit, and were truncated. (Don't know why this
didn't happen in the first go-around.) So I had to edit each tab with
truncated data, delete the contents, select and copy the originating
tab's data, and paste into the new tab. Getting to that point took
about 2 hours. That's as far as I got yesterday.

Later today, I'll see if I can copy/paste the code for each form and
module to get a working app again -- and try another Save As.

Everybody sing the Backup Song.....

I'm just sayin'...backups. Off of that computer.