将Excel文件保存为制表符分隔的文本文件，不带引号

Question

我有一个 Excel 2010 工作簿。我需要将每个工作表的使用范围保存为制表符分隔的文本文件，不带引号，文件名与工作簿相同，扩展名由工作表名称指定。

请注意，只要 Excel 看到逗号，就会愚蠢地用引号将值括起来，即使分隔符是制表符；除此之外，正常的“另存为”/“文本（制表符分隔）”就可以了。

我更愿意使用 Excel 中的 VBA 代码来完成此操作。

如果有Python解决方案，我也会感兴趣。但目前 pywin32 对 Python 3 的支持还只是实验性的，所以我不确定我是否可以使用它。

Answer 1

好的，这是一个稍微复杂的例程，是我几个月前为我的一位客户写的。此代码将 Excel 工作表导出到不带引号的固定宽度文件。还附上了屏幕截图。我相信这段代码可以做得更好:)

经过尝试和测试

Option Explicit

'~~> Change this to relevant output filename and path
Const strOutputFile As String = "C:\Output.Csv"

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim MyArray() As Long, MaxLength As Long
    Dim ff As Long, i As Long, lastRow As Long, LastCol As Long
    Dim strOutput As String

    On Error GoTo Whoa

    Application.ScreenUpdating = False

    '~~> Change this to the respective sheet
    Set ws = Sheets("Sheet1")
    LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

    '~~> Loop through each Column to get the max size of the field
    For i = 1 To LastCol
        MaxLength = getMaxLength(ws, i)
        ReDim Preserve MyArray(i)
        MyArray(i) = MaxLength
    Next i

    ff = FreeFile

    '~~> output file
    Open strOutputFile For Output As #ff

    '~~> Write to text file
    With ws
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row

        For Each rng In .Range("A1:A" & lastRow)
            With rng
                For i = 1 To UBound(MyArray)
                    '~~> Insert a DELIMITER here if your text has spaces
                    strOutput = strOutput & " " & Left(.Offset(0, i-1).Text & _
                                String(MyArray(i), " "), MyArray(i))
                Next i

                Print #ff, Mid(Trim(strOutput), 1)
                strOutput = Empty
            End With
        Next rng
    End With

LetsContinue:
    On Error Resume Next
        Close #ff
    On Error GoTo 0
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

'~~> Function to get the max size
Public Function getMaxLength(ws As Worksheet, Col As Long) As Long
    Dim lastRow As Long, j As Long

    getMaxLength = 0

    lastRow = ws.Range("A" & ws.Rows.Count).End(-4162).Row

    For j = 1 To lastRow
        If Len(Trim(ws.Cells(j, Col).Value)) > getMaxLength Then _
        getMaxLength = Len(Trim(ws.Cells(j, Col).Value))
    Next j
End Function

Answer 2

打开您的 Excel/csv/文本
执行您所需的操作，然后您可以使用文件格式另存为 xlTextPrinter

ActiveWorkbook.SaveAs Filename:="Your File Name.txt", FileFormat:=xlTextPrinter, CreateBackup:=False

不需要额外的代码来替换额外的引号