使用FileDialog打开工作簿并在Excel VBA中进行操作

谢谢Frank.i有了这个想法。 这是工作代码。

Option Explicit
Private Sub CommandButton1_Click()
  Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
  Dim fd As Office.FileDialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls?"
    If .Show = True Then
      fileName = Dir(.SelectedItems(1))
    End If
  End With
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Workbooks.Open (fileName)
  For Each sheet In Workbooks(fileName).Worksheets
    total = Workbooks("import-sheets.xlsm").Worksheets.Count
    Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("import-sheets.xlsm").Worksheets(total)
  Next sheet
  Workbooks(fileName).Close
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

除非我误解了您的问题，否则您只能打开一个只读文件。 这是一个简单的示例，没有进行任何检查。

要从用户获取文件路径，请使用以下功能：

Private Function get_user_specified_filepath() As String
    'or use the other code example here.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = False
    fd.Title = "Please select the file."
    get_user_specified_filepath = fd.SelectedItems(1)
End Function

然后仅以只读方式打开文件并将其分配给变量：

dim wb as workbook
set wb = Workbooks.Open(get_user_specified_filepath(), ReadOnly:=True)