About Us

Steve

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?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need to export to a CSV but my file has more than 65536 rows	laredotornado	Excel Discussion (Misc queries)	6	January 30th 08 05:03 AM
import tekst 65536 rows	Reniek	Excel Programming	3	May 22nd 05 10:47 PM
Importing Text File with more than 65536 rows	Allan	Excel Programming	4	May 16th 05 01:34 AM
How can I open a large Excel file with more than 65536 rows?	Ted	Excel Discussion (Misc queries)	1	January 13th 05 07:47 PM
export a text file which more than 65536 rows into excel	Navin	Excel Programming	2	November 19th 03 10:55 PM

Menu

About Us