VBA 打开 Excel 文件 - 对话框更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43622367/
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
VBA Open an Excel file - Dialog box changes
提问by MMS
I am running an excel vba script, where in I am trying to open a dialog box to select an excel file and open that excel file. I try to give the path of the folder so that the end user can go directly to the folder and select the file that he wants.
我正在运行一个 excel vba 脚本，其中我试图打开一个对话框来选择一个 excel 文件并打开该 excel 文件。我尝试给出文件夹的路径，以便最终用户可以直接进入文件夹并选择他想要的文件。
But, it works fine for the first time but when the next time it runs it opens the folder where in the end user has selected the file last time.
但是，它第一次运行良好，但下次运行时它会打开最终用户上次选择文件的文件夹。
Here goes my code,
这是我的代码，
thisYear = Year(Date)
'change the display name of the open file dialog
Application.FileDialog(msoFileDialogOpen).Title = _
"Select Input Report"
'Remove all other filters
Application.FileDialog(msoFileDialogOpen).Filters.Clear
'Add a custom filter
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
"Excel Files Only", "*.xls*")
'Select the start folder
Application.FileDialog(msoFileDialogOpen _
).InitialFileName = "\driveA\Reports\" & thisYear & ""
file = Application.FileDialog(msoFileDialogOpen).Show
Application.FileDialog(msoFileDialogOpen).Execute
How to work around this?
如何解决这个问题？
采纳答案by David Zemens
It's better to work with an object variable rather than repeatedly calling on Application.FileDialogbecause each call to Application.FileDialogis likely treated as a new instance of that class, which probably explains your issues. This is a hypothesis I haven't tested yet and I'm not 100% but it seems reasonable.
最好使用对象变量而不是重复调用，Application.FileDialog因为每次调用Application.FileDialog都可能被视为该类的新实例，这可能解释了您的问题。这是一个我还没有测试过的假设，我不是 100% 但它似乎是合理的。
Try instead:
试试吧：
Dim fdlg as FileDialog
Set fdlg = Application.FileDialog(msoFileDialogOpen)
'change the display name of the open file dialog
fdlg.Title = "Select Input Report"
'Remove all other filters
fdlg.Filters.Clear
'Add a custom filter
fdlg.Filters.Add "Excel Files Only", "*.xls*"
'Select the start folder
fdlg.InitialFileName = "\driveA\Reports\" & thisYear & ""
'Display to user:
fdlg.Show
'Ensure selection:
If fdlg.SelectedItems.Count <> 0 Then
'Captures the filename chosen:
file = fdlg.SelectedItems(1)
'Then, you probably want to open it:
Set wb = Workbooks.Open(file)
Else
'no file is selected, add error-handling or inform user, exit sub early, etc.
End If
回答by M--
Add this before calling your dialogbox:
在调用对话框之前添加以下内容：
ChDir "\\path\to\my\desired\folder\"
ChDir "\\path\to\my\desired\folder\"
Then use CurDir()instead of explicit path to the folder.
然后使用CurDir()文件夹的显式路径代替。
If you also need to change the drive then use:
如果您还需要更改驱动器，请使用：
ChDrive "X"
ChDrive "X"
Read herealso.
也在这里阅读。
