时间戳

首页 > 解决方案 > 将 Excel 文件转换为文本文件

excel - 将 Excel 文件转换为文本文件

问题描述

我正在尝试将 Excel 文件转换为文本文件，每个列标题和行值都用“”包围，分隔符是 ;。

我找到了一个在一定程度上有效的代码。不起作用的是 Excel 中的最后一列没有值。

在第 2 行，倒数第二列的值为“2010 年 1 月 1 日”。

这是错误的，上传网站拒绝它。

  1. 如何修复 VBA 代码？
  2. 默认情况下，它保存在 Documents 文件夹中。
    如何将 txt 文件保存到特定路径\文件夹？
Public Sub OutputQuotedCSV()
    Const QSTR As String = """"
    Dim myRecord As Range
    Dim myField As Range
    Dim nFileNum As Long
    Dim sOut As String
        
    nFileNum = FreeFile
    Open "File2.txt" For Output As #nFileNum
    For Each myRecord In Range("A1:A" & _
      Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Range(.Cells(1), _
              Cells(.Row, 256).End(xlToLeft))
                sOut = sOut & ";" & QSTR & _
                  Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
            Next myField
            Print #nFileNum, Mid(sOut, 2)
            sOut = Empty
        End With
    Next myRecord
    Close #nFileNum
End Sub

标签： excelvba

解决方案

您当前的代码仅运行到每行占用单元格的末尾（因为它在每一行上运行 xlToLeft）

您可以改为这样做 - 从标题行获取行长并将其用于每一行：

Public Sub OutputQuotedCSV()
    Const QSTR As String = """"
    Const SEP As String = ";"
    Dim myRecord As Range
    Dim myField As Range
    Dim nFileNum As Long, rngOut As Range
    Dim sOut As String, s As String
    Dim lstRow As Long, lstCol As Long

    nFileNum = FreeFile
    Open "C:\Tester\File2.txt" For Output As #nFileNum

    'find the range to be exported
    With ActiveSheet
        lstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lstCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set rngOut = .Range(.Range("A1"), .Cells(lstRow, lstCol))
    End With

    'loop over each row in the export range
    For Each myRecord In rngOut.Rows

        sOut = ""
        s = "" 'no separator for first record
        'loop over each cell in the row
        For Each myField In myRecord.Cells
            'quote the cell text, escaping any embedded " with ""
            sOut = sOut & s & QSTR & Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
            s = SEP 'add the separator for subsequent fields
        Next myField
        Print #nFileNum, sOut

    Next myRecord

    Close #nFileNum
End Sub

推荐阅读