想要达到的目的:批量将txt转换成对应的Excel格式(一个txt文件对应一个Excel文件)。
1.利用现有工具(适用于数据量少的情况)
可以在线直接转换:https://anyconv.com/txt-to-xlsx-converter/
亲测是可以使用的,但是要一个个下载。
2.利用VBA
Sub txt2excel()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim txt, fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
If .Show = -1 Then
For Each txt In .SelectedItems
Workbooks.OpenText Filename:=txt, Origin:=936, DataType:=xlDelimited, Semicolon:=False, Comma:=True, Space:=False, Other:=False, Tab:=True
Range("a:u").NumberFormatLocal = "@"
ActiveWorkbook.SaveAs Filename:=Mid(txt, 1, Len(txt) - 4) & ".xls", FileFormat:=xlExcel8
'ActiveWorkbook.SaveAs Filename:=Mid(txt, 1, Len(txt) - 4), FileFormat:=xlExcel8
ActiveWorkbook.Close True
Next
Else
Exit Sub
End If
End With
End Sub