Excel VBA多选和将工作簿设置为变量

Question

之前我有一段代码，它使用GetOpenFilename来选择单个文件，设置变量，并询问您是否要选择另一个文件(Y/N)，执行更多代码以打开第二个文件等。如果用户在任何时候选择“NO”，则会跳过其余的代码。

现在我正在尝试让一个存储在工作簿中的宏打开最多三个文件。然后需要将每个工作簿作为自己的变量，因为它需要在每个工作簿中查找一个字符串，然后将这些变量传递到另一个模块。我无法弄清楚如何为代码设置每个工作簿。感激任何帮助。

Dim files As Variant
Dim i As Integer
Dim WBtemp1 As Workbook
Dim WBtemp2 As Workbook
Dim WBtemp3 As Workbook
Dim RF As String

    files = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xls*),*.xls*", Title:="Please select up to 3 Files", MultiSelect:=True)

    If Not IsArray(files) Then Exit Sub

    If UBound(files) < 3 Then
        MsgBox "You have selected more than 3 files."

    End If



    For i = 1 To UBound(files)

    Workbooks.Open files(i)

    Next

    Set WBtemp1 = Workbooks(1)
    Set WBtemp2 = Workbooks(2)
    Set WBtemp3 = Workbooks(3)


    RF = WBtemp1.Worksheets(1).Range("V3")
    RF1 = Mid(RF, 12, 8)
    RF = WBtemp2.Worksheets(1).Range("V3")
    RF2 = Mid(RF, 12, 8)
    RF = WBtemp3.Worksheets(1).Range("V3")
    RF3 = Mid(RF, 12, 8)



        Call Macro2(WBtemp1, WBtemp2, WBtemp3, RF1, RF2, RF3)

- Scott

Answer 1

要将您的变量设置为指向3个选定的工作簿，您需要使用存储在files数组中的值，并提取不带文件夹路径的文件名，然后使用它来引用工作簿：

Dim filenames(1 To 3) As String
For i = 1 To 3
    filenames(i) = Right(files(i), Len(files(i)) - InStrRev(files(i), "\"))
Next i
Dim WBtemp1, WBtemp2, WBtemp3

Set WBtemp1 = Workbooks(filenames(1))
Set WBtemp2 = Workbooks(filenames(2))
Set WBtemp3 = Workbooks(filenames(3))

或者更好的做法是，您可以在打开每个工作簿时将其分配给一个变量，方法如下：

Set WBtemp1 = Workbooks.Open(files(1))
Set WBtemp2 = Workbooks.Open(files(2))
Set WBtemp3 = Workbooks.Open(files(3))

- DecimalTurn

---

这会导致“Subscript out of range”错误发生在 Set WBtemp1 = Workbooks(files(1)) 无论是打开一个文件还是多个文件。 - Scott

谢谢。这正是我发现的问题。但是，这会导致Excel重新打开工作簿吗？ - Scott

你需要将打开工作簿的代码替换为将其分配给变量的代码行，以避免重复打开文件。 - DecimalTurn

Answer 2

这里是我认为你可以实际使用的代码。它展示了如何创建和处理工作簿数组，并为Rf创建类似的数组。我添加了一个Macro2子程序，它将数组作为参数以及一个只接受数组成员之一的函数（一个子程序将以相同方式调用）。

Option Explicit

Sub SelectFiles()

    Dim Files As Variant
    Dim WbTempl() As Workbook
    Dim Tmp As String
    Dim Rf() As String
    Dim i As Integer

    Files = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xls*),*.xls*", _
                                        Title:="Please select up to 3 Files", _
                                        MultiSelect:=True)

    If IsArray(Files) Then                  ' avoid jumps: let the code flow
        For i = 1 To UBound(Files)
            If i > 3 Then
                MsgBox "You selected " & UBound(Files) & " files." & vbCr & _
                       "Only the first 3 will be processed.", _
                       vbInformation, "Too many files selected"
                ReDim Preserve Files(1 To 3)
                Exit For
            Else
                ReDim Preserve WbTempl(1 To i)
            End If
            Set WbTempl(i) = Workbooks.Open(Files(i))
        Next i

        ReDim Rf(1 To UBound(WbTempl))
        For i = 1 To UBound(WbTempl)
            Tmp = WbTempl(i).Worksheets(1).Range("V3").Value
            If Len(Tmp) < 20 Then
                Tmp = String(20, "R")
            End If
            Rf(i) = Mid(Tmp, 12, 8)
            If IsTemplate(WbTempl(i), Rf(i)) = True Then
                Tmp = " "
            Else
                Tmp = " not "
            End If
            MsgBox WbTempl(i).Name & " is" & Tmp & "a template", vbInformation
        Next i

        ' using meaningful names isn't a useless exercise
        ' using nondescript names will cost you much more time eventually
        Macro2 WbTempl, Rf
    End If
End Sub

Private Sub Macro2(Wb() As Workbook, _
                   Rf() As String)

    Dim i As Integer

    For i = 1 To UBound(Wb)
        Debug.Print Rf(i), Wb(i).Name
    Next i
End Sub

Private Function IsTemplate(Wb As Workbook, _
                           ByVal Rf As String) As Boolean

    Debug.Print "Here is Function 'IsTemplate'. Current Rf = "; Rf
    IsTemplate = (Wb.FileFormat = xlTemplate)
End Function

- Variatus

Answer 3

修改为：在模块中添加了一个On Error Resume Next。

On Error Resume Next
Dim MyFiles As Variant
Dim i As Integer
Dim WBtemp1 As Workbook
Dim WBtemp2 As Workbook
Dim WBtemp3 As Workbook

    MyFiles = Application.GetOpenFilename(FileFilter:="Excel workbooks(*.xls*),*.xls*", Title:="Please select up to 3 Files", MultiSelect:=True)

    If Not IsArray(MyFiles) Then Exit Sub

    If UBound(MyFiles) > 3 Then
        MsgBox "You have selected more than 3 files."

    End If

    For i = 1 To UBound(MyFiles)

    Workbooks.Open MyFiles(i)

    Next i


    Set WBtemp1 = Workbooks.Open(MyFiles(1))

    If UBound(MyFiles) < 2 Then GoTo Sng Else GoTo Mult

Mult:
    Set WBtemp2 = Workbooks.Open(MyFiles(2))
    Set WBtemp3 = Workbooks.Open(MyFiles(3))

        RF = WBtemp1.Worksheets(1).Range("V3")
        RF1 = Mid(RF, 12, 8)

        RF = WBtemp2.Worksheets(1).Range("V3")
        RF2 = Mid(RF, 12, 8)

        RF = WBtemp3.Worksheets(1).Range("V3")
        RF3 = Mid(RF, 12, 8)

Sng:
    Set wb1 = ActiveWorkbook


        Call Macro2(WBtemp1, WBtemp2, WBtemp3, RF1, RF2, RF3)

- Scott