Thanks JMB, That really hit the spot

Steve

"JMB" wrote:

If you search this newsgroup, you will find links to Microsofts site where
they have posted a macro to do this, but here is a modified version of the
same thing. I split the data as it comes into the workbook (using %% as
the delimiter-change as needed) instead of using TextToColumns at the end as
MS's macro does. For me, it sped things up 66%. Also, this one puts 50000
lines to a sheet -change as needed.

Option Explicit

Sub Import()
Const lngLastRow As Long = 50000
Const strDelimiter As String = "%%"
Dim objDestWkBk As Workbook
Dim objDestWkSht As Worksheet
Dim varResult As Variant
Dim varStartTime As Variant
Dim varEndTime As Variant
Dim dblCounter As Double
Dim lngFNumber As Long
Dim lngCounter As Long
Dim i As Long
Dim strResult As String
Dim strFName As String

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Initialize variables
strFName = CStr(Application.GetOpenFilename)
If strFName = "" Or strFName = "False" Then End
lngFNumber = FreeFile()
dblCounter = 1
lngCounter = 1

'Open File
Open strFName For Input As #lngFNumber
varStartTime = Time

'Create new workbook
Set objDestWkBk = Workbooks.Add(template:=xlWorksheet)
Set objDestWkSht = objDestWkBk.Worksheets(1)

'Import the File
Do While Seek(lngFNumber) <= LOF(lngFNumber)
Application.StatusBar = "Importing Row " & _
Format(dblCounter, "#,###") & ": " & _
Format(Seek(lngFNumber), "#,###") & " / " & _
Format(LOF(lngFNumber), "#,###") & " bytes"
Line Input #lngFNumber, strResult
If Left(strResult, 1) = "=" Then _
strResult = "'" & strResult
varResult = Split(strResult, strDelimiter, -1, vbTextCompare)

For i = LBound(varResult) To UBound(varResult)
objDestWkSht.Cells(lngCounter, _
i + 1).Value = varResult(i)
Next i

'Increment counter variables
dblCounter = dblCounter + 1
If lngCounter = lngLastRow Then
lngCounter = 1
With objDestWkBk
Set objDestWkSht = .Worksheets.Add
objDestWkSht.Move after:=.Sheets(.Sheets.Count)
End With
Else: lngCounter = lngCounter + 1
End If
Loop

CleanUp:
Close
Application.StatusBar = False
Application.ScreenUpdating = True
If Err.Number < 0 Then
MsgBox "Error " & Err.Number & ": " & Err.Description
Else
varEndTime = Time
MsgBox "Start Time: " & varStartTime & Chr(10) & "End Time: " & varEndTime
End If
Exit Sub

End Sub

"Steve" wrote:

I need to import a csv file that has more than 65536 rows of data and 20
columns, is there a way of setting up a vba macro so that once the 65536 has
been reached, the continuation moves onto the next sheet?