Saving Problem Excel 2000 with VBA code

[viaene]

Hello,

Hopefully someone can help me with a strange problem I've encountered
with Excel:

I have an excel file with some VBA code. Now everytime I save the
file, there is an extra file created in the same directory/folder with
a filename like 'F43D6910'. If I close the file and reopen it, all my
changes are saved. I'm working on Windows XP but some of my collegues
still have NT. If they try to save the file, Dr Watson appears
(opening the file is no problem...).
If I double clic one of the created files, excel opens and I noticed
that it was a copy of my original xls file.
The main problem is that every additional file has the same size as
the original excel file, and when I tried deleting these files, I
could no longer open the original file.

Has anybody an idea what causes this and how to solve this problem.

Many Thanks

 

[Guest]

The files with names like "A1B2C3D4" are automatically generated by
Excel whenever VBA code is attempting to save the spreadsheet and cannot do
so. But, since you are in code, Excel needs to so something and this is its
answer. Is the original workbook marked as read only through windows? Was
it marked as read only during some previous "save as" operation? Are there
multiple users hitting the workbook at the same time (either as a "shared"
workbook or not)?
I have personally experienced this problem with a multi-user workbook
configuration. My particular issue dealt with VBA code manipulating four
separate workbooks as a "system" and some of my users was opening one of
those workbooks "manually" and keeping it open. Therefore, my code, when
attempting to save data from one of the other users, could not do so and I
ended up with a file like A1B2C3D4. I solved the problem by placing a
password on that particular workbook and modifying my VBA code to open the
workbook with that password.

Steve in Ohio

 

[Dave Peterson]

When excel saves the file, it saves it as a temporary file with a funny name (8
characters--no extension).

If the save is successful, xl will delete the original (or rename it to its
backup name (like "backup of book1.xlk)) and if that's successful, xl will
rename the funny named file to the original's name.

Common things that get blamed for interruptions to this process are antivirus
software poking its head in or network errors--either permissions or physical
problems.

I think I'd talk to the IT folks and ask them to double check permissions and
maybe temporarily disable the AV software (do one at a time, so you can isolate
the problem.)

======
And xl does the same stuff whether the file is being saved via a macro or
manually.

 

[viaene]

Thanks for the info, but I don't think that's my problem. It's not
even the case that I save the excel file from within the VBA code. I
have this problem when I just hit the save button in the excel
toolbar, after I closed the visual basic editor, or even when I never
opened it and just save the file without making any changes. Also I
don't think there's a permissions problem because the file is on a
network folder and I have no problem, reading, writing or deleting in
the folder.
I really have to get this issue solved, HELP!!

Many Thanks

 

[Dave Peterson]

How about the Antivirus program? Did you try disabling it to test?

 

[viaene]

Thanks for looking at this problem. It didn't have something to do
with the antivirus either, but I managed to solve my problem. In the
VBA code I created a long string with severel line continuations.
Although the compiler didn't give any problems, and the code worked
OK, this was creating the problem. After 10 line continuations I used
a string concatination. Now the file is saving just fine and I could
delete the 'strange' files, although I still have no idea why this
happens.

 

[Dave Peterson]

I'm glad you solved your problem. But I'd keep an eye open for it to recur.

(I've got a funny feeling your fix was a coincidence--but I've been wrong
before--lots of times!)