激活存储在数组中的文件窗口，但出现下标超出范围错误？

Question

Sub Merge()
Dim File      As String
Dim AllFiles(), Filename As Variant
Dim count, test, StartRow, LastRow, LastColumn As Long
Dim LastCell As Variant
test = 0
ChDir "C:\" 'Insert suitable directory for your computer ex:ChDir "C:\Users\Jerry Hou\" if file of interest is in "Jerry Hou" Folder
  ReDim AllFiles(1)
Do
    Application.EnableCancelKey = xlDisabled
    File = Application.GetOpenFilename("XML Files (*.xml),*.xml", 1, "Select File to be Merged") 'Needs to select in Order to merge files
    Application.EnableCancelKey = xlErrorHandler
    If (File = "False") Then Exit Do
    ReDim Preserve AllFiles(count) 'Preserve ?
    AllFiles(count) = File 'File== file name and directory
    count = (count + 1)
    If (MsgBox("Select Another File To be Merged With?", vbQuestion + vbOKCancel, "Merge Files") = vbCancel) Then Exit Do
Loop  'Select Cancel in MsgBox to finish merge file(s) selection

If (count = 0) Then
    MsgBox "No selection" 'If you hit Exit from open prompt window
    Exit Sub
End If

 For count = 0 To UBound(AllFiles)
    MsgBox "User selected file name: " & AllFiles(count)

Next
 test = count
 For test = UBound(AllFiles) To LBound(AllFiles) Step -1
 Workbooks.Open Filename:=AllFiles(test)
Next

ReDim AllFiles(count)
 test = 2
Do While (test <= count)
Filename = AllFiles(test)
Workbooks(AllFiles(test)).Activate 'ERROR Brings 2nd file that the user had selected to Last xml file selected in order to Front
 'Copy and Paste TMG tab
 Sheets("TMG_4 0").Activate
 StartRow = 2
 LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 LastCell = Cells(LastRow, LastColumn).Address 'Find lastcell of to be copied file
 Range("A2:" & LastCell).Select
 Selection.Copy
 Windows("Allfiles(1).xml").Activate 'ERROR
 Sheets("TMG_4 0").Activate
 LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 LastRow = LastRow + 1
 Range("LastRow").Select 'ERROR
 ActiveSheet.Paste

 'Copy and Paste Gamma tab
 Sheets("GammaCPS 0").Activate
 StartRow = 2
 LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 LastCell = Cells(LastRow, LastColumn).Address
 Range("A2:" & LastCell).Select
 Selection.Copy

 Windows("Allfiles(1).xml").Activate 'ERROR Windows("File_name.xlsm").activate 
 Sheets("GammaCPS 0").Activate
 LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 LastRow = LastRow + 1
 Range("LastRow").Select 'ERROR
 ActiveSheet.Paste
 test = test + 1
Loop

Windows("Allfiles(1).xml").Activate 'ERROR

ActiveWorkbook.SaveAs Filename:="C:\" & AllFiles(1) & AllFiles(test) & ".xlsm", FileFormat:=52

结束子

Answer 1

• 您重新调整 AllFiles 但从未用任何内容填充它。是否缺少代码？
• AllFiles 是一个基于 0 的数组，因此如果您想从第二个元素开始，则需要使用 test = 1 而不是 test = 2。

• 要循环遍历数组，请尝试以下操作：

  For test = 1 to ubound(AllFiles) - 1 '这会从第二个元素到最后一个元素循环遍历数组

• “LastRow”是命名范围吗？如果没有，那是行不通的。以下将选择工作表中最后使用的行：

  activesheet.Rows(activesheet.usedrange.rows.count).select

• 您的 SaveAs 失败，因为 1) AllFiles 看起来从未填充过，并且2）您所写的保存路径实际上是：C:\Allfile(1)&Allfiles(count)\.xlsm。你想要：

  ActiveWorkbook.SaveAs 文件名:= "C:\" &所有文件(1) &所有文件（测试）& “.xlsm”

代码更新后编辑

• 您永远不会初始化计数变量，为了安全起见，将 count = 0 添加到开头。

• GetOpenFilename 实际上返回完整路径。将路径存储在变量（例如 AllFiles()）中后，您可以使用 mid(AllFiles(test), instrrev(AllFiles(test), "\") + 1)仅获取文件名部分code>

• 您不需要在主 Do 循环之前使用 ReDim AllFiles(count)。除非您使用 Preserve 关键字，否则 ReDim 会擦除数组的内容。

• 将 Workbooks(AllFiles(test)).Activate 更改为 Workbooks(Mid(AllFiles(test), InStrRev(AllFiles(test), "\") + 1)).Activate 删除路径信息并仅保留文件名。

• Windows("Allfiles(1).xml").Activate 将不起作用，因为您发送了文字字符串。您需要再次在此处 WORKBOOKS(Mid(AllFiles(1), InStrRev(AllFiles(1), "\") + 1)).Activate。

• LastRow = LastRow + 1 可能不是您的意思。尝试 Set LastRow = LastRow.Offset(1, 0)

• 将 Range("LastRow").Select 更改为 LastRow.select< /p>

• 所有 Windows( 实例应更改为Workbooks(