利用 Scripting.FileSystemObject 列舉資料夾內所有 .txt 檔案,逐一以 Workbooks.OpenText 開啟(自動依分隔符號分欄),另存為同名 .xls 檔案並關閉。
Dim Fso As Object, Fl As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
For Each Fl In Fso.GetFolder(ThisWorkbook.Path & "\").Files
If Fl.Name <> ThisWorkbook.Name Then ' 排除自身巨集檔
Workbooks.OpenText Fl.Path ' 開啟文字檔(自動啟動文字嚮導預設值)
With ActiveWorkbook
.SaveAs ThisWorkbook.Path & "\" & Fl.Name & ".xls" ' 另存為 .xls
.Close False ' 不儲存變更(已另存)
End With
End If
Next Fl
CreateObject("Scripting.FileSystemObject"):晚期繫結,無需加入參考,部署方便。Fso.GetFolder(ThisWorkbook.Path & "\").Files:取得巨集所在活頁簿資料夾下所有檔案集合。Workbooks.OpenText Fl.Path:以預設分隔符號(Tab/逗號/固定寬度)開啟文字檔。若格式特殊,可加參數指定 DataType、TextQualifier、ConsecutiveDelimiter 等。.SaveAs ... .xls:儲存為 Excel 97-2003 格式。若需 .xlsx,改用 xlOpenXMLWorkbook (51)。.Close False:關閉時不儲存(因已另存新檔),避免提示「要儲存變更嗎」。Sub BatchTxtToXlsx()
Dim Fso As Object, Fl As Object, wb As Workbook
Set Fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
For Each Fl In Fso.GetFolder(ThisWorkbook.Path & "\").Files
If LCase(Fso.GetExtensionName(Fl.Name)) = "txt" Then
Set wb = Workbooks.OpenText(Fl.Path, DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False)
On Error Resume Next
wb.SaveAs Replace(Fl.Path, ".txt", ".xlsx"), xlOpenXMLWorkbook
wb.Close False
On Error GoTo 0
End If
Next
MsgBox "批次轉檔完成"
End Sub
.txt(不區分大小寫)。OpenText 參數:Tab/逗號分隔、雙引號文字限定符。xlOpenXMLWorkbook = 51)支援百萬列。