I don't have a guess about the second part, but the first part you could use:

Sub UpdateFiles2()
Dim FName As String
dim TestStr as string

FName = "P:\_InterviewAdmin\Spreadsheet\Test.xls"

teststr = ""
on error resume next
teststr = Dir(FName)
on error goto 0

if teststr = "" then
'not found
else
'found
end if

End sub

===
Just an idea. Maybe you do a .saveas and point at the windows temp folder
(local), then follow up with a .saveas to the network drive.

Maybe it'll wake up that server.

That's just a wild guess. I try it manually first before I did the code.




PaulC wrote:

I have a small ExcelVBA database system that opens 4 Excel files
(Excel97) on a server at the start of the day. These are updated and
saved by macros through the day and finally closed by a subroutine at
the end of the day.

Our server is proving very unreliable and quite often is not available.
If it cuts out and you then try to run one of the macros that save the
files to the server you get an error message saying the file cannot be
saved. Even when the server returns it will not normally allow the
files to be saved. You then have to close all files, losing data that
was not saved.

I have tried to incorporate an error check before a macro that will
need to save one of the files (to the P:\ drive) is run, so that the
macro is stopped and only allowed to be run when the server is again
available. The code I have used is below. (The first check is just to
force users to log in correctly. Folder '\_InterviewAdmin' is only
available to the correct log in. Test.xls is a small test file saved
in the same folder as the data files.)

Sub UpdateFiles()

Dim FName As String
Dim Ans As String

On Error GoTo LabelCK

FName = "P:\_InterviewAdmin\Spreadsheet\Test.xls"

If Dir(FName) = "" Then

Ans = MsgBox("Check that you are logged on as
'interviewadmin'.", vbOKOnly, "Incorrect Login ")
Exit Sub

End If

....rest of macro code including saving workbook commands

Exit Sub

LabelCK:
Ans = MsgBox("The Public Folder is not available. You will have to
wait until it is available again. DO NOT TRY TO CLOSE CLIENT LIST
FILES.", vbOKOnly, "Public Folder Unavailable")
Exit Sub

End Sub

When tested, if the P:\ drive is removed, it jumps from the 'If
Dir(FName)="" Then' line, correctly, to the error LabelCK line, without
executing the macro. However, when the P:\ drive is then restored it
will still not allow the files which are still open to be saved. It
displays an Error 1004 message.

Is there a way round this so that the macros can resume after the
server is available again, without having to close all the files?

Paul

--
PaulC
------------------------------------------------------------------------
PaulC's Profile: http://www.excelforum.com/member.php...fo&userid=7563
View this thread: http://www.excelforum.com/showthread...hreadid=276187

--

Dave Peterson