如何將超過65536筆以上的資料放入活頁簿(已解決^^) - Excel程式區

[發問] 如何將超過65536筆以上的資料放入活頁簿(已解決^^)

　頭銜: 街友一族

論壇貢獻勳章

1^# 跳轉到 »

發表於 2011-5-27 14:07 | 顯示全部帖子

Sub yy()
'引用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
End Sub

複製代碼

沒文件可測試，你自己測試一下吧

靜思自在 : 做好事不能少我一人，做壞事不能多我一人。