首页 文章

从“打开文件”对话框获取工作簿参考

提问于
浏览 次
0

我正在使用Excel 2013宏从用户选择的工作簿中提取数据，我的vba有点生疏 .

Application.GetOpenFilename 提示用户输入文件位置，打开文件并返回一个字符串 . Workbooks.Open(string) 返回工作簿 - 如果您事先知道该名称 .

我想结合这些来询问用户打开哪个文件，并返回一个工作簿 .

基于弗兰克的回答（Open a workbook using FileDialog and manipulate it in Excel VBA）我试过这个：

Function openDataFile() As Workbook
'
  Dim wb As Workbook
  Dim filename As String
  Dim fd As Office.FileDialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  fd.AllowMultiSelect = False
  fd.Title = "Select the file to extract data"
  'filename = fd.SelectedItems(1)
  Set wb = Workbooks.Open(fd.SelectedItems(1))
  openDataFile = wb

End Function

但这会在 Run-time error '5': Invalid procedure call or argument. 的注释行中落空

如何提示用户打开excel文件，并将其作为工作簿返回？

excel-vba excel-2013 vba excel

2 回答

  • 2

    请尝试以下代码：

    Function openDataFile() As Workbook
'
Dim wb            As Workbook
Dim filename      As String
Dim fd            As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the file to extract data"

' Optional properties: Add filters
fd.Filters.Clear
fd.Filters.Add "Excel files", "*.xls*" ' show Excel file extensions only

' means success opening the FileDialog
If fd.Show = -1 Then
    filename = fd.SelectedItems(1)
End If

' error handling if the user didn't select any file
If filename = "" Then
    MsgBox "No Excel file was selected !", vbExclamation, "Warning"
    End
End If

Set openDataFile = Workbooks.Open(filename)

End Function

    然后我添加了下面的Sub来测试这个功能：

    Sub test()

Dim testWb  As Workbook

Set testWb = openDataFile    
Debug.Print testWb.Name

End Sub
    回复于
  • 0

    看起来你没有显示 FileDialog 所以可能是这样的：

    Function openDataFile() As Workbook
'
  Dim wb As Workbook
  Dim filename As String
  Dim fd As Office.FileDialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  fd.AllowMultiSelect = False
  fd.Title = "Select the file to extract data"
  fd.show
  On Error Resume Next ' handling error over the select.. later in the script you could have an `if fileName = "" then exit sub` or something to that affect
  fileName = fd.SelectedItems(1)
  On Error GoTo 0
  Set wb = Workbooks.Open(fileName)
  openDataFile = wb

End Function
    回复于

相关问题