当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Open Dialog defaulting to specified network path (not drive)

问题描述:

I'm trying to assign my open dialog box to default to a specific folder on the network. Let's use the path:

\\test\yes\no\

The code below doesn't work, but also doesn't error out. I can't figure out what I'm doing wrong.

Private Declare Function SetCurrentDirectory _

Lib "kernel32" _

Alias "SetCurrentDirectoryA" ( _

ByVal lpPathName As String) _

As Long

SetCurrentDirectory "\\test\yes\no\"

I've seen a few ways people are doing this, but nothing seems to be working for me. I'm using excel 2010 if that helps.

My directory code:

With Application.FileDialog(msoFileDialogFolderPicker)

SetCurrentDirectory "\\test\yes\no\"

.AllowMultiSelect = False

If .Show <> -1 Then MsgBox "No folder selected! Exiting script.": Exit Sub

myDir = .SelectedItems(1)

End With

MsgBox "Please choose the folder."

Application.DisplayAlerts = False

'*********************************************************************************************

'Check for .xls cutsheets; open one at a time with a loop until all file data has been copied

'*********************************************************************************************

folderPath = myDir

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

fileName = Dir(folderPath & "*.xls")

Do While fileName <> ""

Application.ScreenUpdating = False

Set wbkCS = Workbooks.Open(folderPath & fileName)

网友答案:

I do not either get it to work using Application.FileDialog and SetCurrentDirectory. However, using the InitialFileName property and setting it to "\\test\yes\no\" (i.e. only specifying a path) does work.

With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = "\\test\yes\no\"
    .AllowMultiSelect = False
    If .Show <> -1 Then MsgBox "No folder selected! Exiting script.": Exit Sub
        myDir = .SelectedItems(1)
End With
网友答案:

I'm able to open a dialog box using the following:

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


Sub OpenDialogInNetworkPath()
    SetCurrentDirectory "\\test\yes\no\"
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a file to import", _
    FileFilter:="Excel Files *.xls (*.xls),")
    Workbooks.Open Filename:=FileToOpen
End Sub

Does this help? Can you please post the full macro?

分享给朋友:
您可能感兴趣的文章:
随机阅读: