vba - Excel 2016 宏无法找到文件 : Runtime Error 1004

Question

我在 Excel 2016 中尝试使用 VBA 打开其他 Excel 文件时遇到问题。文件是否位于同一目录中并不重要。我认为这与 Excel 2016 中阻止搜索的默认设置有关？该宏在 Excel 2010 中运行。

Private Sub CommmandButton_Click()
Dim source As String
Dim temp As Workbook

source = InputBox("Enter source")

Set temp = Workbooks.Open(source)

end sub

Answer 1

这是使用 FileDialog 对象的示例解决方案

Private Sub CommmandButton_Click()
    Dim fDialog As FileDialog, _
        wb      As Excel.Workbook
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .AllowMultiSelect = False
        .Title = "Select a file"
        .InitialFileName = "C:\"
        .Filters.Clear
        ' Prevent Error by specifying that the user must use an excel file
        .Filters.Add "Excel files", "*.xlsx,*.xls,*.xlsm"
    End With
    If fDialog.Show = -1 Then
       Set wb = Excel.Workbooks.Open(fDialog.SelectedItems(1))
    Else 
        End  ' Cleanly exit the Macro if the user cancels
    End If

End Sub