我目前正在使用以下代码提示用户输入工作簿，打开它，从中获取一些信息，然后关闭它。目前，我通过使用工作簿集合和索引（“woorkbooks（2）”）来处理打开的工作簿。现在我需要打开两个工作簿，我的问题是我不知道哪些工作簿将被索引为 2，哪些将被索引为 3。所以，我认为必须有一种方法来获得对每个工作簿的引用工作簿。
Function openfile() As Boolean
Dim fd As FileDialog
Dim file_was_chosen As Boolean
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.Filters.Clear
.Filters.Add "Excel File", "*.xl*"
End With
file_was_chosen = fd.Show
If Not file_was_chosen Then
MsgBox "You didn't select a file"
openfile = False
Exit Function
End If
fd.Execute
openfile = True
End Function
现在我已经看到了一些解决这个问题的解决方案，包括获取每个工作簿的完整路径，但我更愿意避免使用完整路径，因为它包含不同语言的单词（工作簿的名称显示为问号）。此外，我更喜欢一种解决方案，其中用户只提示一次 2 个文件而不是两次。
此版本为用户提供了一个对话框。享受。无论谁对我的其他答案投反对票，请在其中添加评论，解释您不喜欢它的原因，以至于需要投反对票。
Function openfile() As Variant
Dim aOpen(2) As String, itm As Variant, cnt As Long, lAsk As Long
Dim fd As FileDialog
Dim file_was_chosen As Boolean
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.Filters.Clear
.Filters.Add "Excel File", "*.xl*"
End With
Do
file_was_chosen = fd.Show
If Not file_was_chosen Or fd.SelectedItems.Count > 2 Then
lAsk = MsgBox("You didn't select one or two files, try again?", vbQuestion + vbYesNo, "File count mismatch")
If lAsk = vbNo Then
openfile = aOpen
Exit Function
End If
End If
Loop While fd.SelectedItems.Count < 1 Or fd.SelectedItems.Count > 2
cnt = 0
For Each itm In fd.SelectedItems
aOpen(cnt) = itm
cnt = cnt + 1
Next
openfile = aOpen
fd.Execute
End Function
Sub test()
Dim vRslt As Variant
Dim wkb As Excel.Workbook, wkb1 As Excel.Workbook, wkb2 As Excel.Workbook
vRslt = openfile
For Each wkb In Application.Workbooks
If wkb.Path & "\" & wkb.Name = vRslt(0) Then Set wkb1 = wkb
If wkb.Path & "\" & wkb.Name = vRslt(1) Then Set wkb2 = wkb
Next
If vRslt(0) = "" Then ' no files
MsgBox "No files opened so nothing happens..."
ElseIf vRslt(1) = "" Then ' one file was opened
MsgBox "One file so do whatever you want for one file"
Else ' two files were opened
MsgBox "Two files so do whatever you want for two files"
End If
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句