Refer to network drive using network name instead of a mapped letter (eg "G:\")

[Glory]

At my work, the people who set up the computers used a lot of different letters to map the network.

I've tried using the path " \\networkname\ " in place of a letter, with no luck.

Any ideas?

 

[Glory]

All the answers I can find on the web are all about checking to see whether a file exists, and don't offer any information whatsoever about actually incorporating the UNC path into the open method's filename argument.

 

[shg]

I've not used HP's suggestion of fchDirUNC, but it looks more direct than what I use to get a UNC path from a mapped drive:

Function Path2UNC(sFullName As String) As String
    ' Converts the mapped drive path in sFullName to a UNC path if one exists.
    ' If not, returns a null string
 
    Dim sDrive      As String
    Dim i           As Long
 
    sDrive = UCase(Left(sFullName, 2))
 
    With CreateObject("WScript.Network").EnumNetworkDrives
        For i = 0 To .Count - 1 Step 2
            If .Item(i) = sDrive Then
                Path2UNC = .Item(i + 1) & Mid(sFullName, 3)
                Exit For
            End If
        Next
    End With
End Function

So

    Dim sUNC As String
    
    sUNC = Path2UNC("X:\myPath\myFile.xls")
    If Len(Dir(sUNC)) Then
        Workbooks.Open sUNC
'        ...
    End With

 

[Glory]

Like I said, I don't understand how to implement Hot Pepper's solution. I've been trying to figure it out all day.

It looks like your suggestion will only work if you already know the letter that the drive will be mapped to. Am I right about that?

 

[xenou]

All the answers I can find on the web are all about checking to see whether a file exists, and don't offer any information whatsoever about actually incorporating the UNC path into the open method's filename argument

.

As far as I know you can use the UNC path in the open method's filename argument:

Workbooks.Open("\\server1\folder1\Book1.xls")

ξ

 

[Glory]

Thanks for the reply, xenou. Somebody had changed the name of a folder in the path to my file since I last copied it down. I got it working now.

I never bothered to give it more than a cursory glance... especially after Hot Pepper's post, and the mob of hits on Google that say it's not possible to use UNC with either Dir or ChDir directly.

Thanks again.

 

[xenou]

Just testing here at work:
Workbooks.Open(), DIR, and CHDIR all work with a UNC path. Google is wrong? {gasp}