INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Quick Fail on Cannot Connect to Network Folder

Quick Fail on Cannot Connect to Network Folder

(OP)
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:

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

RE: Quick Fail on Cannot Connect to Network Folder

Try:

CODE

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.

RE: Quick Fail on Cannot Connect to Network Folder

(OP)
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

RE: Quick Fail on Cannot Connect to Network Folder

(OP)
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:

CODE

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

RE: Quick Fail on Cannot Connect to Network Folder

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close