Quick Fail on Cannot Connect to Network Folder 1

[kjv1611]

I'm using this in Excel (Excel 2010 currently).
Trying to use Excel to copy a file from one folder at one network location to multiple other network locations.
Ever so often, I'll come across network paths (target paths) that are unreachable because: The computer is turned off or perhaps there's some technical issue at that machine.

What I want to do is to build a script to run through a list of folder addresses, copying the same file to the folders. However, I've noticed that when just testing opening a link to a folder that doesn't currently exist (probably, the computer is turned off, or else it could be an old computer taken off the system, but the list not yet updated). When I click on a link for it or else if I run the Dir() function to test whether the file is there, Excel (running this currently in Excel in my testing) will sit and spin for way too long of a time before failing. I'd rather it fail within a second or two if possible, and then move on.

My question is: is there an easy way to force the time to assume failure to be much shorter than it apparently is?

So far I've tested 2 things:
1. Actually clicking a link to a folder I know is not currently available to the network (this is a link to a folder, not file)
2. Using the Dir() function in a short procedure to see if it'd fail more quickly when looking at the same linked folder. (This gives error 52: Bad file name or number)

Test VBA code:

Sub TestDirectory()
    Debug.Print Dir("\\MyNetworkFolderPath", vbDirectory)
End Sub

Thanks for any thoughts.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

Try:

Dim FS As Object
Dim DirExists As Boolean

Set FS = CreateObject("Scripting.FileSystemObject")
DirExists = FS.FolderExists("\\MyNetworkFolderPath")
Set FS = Nothing

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[kjv1611]

Thanks, will do. I was already headed in the direction of using the FileSystemObject anyway, but was trying to avoid it if I could.

Planning on using fso.CopyFile to copy the actual file so I can specify to overwrite, since this is something that can come up multiple times in a week or once ever 6 months or so, no rhyme or reason, I'm told.

I'll post back with my results. Thanks

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Still hangs, though it seems to hang not quite as badly. I am sure it takes so long because it's a network path. Surely there's some way to tell it to give up after a couple of seconds, since access to the locations is pretty close to instantaneous when the work, most the time.

I tried adding DoEvents just to see if it would at least keep Excel from spinning, but that made no difference.

This is definitely an improvement though. Here's my full current code (minus the DoEvents) for the test:

Sub TestDirectory()
'    Debug.Print Dir("\\MyNetworkFolderPath", vbDirectory)
    
    Dim fso As Scripting.FileSystemObject
    Dim bolDirExists As Boolean
    
    Set fso = New Scripting.FileSystemObject
    
    DoEvents
    bolDirExists = fso.FolderExists("\\MyNetworkFolderPath")
    DoEvents
    If bolDirExists = True Then
        Debug.Print "Exists!"
    Else
        Debug.Print "does not exist!"
    End If
End Sub

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

As an update, embedded within the code to copy the actual file, it isn't instantaneous, but it definitely runs better than I expected when I tested with the Dir() function. Definitely a good approach.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57