[SIZE=2][COLOR=#0000ff]Sub[/COLOR] ImportLargeFile()
[/SIZE][SIZE=2][COLOR=#006600]' Imports text file into Excel workbook using ADO.
' If the number of records exceeds 65536 then it splits it over more than one sheet.
[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] strFilePath [COLOR=#0000ff]As String[/COLOR], strFilename [COLOR=#0000ff]As String[/COLOR], vFullPath [/SIZE][SIZE=2][COLOR=#0000ff]As Variant
[/COLOR][COLOR=#0000ff]Dim[/COLOR] lngCounter [/SIZE][SIZE=2][COLOR=#0000ff]As Long
[/COLOR][COLOR=#0000ff]Dim[/COLOR] oConn [COLOR=#0000ff]As Object[/COLOR], oRS [COLOR=#0000ff]As Object[/COLOR], oFSObj [/SIZE][SIZE=2][COLOR=#0000ff]As Object
[/COLOR]
[/SIZE][SIZE=2][COLOR=#006600]' Get a text file name
[/COLOR]vFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
[COLOR=#0000ff]If[/COLOR] vFullPath = [COLOR=#0000ff]False Then Exit Sub[/COLOR] [COLOR=#006600]'User pressed Cancel on the open file dialog[/COLOR]
Application.ScreenUpdating = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR]
[/SIZE][SIZE=2][COLOR=#006600]' This gives us a full path name e.g. C:＼folder＼file.txt
' We need to split this into path and file name
[/COLOR][COLOR=#0000ff]Set[/COLOR] oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(vFullPath).Name
[COLOR=#006600]' Open an ADO connection to the folder specified[/COLOR]
[COLOR=#0000ff]Set[/COLOR] oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
[COLOR=#0000ff]Set[/COLOR] oRS = CreateObject("ADODB.RECORDSET")
[/SIZE][SIZE=2][COLOR=#006600]' Now actually open the text file and import into Excel
[/COLOR]oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
[COLOR=#0000ff]While Not[/COLOR] oRS.EOF
Sheets.Add
ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
[/SIZE][SIZE=2][COLOR=#0000ff]Wend
[/COLOR]oRS.Close
oConn.Close
Application.ScreenUpdating = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][COLOR=#0000ff]End Sub[/COLOR]
[/SIZE]