200万txt数据导入excel代码

Sub ADO法() '假设文本文件是用逗号分隔的标准格式，文件名：文本文件.txt
Dim cnn As Object, rs As Object, SQL$, arr$(), i&, m&, sh As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sh In Worksheets
If https://www.wendangku.net/doc/4512455169.html, <> https://www.wendangku.net/doc/4512455169.html, Then sh.Delete
Next
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='text;HDR=Yes;FMT=Delimited';Data Source=" & ThisWorkbook.Path
SQL = "SELECT * FROM 文本文件.txt"
Set rs = cnn.Execute(SQL)
ReDim arr(0 To rs.Fields.Count - 1)
For i = 0 To rs.Fields.Count - 1
arr(i) = rs.Fields(i).Name
Next
While Not rs.EOF
m = m + 1
Worksheets.Add(After:=Worksheets(m)).Name = "第" & m & "页"
ActiveSheet.Range("a1").Resize(, rs.Fields.Count) = arr
ActiveSheet.Range("a2").CopyFromRecordset rs, Rows.Count - 1
Wend
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Application.ScreenUpdating = True
End Sub