Error number of saving in a server

[cesarsoto]

Hello everyone, I did a Macro for Excel 2000 that execute a Save as action to a server, sometimes the server is down or had some problems ,when a run the Save As macro the macro breaks, I 've tried with "On error" function using the Error Number = 1004 to ask to the user's another path, But I'd been watching that doesn't work .

Example:

Public Function case()
Dim msg, Style, Title, Help, Ctxt, Response, MyString, Default, MyValue

On Error GoTo ErrorHandler

ActiveWorkbook.SaveAs Filename:="\\server\folder\file.PRN", FileFormat:= _xlTextPrinter, CreateBackup:=False

ErrorHandler:
Select Case Err.Number
Case 1004
msg = "Do you want to save in another path?"
Style = vbYesNo + vbDefaultButton2
Title = "Information"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(msg, Style, Title, Help, Ctxt)
If Response = vbYes Then

msg = "Enter your path"
Title = "Path"
Default = "c:"
MyValue = InputBox(msg, Title,Default)
ActiveWorkbook.SaveAs Filename:=MyValue & "\file.PRN", FileFormat:= _xlTextPrinter, CreateBackup:=False

Does anybody knows the Error Number or the correct action for this Problem??

Thanks

Cesar Soto

 

[dougcranston]

cesarsoto,

Why not test for the existinance of the folder or a file in that directory before attempting the save as? If it exists, you should be able to connect to it, assuming you have permissions.

One way you could do this, is by using WMI. See following links for help.

Testing IP Connectivity With WMI

http://serverwatch.com/tutorials/article.phpr/1571771

Also, checkout SCRIPTOMATIC for samples:

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/scriptcenter/wmimatic.asp

TechNet Home > Script Center > Files and Folders > Verify that a File Exists (Check the Left Menu)

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/scriptcenter/wmimatic.asp

Hope this helps.
DougCranston