Sometimes files are broken and beyond repair...can we prevent this?

[davidam]

I spent some time stressing this morning...feeling pretty confused about what I thought I knew about VBA. A file that I had moved here and there, changed, copied and used quite a bit, suddenly would not allow another file to copy data from it...it them threw a 'locked' error when VBA tried to just activate a sheet on the file. Fortunately, right at that moment, my daughter needed a ride so I took a break. As I was driving home it occurred to me the the file (and not me) was broken and it needed to be rebuilt. I did so and the problem was gone. It was a small file so it was easy to do; but if it had been a large one it would have been a different story. This begs the question: what can we do to prevent files from getting broke? A few things that I think are: saving files often and not running macros until the file has been saved. Any comments?

 

[VoG]

One word: Backup, Backup, Backup

 

[davidam]

With Peter's comment...and probably try to keep rotate backups so that we have copies that are a little older than the most recent version to prevent saving a file that we do not yet know is broken.

I am going to buy a few more usb sticks to day!!

 

[VoG]

On a more serious note I have only ever had this happen once. Fortunately at work where backups were made automatically every night. It was a file with months of experimental data painstakingly entered, charts, lookups, reverse lookups and a bunch of macros. It just refused to open at work but I could bring it home and it opened fine. I never really figured out the reason but at the time we had a rather dodgy implementation of Lotus Notes with all sorts of VBA running in the background. Once we ditched that, it reverted to opening with no problem.

At home I do a nightly backup of My Documents to an external HD using Norton Ghost. Better safe than sorry even though most of my home stuff is total dross.

 

[Norie]

Perhaps avoiding moving worksheets here, there and everywhere if possible might be a good idea.

Another thing you might want to check is what your code is actually doing.

Could it be leaving files open? Leaving ghost instances of Excel floating around in the wilderness?

 

[davidam]

In developing these files it seems almost inevitable that you are going to be moving them hither and thither etc...

 

[xenou]

suddenly would not allow another file to copy data from it...it them threw a 'locked' error when VBA tried to just activate a sheet on the file.

A "locked error" sounds to me (rather) like there was a vba error but the vb project was locked so you couldn't see what the actual error was. In which case, maybe it would be possible to unlock the project and find out what the error actually was (?).

In general I agree, however, with VoG's response and my experience is similar. I've only once actually encountered a corrupted file. When I did, I restored painlessly from a backup within a few minutes.

Nevertheless, a corrupt file and a vba error are two things (not the same).

 

[davidam]

Actually the vb project was not locked and I could see it. It would not activate a sheet because and it said it was locked...I am pretty sure it was a corrupted file. I made a new file and copied all of the macros word for word and had no more problems. I am surprised to hear that corrupted files are considered so rare because I have seen it, I think, relatively often in me short programming career.

 

[xenou]

I've never come across this error before. It's interesting - I'm not sure what it really means. Perhaps the sheet actually was locked for some reason other than corruption (?). If I knew what a locked sheet was or why such a sheet could not be activated ...

 

[davidam]

Even if a sheet is locked...you can still activate it, right?