将Excel文件保存为.txt格式,不带引号 [英] Saving a Excel File into .txt format without quotes
本文介绍了将Excel文件保存为.txt格式,不带引号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个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.
LOGIC
- 将文件作为TAB分隔的文件保存在用户临时目录中
- 在1 go中阅读文本文件
- 替换
- Save the File as a TAB delimited File in the user temp directory
- Read the text file in 1 go
- Replace
""
with blanks and write to the new file at the same time.
CODE TRIED AND TESTED)
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:\Users\Siddharth Rout\Desktop\MyWorkbook.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
SNAPSHOTS
实际工作簿
保存后
这篇关于将Excel文件保存为.txt格式,不带引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文