Import big text file into multiple sheets

**Josh_123456** · 06-14-2007, 04:34 PM

I am trying to import a text file into excel.

Right before the import takes place, I get a dialog box that states:

the text file contains more data than will fit on a single worksheet.

To continue and import as much of the data as will fit, click ok. You can then import the rest of the data by repeating the import operation on another worksheet and using the text import wizard to exclude data already imported.

Is there anyway to code this with doing this automatically into the next sheet?

thanks for any help

Josh

**royUK** · 06-15-2007, 04:09 AM

Here's some code

Option Explicit

Sub LargeFileImport()
    Dim ResultStr As String
    Dim FileName As String
    Dim FileNum As Integer
    Dim Counter As Double
    'Ask User for File's Name
    FileName = Application.GetOpenFilename
    'Check for no entry
    If FileName = "" Then End
    'Get Next Available File Handle Number
    FileNum = FreeFile()
    'Open Text File For Input
    Open FileName For Input As #FileNum
    'Turn Screen Updating Off
    Application.ScreenUpdating = False
    'Create A New WorkBook With One Worksheet In It
    Workbooks.Add Template:=xlWorksheet
    'Set The Counter to 1
    Counter = 1
    'Loop Until the End Of File Is Reached
    Do While Seek(FileNum) <= LOF(FileNum)
        'Display Importing Row Number On Status Bar
        Application.StatusBar = "Importing Row " & Counter & " of text file " _
                                & FileName
        'Store One Line Of Text From File To Variable
        Line Input #FileNum, ResultStr
        'Store Variable Data Into Active Cell
        If Left(ResultStr, 1) = "=" Then
            ActiveCell.Value = "'" & ResultStr
        Else
            ActiveCell.Value = ResultStr
        End If
        If ActiveCell.Row = 65536 Then
            'If On The Last Row Then Add A New Sheet
            ActiveWorkbook.Sheets.Add
        Else
            'If Not The Last Row Then Go One Cell Down
            ActiveCell.Offset(1, 0).Select
        End If
        'Increment the Counter By 1
        Counter = Counter + 1
        'Start Again At Top Of 'Do While' Statement
    Loop
    'Close The Open Text File
    Close
    'Remove Message From Status Bar
    Application.StatusBar = False
End Sub

**Josh_123456** · 06-21-2007, 01:17 PM

Thank you kindly for the help!

Josh

**linspalmer** · 10-08-2015, 04:54 PM

So I am a total novice and I think the code that you have posted will help me but what steps do I take before entering the code?

Again, I am a basic user.

Thank you!

**scottiex** · 10-08-2015, 08:14 PM

Here are some instructions

http://www.rondebruin.nl/win/code.htm

you will want to insert a module and paste it in there.

then alt F8 brings up the menu you can use to see the macro you have inserted.

**protonLeah** · 10-08-2015, 08:17 PM

linspalmer,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.