首先,VBE工程窗口中,点击”工具“,”引用“,找到"microsoft scripting runtime"这一条,打上勾,确定
运行以下代码
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 Set myTxt = Nothing Set fso = Nothing Application.ScreenUpdating = True End Sub
如果一个工作表装不了,会自动新建表,装不了,再添加新表,直到导完为止,
不过有个问题,如果你电脑内存不是很大,而导出来有十几个表的话,可能会提示“内存资源不足",这种情况下你只能把文本文件分成一两个来试
发表评论