将 Excel 文件保存为不带引号的 .txt 格式 [英] Saving a Excel File into .txt format without quotes

问题描述

我有一个 Excel 工作表，其中 A 列中有数据.单元格中有许多特殊字符.当我以 .txt 格式保存工作表时，每行的开头都会出现引号.我尝试手动和宏将文件保存为 .txt 格式.为什么会这样?如何删除它们?我无法删除引号.附上图片

I have a excel sheet which has data in column A.There are many special characters in the cells.When I save the sheet in .txt format I get inverted commas at the start of each line. I tried both manually and by macro saving the file in .txt format.Why is it so? How to remove them? I am not able to remove the quotes. Attaching a pic

推荐答案

试试这个代码.这可以满足您的需求.

Try this code. This does what you want.

逻辑

1. 将文件另存为用户临时目录中的 TAB 分隔文件
2. 一键读取文本文件
3. 用空格替换 "" 并同时写入新文件.

1. Save the File as a TAB delimited File in the user temp directory
2. Read the text file in 1 go
3. Replace "" with blanks and write to the new file at the same time.

代码(久经考验)

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

'~~> Change this where and how you want to save the file
Const FlName = "C:UsersSiddharth RoutDesktopMyWorkbook.txt"

Sub Sample()
    Dim tmpFile As String
    Dim MyData As String, strData() As String
    Dim entireline As String
    Dim filesize As Integer

    '~~> Create a Temp File
    tmpFile = TempPath & Format(Now, "ddmmyyyyhhmmss") & ".txt"

    ActiveWorkbook.SaveAs Filename:=tmpFile _
    , FileFormat:=xlText, CreateBackup:=False

    '~~> Read the entire file in 1 Go!
    Open tmpFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    '~~> Get a free file handle
    filesize = FreeFile()

    '~~> Open your file
    Open FlName For Output As #filesize

    For i = LBound(strData) To UBound(strData)
        entireline = Replace(strData(i), """", "")
        '~~> Export Text
        Print #filesize, entireline
    Next i

    Close #filesize

    MsgBox "Done"
End Sub

Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

快照

实际工作簿

保存后

这篇关于将 Excel 文件保存为不带引号的 .txt 格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！