Test Network Location (Computer Name) to determine if reachable / valid

[kjv1611]

I'd like to determine using a list in Excel whether each of the computers listed are valid and then highlight accordingly. What I'm interested in here is the part of trying to validate the path/link.

Examples:

\\pc1
\\pc2
\\pc3
\\pc4

That's basically how things are setup with computers on our network. I can hyperlink to them, and the ones that are connected show up in Windows File Explorer no problem. The others give a Windows message of "Cannot open the specified file".

It would be really nice to be able to check all these in advance to highlight those that should NOT work.

I tried the Dir() function, specifying vbDirectory, and that seems to not work. I also tried using FileScriptingObject FileSystemObject, and that was a no go with setting a folder object equal to the computer path.

Is there some way I can get this done via VBA? Am I spinning my wheels for nothing?

Thanks for any suggestions.



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

 

[SkipVought]

Hi,

I'd use File System Objects.

Also check out GetOpenFileName to validate any path.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

My oh my - When I said "FileScriptingObject", I meant "FileSystemObject". My mind played games with my fingers since I'm used to typing Scripting.FileSystemObject.

GetOpenFileName also does not work. Thanks for the suggestion, though.

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

 

[kjv1611]

One other option I've tried to play with is checking the hyperlink, as I've added hyperlinks to the computer/folder locations. That has also not panned out. I can make it follow a link, but if I went that route, I'd rather:
1. Have VBA see if the link can be opened without actually opening.
2. If cannot do #1, then open the link and immediately close the opened window.

But since I ran into issues with the hyperlinks handling methods, I thought I'd try using the Dir() function, and then FileSystemObject, but so far no go on any of them.

Windows will often tell you at startup if a network drive is unavailable. It has to be actively checking to tell you that. If the OS can do it, is there not some way I can trigger the same?

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

 

[kjv1611]

What I've figured out so far is that I can use the Dir() function against an actual directory underneath the computer, so:
dir("\\pc1[highlight #FCE94F]\Folder123[/highlight]",vbDirectory) works

However, if I try the same with just the computer name path (even though I can browse it), I get an error "bad filename":
dir("\\pc1",vbDirectory) does not work but ends in an error: Bad file name

I can get it the next to run without error, but it's not correct:
dir("\\pc1",vbVolume))

So, still digging. Even if I have to run a shell command to get it to work, I'm fine with that. There's got to be a right way to do this.

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

 

[kjv1611]

One example I've toyed with the idea of testing hyperlinks is here:

http://www.utteraccess.com/forum/validate-hyperlink-VBA-t1037294.html

I think the issue is that method wants to work with web / external links, and not internal LAN links.

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

 

[kjv1611]

One more update. I thought I found a way to do this with all but the computer name only, but that's not the case. If the path is the computer name and the network share name for the folder, Dir() konk's out with "Bad file name or number" again.

Example:
\\pc1\SharedFolderName

I can browse to the location via Windows File Explorer, but the Dir() function does not like it.

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