VBA导出多格式Excel数据至TXT时出现Run-time error '13'类型不匹配问题的解决咨询

The Error 13 you're hitting happens because the Join function only works with string arrays, but your vRow variable contains a mix of data types (Excel error values like #N/D, dates, times, numbers, text) that haven't been converted to strings. Let's break down the fix with targeted code changes to handle all data types properly, while preserving your required hour format for the third column.

Root Cause #

When you use Application.WorksheetFunction.Index(vdat, i, 0) to grab a row from your array, it returns a Variant array with mixed types. Join can't handle this—every element needs to be a string, otherwise you get a type mismatch. Your replaceError function only handles error values, but doesn't convert dates, times, or numbers to strings.

Solution: Process Each Value Individually #

Instead of relying on Join, loop through each cell value in the row, convert it to a string (with specific formatting for dates/times), and build the line text manually. This gives you full control over formatting and error handling.

Updated exportRgToTxt Function

Function exportRgToTxt(rg As Range, filename As String)
    Const SEPARATOR = vbTab
    Dim i As Long, j As Long
    Dim vdat As Variant
    Dim txtFile As Long
    Dim lineText As String
    
    ' Load range values into an array for faster processing
    vdat = rg.Value
    txtFile = FreeFile
    Open filename For Output As txtFile
    
    ' Loop through each row in the array
    For i = LBound(vdat, 1) To UBound(vdat, 1)
        lineText = ""
        ' Loop through each column in the row
        For j = LBound(vdat, 2) To UBound(vdat, 2)
            Select Case True
                ' Handle Excel error values (like #N/D) - replace with empty string or "NA"
                Case IsError(vdat(i, j))
                    lineText = lineText & vbNullString ' Replace with "NA" if you want to keep a marker
                ' Preserve short date format for column B (index 2)
                Case j = 2 And IsDate(vdat(i, j))
                    lineText = lineText & Format(vdat(i, j), "dd/mm/yyyy")
                ' Preserve hour format for column C (index 3)
                Case j = 3 And IsDate(vdat(i, j))
                    lineText = lineText & Format(vdat(i, j), "hh:mm:ss")
                ' Convert all other values to strings
                Case Else
                    lineText = lineText & CStr(vdat(i, j))
            End Select
            
            ' Add separator unless we're on the last column
            If j < UBound(vdat, 2) Then
                lineText = lineText & SEPARATOR
            End If
        Next j
        
        ' Write line to file - omit CRLF for the last row
        If i = UBound(vdat, 1) Then
            Print #txtFile, lineText;
        Else
            Print #txtFile, lineText
        End If
    Next i
    
    Close txtFile
End Function

Additional Fixes for Your Main Sub #

Don't forget to uncomment and properly assign the LastRow variable—otherwise your export range will be incomplete:

Sub eliminar_linea_vba()
    ' ... (keep your existing variable declarations and copy/paste logic)
    
    GenerarEtiqueta.Range("A1:Y100").AutoFilter field:=1, Criteria1:="<>0"
    ' Calculate last row with data
    Dim LastRow As Long
    LastRow = GenerarEtiqueta.Cells(Rows.Count, "A").End(xlUp).Row
    ' Call the updated export function
    exportRgToTxt GenerarEtiqueta.Range("A1:Y" & LastRow), "C:\Users\artur\Desktop\Trigger\Trigger_vba.txt"
End Sub

Key Improvements #

1. No more Join restrictions: By building each line manually, we avoid type mismatch errors entirely.
2. Controlled formatting: Dates (column B) and times (column C) are formatted exactly as you need them.
3. Error handling: Excel error values like #N/D are replaced with empty strings (adjust to "NA" or another marker if needed).
4. Faster processing: Using an array to load range values is still efficient, even with the nested loops.

Optional: Adjust Error Handling #

If you want to keep the error text (e.g., "#N/D") instead of replacing it with an empty string, change this line:

lineText = lineText & vbNullString

to:

lineText = lineText & CStr(vdat(i, j))

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