+ Reply to Thread
Results 1 to 3 of 3

OpenText Method

  1. #1
    JH
    Guest

    OpenText Method

    Hi, I have a text file with many pages. It will exceed the maximum number of
    rows in one Excel worksheet. Is it possible to use the OpenText method to
    import this file to more than one worksheet? If not, what is the best way to
    import this file?
    Thanks!

  2. #2
    Eric White
    Guest

    RE: OpenText Method

    Check out:

    http://support.microsoft.com/default...b;en-us;120596

    I know I have a book co-authored by Mr. Excel (www.mrexcel.com) that also
    addresses this problem, but I don't have it handy.

    If you have that much data, it might be easier to import it into Access and
    then pull the data (65536 row at a time) into separate worksheets. (That may
    be how Mr. Excel talks about doing it.)


    "JH" wrote:

    > Hi, I have a text file with many pages. It will exceed the maximum number of
    > rows in one Excel worksheet. Is it possible to use the OpenText method to
    > import this file to more than one worksheet? If not, what is the best way to
    > import this file?
    > Thanks!


  3. #3
    crazybass2
    Guest

    RE: OpenText Method

    JH,

    You can also modify the code given in Eric's link to use only one sheet. If
    your text file is less than 16,777,216 lines you can use the following code
    that will import lines up to 65536 into the first column and then lines
    65537-131072 into the second column and so on until the end of file. This is
    usefull if you don't want a bunch of sheets created. I've never reached the
    limit before and I've processed files up to 1GB that use roughly 32 columns.
    Code follows:

    Sub Importfile()
    Dim FileName As String
    Dim ResultStr As String
    Dim FileNum As Integer
    Dim Counter As Double
    FileName = Application.GetOpenFilename
    Application.ScreenUpdating = False
    If FileName = "False" Then End
    FileNum = FreeFile()
    Open FileName For Input As #FileNum
    Sheet1.Cells(65000, 1).Select
    Counter = 1
    ColCounter = 1
    Do While Seek(FileNum) <= LOF(FileNum)
    If EOF(FileNum) Then End
    Application.StatusBar = "Reading Row " & Counter & " of text file " &
    FileName
    Line Input #FileNum, ResultStr
    ActiveCell.Value = ResultStr
    If ActiveCell.Row = 65536 Then
    ColCounter = ColCounter + 1
    Sheet1.Cells(1, ColCounter).Select
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Counter = Counter + 1
    Loop
    Close
    Application.ScreenUpdating = True
    Application.StatusBar = False
    End Sub


    Hope this helps,
    Mike

    "JH" wrote:

    > Hi, I have a text file with many pages. It will exceed the maximum number of
    > rows in one Excel worksheet. Is it possible to use the OpenText method to
    > import this file to more than one worksheet? If not, what is the best way to
    > import this file?
    > Thanks!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1