怎么将文件夹中的excel文件批量转换成同名的csv文件，txt制表分隔符格式的文件，求宏代码及制作方法

发布网友发布时间：2022-04-29 18:21

共1个回答

热心网友时间：2023-10-02 17:31

制表分隔符格式的文件不是csv扩展名的.
csv扩展名的文件是以逗号分隔的.
txt扩展名的文件是以制表分隔符分隔的.
存为csv 和txt 的代码我都给你你看需要吧
在要转换的目录新建一个excel文件.
打开这个新的excel文件
ALT+F11进入vba编辑窗口新建一个模块粘贴以下代码.

存为制表分隔符的文件

Sub 转换TXT()
Dim FilePath, MyFile, iPath As String
iPath = ThisWorkbook.Path
MyFile = Dir(iPath & "\*.xls")
If MyFile <> "" Then
Do
On Error Resume Next
If MyFile = ThisWorkbook.Name Then MyFile = Dir
Workbooks.Open (iPath & "\" & MyFile)
MyFile = Replace(MyFile, ".xls", ".txt")
Name = "\" & MyFile
FilePath = iPath & Name
ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:= _
xlText, CreateBackup:=False
Workbooks(MyFile).Close True
MyFile = Dir
Loop While MyFile <> ""
End If
End Sub

Sub 转换CSV()
Dim FilePath, MyFile, iPath As String
iPath = ThisWorkbook.Path
MyFile = Dir(iPath & "\*.xls")
If MyFile <> "" Then
Do
On Error Resume Next
If MyFile = ThisWorkbook.Name Then MyFile = Dir
Workbooks.Open (iPath & "\" & MyFile)
MyFile = Replace(MyFile, ".xls", ".csv")
Name = "\" & MyFile
FilePath = iPath & Name
ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:= _
xlCSV, CreateBackup:=False
Workbooks(MyFile).Close True
MyFile = Dir
Loop While MyFile <> ""
End If
End Sub