如何将多个txt文件转换并保存到Excel

Question

我有几个txt文件在一个特定的文件夹中，我想通过将这些文件转换为Excel来进行文本分列。然后, 我想将各自的Excel文件保存在同一文件夹中，删除文本文件并只保留Excel文件。

我需要VBA代码来完成此操作，并且可以通过筛选A列中的空格并删除所有空格。

谢谢您的帮助。

- vinmer

Answer 1

尝试这个 ***记得更改文件夹名称（spath）!****：

Sub getTextFiles()

Dim spath As Variant
Dim sfile As Variant
Dim lc As Variant
Dim txtBook As Workbook
Dim x As Variant
Dim saveName As String

Application.DisplayAlerts = False

'IMPORTANT!!!!!
'Set path to folder where you keep the text files
spath = "C:\test\"

'Loop through all text files in the folder
sfile = Dir(spath & "*.txt")

Do While sfile <> ""

    'Open the text file
    Set txtBook = Workbooks.Open(spath & sfile)

    'Text to Columns - comma separated
    txtBook.Sheets(1).Columns(1).TextToColumns Destination:=txtBook.Sheets(1).Cells(1, 1), DataType:=xlDelimited, _
    Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False

    'Find last row with data
    lc = txtBook.Sheets(1).Cells(txtBook.Sheets(1).Rows.Count, "a").End(xlUp).Row

    'Loop through all rows in column "A" and delete the row if cell is blank
    For x = lc To 1 Step -1
        If txtBook.Sheets(1).Cells(x, 1) = "" Then txtBook.Sheets(1).Cells(x, 1).EntireRow.Delete
    Next x

    'Save file as xlsx and close it

    'File name without the ".txt" part
    saveName = Left(sfile, Len(sfile) - 4)

    txtBook.SaveAs Filename:=spath & saveName, FileFormat:=51, CreateBackup:=False
    txtBook.Close


    Set txtBook = Nothing

    'Delete old text file
    Kill spath & sfile

    'Get another file
    sfile = Dir()

Loop

Application.DisplayAlerts = True

End Sub

- Dawid SA Tokyo

---

谢谢亲爱的，看起来不错。但是有一个小问题。我应该使用的分隔符是（“{”），我已经更改了代码如下：'Text to Columns - 逗号分隔 txtBook.Sheets(1).Columns(1).TextToColumns Destination:=txtBook.Sheets(1).Cells(1, 1), DataType:=xlDelimited, _ Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:="{"但是出现了一些错误，请指导。 - vinmer

加号 Other:=("{") 应该改为 Other:=True，然后 OtherChar:="("{")"。 - Dawid SA Tokyo

别忘了在 OtherChar:="{" 之前加逗号！ - Dawid SA Tokyo