我碰到的问题有点不同,我的文本文件是以空格分隔的,所以我只能先导入,然后再分列,导入的时候,我是借鉴蓝桥玄霜版主的代码,如果一个工作表装不了,会自动新建工作表,直到导完为止,到目前为止,个人觉得还不错,
Sub 导入文本文件()
'导入文本文件
'引用Microsoft Scripting Runtime
Application.ScreenUpdating = False
Dim fso As Scripting.FileSystemObject
Dim myTxt As Scripting.TextStream
Dim myfile As String, myname$
Dim i As Long, j%
ActiveSheet.Cells.Clear
myfile = Application.GetOpenFilename("text files (*.txt),*.txt", , "请选择文本文件") '指定文件名
Set fso = New Scripting.FileSystemObject
Set myTxt = fso.OpenTextFile(Filename:=myfile, IOMode:=ForReading)
With myTxt
i = 1: j = 1: myname = "sheet" & j
Do Until .AtEndOfStream
Sheets(myname).Cells(i, 1) = .ReadLine
i = i + 1
If i = 65530 Then
j = j + 1
If j > 3 Then
Sheets.Add after:=Sheets(myname)
End If
myname = "sheet" & j
i = 1
End If
Loop
.Close
End With
红色部分可以改为65537,这样就可以把65536行全部放满数据,我自己留了最后几行以便编辑,否则会导致溢出错误,