Excel VBA合并文件异常：数据少一行且最后文件数据未写入求助

Hey there, let’s work through those two bugs in your CombineFiles macro—they’re common issues with easy fixes once you know where to look.

Issue 1: Missing One Row of Data #

Most of the time, this happens because your code is either skipping the first row (like headers) or failing to capture the last row of the text file. Here’s what to check:

• If you’re using CurrentRegion starting at row 2: If your code uses something like Range("A2").CurrentRegion, it’s intentionally skipping the first row. Switch it to Range("A1").CurrentRegion to include all rows.
• End(xlUp) not detecting the last row: Sometimes Excel doesn’t recognize the final row if it has trailing spaces or if the cell isn’t "actively" edited. Instead of relying solely on End(xlUp), use a combination to get the true last row:

  Dim xLastRow As Long
  xLastRow = xTempWb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
  ' Then copy from row 1 to xLastRow (adjust to row 2 if you want to skip headers)
  xTempWb.Sheets(1).Range("A1:" & Cells(xLastRow, xTempWb.Sheets(1).UsedRange.Columns.Count).Address).Copy
  

• Text import starting at row 2: If you’re using Workbooks.OpenText, double-check the StartRow parameter is set to 1 instead of 2—this ensures you import every line from the text file.

Issue 2: Last Text File’s Data Isn’t Copied to Theta Sheet #

This almost always boils down to a loop indexing mistake. Here’s how to fix it:

• Check your loop bounds: If your code uses For I = 1 To UBound(xFilesToOpen) - 1, you’re explicitly skipping the last file. Instead, loop through every file in the selected array using LBound and UBound to cover all indexes safely:

  ' After getting xFilesToOpen with GetOpenFilename
  If TypeName(xFilesToOpen) = "Boolean" Then Exit Sub ' Exit if user cancels selection
  
  For I = LBound(xFilesToOpen) To UBound(xFilesToOpen)
      ' Your code to open the temp workbook, copy data, paste to Theta sheet
      ' ...
      xTempWb.Close SaveChanges:=False
  Next I
  

• Ensure copy/paste runs for every iteration: Make sure your copy and paste logic is fully inside the loop—sometimes code accidentally puts the final paste outside the loop, which misses the last file.

Modified Code Snippet #

Here’s a cleaned-up version of the core logic that addresses both issues:

Sub CombineFiles()
    Dim xFilesToOpen As Variant
    Dim I As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    Dim xDelimiter As String
    Dim xScreen As Boolean
    Dim StartTime As Double
    Dim MinutesElapsed As String
    
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    StartTime = Timer
    
    ' Select multiple text files
    xFilesToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Select Text Files to Combine", , True)
    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files selected.", vbExclamation
        Application.ScreenUpdating = xScreen
        Exit Sub
    End If
    
    xDelimiter = "," ' Adjust this to your text file's delimiter
    
    For I = LBound(xFilesToOpen) To UBound(xFilesToOpen)
        ' Open text file with correct settings
        Set xTempWb = Workbooks.OpenText( _
            Filename:=xFilesToOpen(I), _
            Origin:=xlWindows, _
            StartRow:=1, _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Comma:=(xDelimiter = ","), _
            Tab:=(xDelimiter = vbTab), _
            Semicolon:=(xDelimiter = ";"), _
            Space:=(xDelimiter = " ") _
        ).Workbooks(1)
        
        ' Get source range (skip headers after first file if needed)
        Dim xSourceRange As Range
        Set xSourceRange = xTempWb.Sheets(1).UsedRange
        If I > LBound(xFilesToOpen) Then
            ' Skip header row for subsequent files
            Set xSourceRange = xSourceRange.Offset(1, 0).Resize(xSourceRange.Rows.Count - 1)
        End If
        
        ' Paste to Theta sheet
        Dim xDestRow As Long
        xDestRow = ThisWorkbook.Sheets("Theta").Cells(Rows.Count, 1).End(xlUp).Row + 1
        xSourceRange.Copy Destination:=ThisWorkbook.Sheets("Theta").Cells(xDestRow, 1)
        
        ' Clean up
        xTempWb.Close SaveChanges:=False
        Set xTempWb = Nothing
    Next I
    
    ' Calculate elapsed time
    MinutesElapsed = Format((Timer - StartTime) / 60, "0.00")
    MsgBox "Combined " & UBound(xFilesToOpen) - LBound(xFilesToOpen) + 1 & " files in " & MinutesElapsed & " minutes.", vbInformation
    
    Application.ScreenUpdating = xScreen
End Sub

This version fixes both issues by:

1. Ensuring all rows are imported (via StartRow:=1 and proper range selection)
2. Looping through every selected file using safe array bounds
3. Adding optional header skipping for subsequent files
4. Including error handling for canceled file selection

内容的提问来源于stack exchange，提问作者Jayakumar krishnamoorthy