experts : how to mapipulate large data in excel

[jkpd2000]

i have a text file which has more than 100k lines . I want to load that file into excel automatically.

is there a way?

[Ian Mac]

On 2002-05-23 15:44, jkpd2000 wrote:
i have a text file which has more than 100k lines . I want to load that file into excel automatically.

is there a way?

There is.

Why do you need to LOAD it into Excel?

Would Access not be better? That's what Databases are made for.

[Anne Troy]

Excel is restricted to 65,536 rows.

Perhaps you need Access.

[GlennG]

You should split the text file instead; then import the data...


Good Luck

[Jay Petrulis]

Try the following. You will have to use Text-to-Columns to parse the data after inporting.

Sub FileImport()

    Dim ResultStr As String
    Dim strFileName As String
    Dim FileNum As Integer
    Dim Counter As Long

    strFileName = Application.GetOpenFilename(Title:="Import text file")
    If strFileName = "" Then End
    FileNum = FreeFile()
    Open strFileName For Input As #FileNum
    Application.ScreenUpdating = False
    Workbooks.Add template:=xlWorksheet
    Counter = 1
    Do While Seek(FileNum) <= LOF(FileNum)
        Line Input #FileNum, ResultStr
        If Left(ResultStr, 1) = "=" Then
            ActiveCell.Value = "'" & ResultStr
        Else
            ActiveCell.Value = ResultStr
        End If

        If ActiveCell.Row = 65535 Then
            ActiveWorkbook.Sheets.Add
        Else
            ActiveCell.Offset(1, 0).Select
        End If
        Counter = Counter + 1
    Loop
    Close
End Sub

Bye,
Jay