File SaveAs causing run-time error

**c4tin** · 06-03-2009, 10:02 AM

I have deployed a program in Excel 2003 that is used to gather and log user input. It has one hangup. After a workbook is submitted it is saved to a network folder under a new system-assigned name. Usually this works fine. However, certain users (using the same version and SP of Excel and the same OS) sporadically receive the following error when the code reaches ActiveWorkbook.SaveAs (NetworkPath):

Run-time error '1004':

Microsoft Office Excel cannot access the file '\\(mynetwork path here)\'. There are several possible reasons:

The file name or path does not extist.
The file is being used by another program.
The workbook you are trying to save has the same as a

None of these conditions are true. I tried using DoEvents to give the system extra time to create the file but that didn't help. And I can't think of a workaround to use with error trapping.

Microsoft did publish an article about this error occuring in this instance (http://support.microsoft.com/kb/291204) but I'm weary to take those steps because I can't be sure it will solve the problem and it involves some registry editing.

Has anyone else run into this? I can't believe it's uncommon but the lack of info on it leads me to believe either it is or there's an easy fix eluding me.

Thanks in advance.

**shg** · 06-03-2009, 10:14 AM

Good job finding the KB article, but it's not clear to me what causes the problem. If the network connection is lost, you can't save the file to the network path at all; so I guess the error occurs when the connection was lost but subsequently restored.

I wonder what would happen if you trapped the error, saved the file locally, and then reattempted the network save.

**c4tin** · 06-03-2009, 10:58 AM

Shg,

I had the same thoughts on the KB article, but I don't have much networking expertise so I just hoped they know what they're talking about.

I like your error trapping suggestion but I'm curious why I'd need to save the file locally? Would that be to test whether the problem is network related?

**Spudley_Jones** · 06-03-2009, 12:44 PM

I have ran into problems when using save-as on a network drive. I found my problem was the security and mapping of the drive. I had to map the network drive to give it a name designation (i.e. S:\ = \\networkserver\) This gave Excel destination to put the file being saved. Hope this helps.

**shg** · 06-03-2009, 01:35 PM

I'm curious why I'd need to save the file locally?

Just to sever the workbook's relationship to its network path. I have no idea if it would work.

After if fails and you save locally, you could do a Dir() on the original path to verify that it is indeed reconnected; if not, there's no point in trying to save it out there again.

**c4tin** · 07-14-2009, 12:34 PM

After exhausting all workarounds and thoroughly confusing my network admin, I was able to solve the problem by using ChDir() before the FileSaveAs(). There's no reason I can think of why this is necessary, because I still need to save the file with a new filename using the full network path, but so far it has worked flawlessly so long as the network path being saved to is the active directory.

Thanks for all your suggestions.