vba 打开网络目录中的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42366047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Open file on network directory
提问by Selrac
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:
驱动器被映射为 M，所以如果我替换以下代码，它会起作用：
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?
宏有没有办法改用网络路径？
回答by R3uK
You can test if the folder exists with Dir():
您可以使用以下命令测试文件夹是否存在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
回答by Selrac
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
回答by Gizmo
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
回答by Codey McCodeface
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
可以用这个把当前文件夹设置为打开对话框出现时出现的文件夹
