Excel VBA .Save Causing a Save As to 'Random' number filename

[jcoeng]

Hello,

I have several VBA macros that have been working great for years. Recently I've been seeing problems when they call the .Save method. It seems to save the workbook but then also have some kind of catastrophic error where it tries to save the file as a random (only say random because I see no significance to my data) number ie 14F11400 (always ending in 00). It takes several attempts to try and cancel out of the operation and when I finally cancel I find the file in my directory without an extension.

I just 'fixed' this issue on a set of files last night where I opened another workbook using vba, then chose to wb.Save and wb.Close on the 'other' workbook. It would have this same error. I 'fixed' it by moving the wb.Save into the 'other' workbook and calling the Sub from my main workbook. All seems to be working great now. Then this morning this error comes up again on a stand-alone workbook, no other workbooks opened or referenced in the vba.

I'm scratching my head over here. Has anyone seen this before and have a clue on where to look to solve it? It doesn't happen every time and it does happen on multiple computers as it is a stored in a shared location.

Thank you,

 

[bobsan42]

From what I've seen, but have not invested too much time into it, this type of files are temporary files. Excel uses them as an intermediate file while saving a workbook. It is not related to VBA in any way. They appear even if you save a workbook with no code in it. Normally you don't see them because they normally only exist for a part of a second. Once the saving is complete and everything is ok your original file (or its contents) is replaced with the temporary file and then it is gone.
Usually these files remain behind if the SAVE did not go well or the Saving process did not complete properly. This may be a workbook corruption or who knows what.
Recently I am having some troubles with corruption of heavily used macro-workbooks. So more and more I prefer to reduce the changes in a workbook with code in it. For this reason I am trying to move as much as possible data out, to keep code and data separate.

These temp files can also be observed when you are saving a large/heavy workbook which takes a considerable amount of time to save.

 

[jcoeng]

Thank you for your reply. I've come to the same conclusion that these are the temp files. What I'm still not able to figure out is why the save process is getting hung up. I've read this may happen if the temp file grows to over 2gb but my main files are nowhere near that. I've also read that this "bug" is specific to the .save method and some have gotten around it using .SaveAs and suppressing prompts. I'm planning to switch over to this in hopes it will help resolve the issue.