this code will put data on multiple sheets

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")

RowCount = 1
If FName <> "" Then
     'open files
     Set fread = fsread.GetFile(FName)
     Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
     
     Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))

     Do While tsread.atendofstream = False
     
        InputLine = tsread.ReadLine

        'extract comma seperated data
        ColumnCount = 1
        Do While InputLine <> ""
           DelimiterPosition = InStr(InputLine, Delimiter)
           If DelimiterPosition > 0 Then
              Data = Trim(Left(InputLine, DelimiterPosition - 1))
              InputLine = Mid(InputLine, DelimiterPosition + 1)
           Else
              Data = Trim(InputLine)
              InputLine = ""
           End If
           
           newsht.Cells(RowCount, ColumnCount) = Data
           ColumnCount = ColumnCount + 1
        Loop
        RowCount = RowCount + 1
        If RowCount = Rows.Count Then
           RowCount = 1
           Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
        End If
     Loop
     tsread.Close
End If
End Sub

Hi Chuck
In Excel 2007 Worksheet size 1,048,576 rows by 16,384 columns but you need
the memory to use it.
HTH
Cimjet

While it is technically feasable to import 600k records into XL it is not
very practical. You will bog down the system if you want to do any
calculations. A better solution might be to use a pivot table to analyze your
data. You can hook directly to the data file via MS Query and import the
records directly into the pivot cache. The pivot cache is not bound by 65,536
and the performance of calculations is remarkably good. Just something to
consider. If you need some help just ask...

I am also having difficulty with this. I'm trying to import a total of about
100,000 records from three tab-delimited files, each under 41,000 records. It
hits 65,536 records and shuts me down, despite the over one million row spec.
for Excel 2007.

I have also tried importing into three separate spreadsheets and
copy/pasting from them into a single spreadsheet, and get a copy and paste
area are not the same size and shape error, though when I select a smaller
number of records the same way (selecting whole rows, not individual cells),
it will perform the copy/paste fine. I've tried copy/pasting by selecting
cells, not rows, with the same results.

The spreadsheets are saved in the new Excel format, not compatibility mode.
I'm running Excel 2007 under Windows Vista, both up-to-date on patches, 1GB
RAM, plenty of disc space and a dual-core processor.

Any help would be greatly appreciated.