excel - 将excel文件另存为不带分隔符的文本并保留空单元格

Question

我有 excel 表想将其保存为 txt 文件，但没有分隔符，同时将空单元格的空间保持在范围内

下面是代码

    Dim WorkRng As Range
Dim xFile As Variant
Dim xFileString As String
On Error Resume Next
xTitleId = "Define Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ActiveSheet.Copy
Application.ActiveSheet.Cells.Clear
WorkRng.Copy Application.ActiveSheet.Range("A1")
Set xFile = CreateObject("Scripting.FileSystemObject")



xFileString = Application.GetSaveAsFilename("") 'fileFilter:="Comma Separated Text (*.CSV), *.CSV")
Application.ActiveWorkbook.SaveAs Filename:=xFileString & ".txt", FileFormat:=xlText, CreateBackup:=False
End Sub

标签： excelvba

Answer 1

您可以使用以下代码：

Sub SaveAsText()

    '===============================================================
    '       First get the used range
    '===============================================================
    Dim cells As Range
    Set cells = ActiveSheet.UsedRange
    
    '===============================================================
    '       Now, iterate over all cells and store data
    '===============================================================
    'Open a text file to write data in
    Dim myFile As String
    myFile = ThisWorkbook.Path & "\output.txt"
    
    Open myFile For Output As #1
    
    Dim cell As Range
    Dim row As Integer
    Dim str As String
    row = 1 'used to write each Excel row in a single row in the text file
    For Each cell In cells
        
        'Is it a new row?
        If cell.row <> row Then 'yes
            
            'Write the previous row's values
            Write #1, str
            
            'Update row
            row = row + 1
            
            'Reset str
            str = cell.Value
            
        Else 'no
            
            'Update str
            If IsEmpty(cell) Then
                str = str & " "
            Else
                str = str & cell.Value
            End If
            
            
        End If
        
    Next
    
    'Write the last str
    Write #1, str
    

    'Close the file
    Close #1
    
End Sub

此代码为您的示例生成以下输出：

我已将输出文件命名为“output.txt”。你可以随意命名它。