CODEWITHSUNDEEP
excelvba
Selrac
Selrac

Reputation: 2293

Open file on network directory

I'm trying to specify a network directory when on the open file dialog box.

Sub Get_Data()
    'ChDrive "M:\"
    ChDir "\\netDrive\xxx$\yyy"
    FileToOpen = Application.GetOpenFilename _
      (Title:="Please choose a file to import", _
      FileFilter:="Excel Files *.xls (*.xls),")
    ''
    If FileToOpen = False Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
    Else
        Workbooks.Open Filename:=FileToOpen
    End If
End Sub

The drive is mapped as M, so if I replace the following code it works:

    ChDrive "M:\"
    ChDir "\yyy"

I don't know for sure users have the drive mapped with the same letter.

Is there a way for the macro to use the network path instead?

Upvotes: 2

Views: 25564

Answers (4)

Gizmo
Gizmo

Reputation: 37

If it helps, here's the way to open a file located in your network:

Sub OpnRef()

    Application.Workbooks.Open ("\\Server\Share\Shared Report Area\Reference Docs 
    \Reference1File.xlsx")

End Sub

Upvotes: 0

Codey McCodeface
Codey McCodeface

Reputation: 41

Set fldr = Application.FileDialog(msoFileDialogFilePicker)

You can use this to set the current folder as the folder that appears when the open dialog box appears

Upvotes: 0

Selrac
Selrac

Reputation: 2293

I found the solution here. Find code below I used:

Private Declare Function SetCurrentDirectoryA Lib "kernel32" _
 (ByVal lpPathName As String) As Long

Function SetUNCPath(sPath As String) As Long
 Dim lReturn As Long
 lReturn = SetCurrentDirectoryA(sPath)
 SetUNCPath = lReturn
End Function

Sub Get_Data()
Dim sPath As String
sPath = "\\netDrive\xxx$\yyy"
 If SetUNCPath(sPath) <> 0 Then
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a file to import", _
    FileFilter:="Excel Files *.xls (*.xls),")
    ''
    If FileToOpen = False Then
         MsgBox "No file specified.", vbExclamation, "Duh!!!"
         Exit Sub
        Else
         Workbooks.Open Filename:=FileToOpen
    End If
Else
 MsgBox "Error in setting the UNC path - " & sPath
 End If
End Sub

Upvotes: 2

R3uK
R3uK

Reputation: 14547

You can test if the folder exists with Dir() :

Sub Get_Data()
    If Dir("M:\", vbDirectory) <> vbNullString Then
        '''Drive mapped
        ChDrive "M:\"
    Else
        '''Drive not mapped
        ChDir "\\netDrive\xxx$\yyy"
    End If

    FileToOpen = Application.GetOpenFilename _
                (Title:="Please choose a file to import", _
                FileFilter:="Excel Files *.xls (*.xls),")

    If FileToOpen = False Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
    Else
        Workbooks.Open FileName:=FileToOpen
    End If
End Sub

Upvotes: 1

Related Questions