+ Reply to Thread
Results 1 to 4 of 4

"Start import at row"-limit when importing text to worksheet

  1. #1
    Mikkel Randorf Hegnhøj
    Guest

    "Start import at row"-limit when importing text to worksheet

    Hi there!

    I'm using Excel 2003 to do analyse large textfiles, to do this I regularly
    imports text to Excel, but unfortunately I've realized that there are some
    limitations when importing text files.

    I have a text file which has more that 65536 lines (which is the maximum row
    numbers in one Excel worksheet), when I import this in Excel I first fill one
    worksheet.
    Now - the great thing is that I should be able to import the rest of the
    textfile on another worksheet, so I start the "Text Impor Wizard", but when
    using the feature "Start import at row" - the maximum row number I can type
    is 32767... :-(

    Of course I can start deleting lines from my textfile - but...

    Please - Would you plan on changing this, so that this scenario would be
    possible to solve?

    Best Regards!
    --
    Mikkel Randorff Hegnhøj

  2. #2
    Dave Peterson
    Guest

    Re: "Start import at row"-limit when importing text to worksheet

    You may want to send your suggestion to MS. These newsgroups are usually just
    people helping people. MS doesn't monitor every post (as far as I know...).

    email your request to:
    [email protected]



    Mikkel Randorf Hegnhøj wrote:
    >
    > Hi there!
    >
    > I'm using Excel 2003 to do analyse large textfiles, to do this I regularly
    > imports text to Excel, but unfortunately I've realized that there are some
    > limitations when importing text files.
    >
    > I have a text file which has more that 65536 lines (which is the maximum row
    > numbers in one Excel worksheet), when I import this in Excel I first fill one
    > worksheet.
    > Now - the great thing is that I should be able to import the rest of the
    > textfile on another worksheet, so I start the "Text Impor Wizard", but when
    > using the feature "Start import at row" - the maximum row number I can type
    > is 32767... :-(
    >
    > Of course I can start deleting lines from my textfile - but...
    >
    > Please - Would you plan on changing this, so that this scenario would be
    > possible to solve?
    >
    > Best Regards!
    > --
    > Mikkel Randorff Hegnhøj


    --

    Dave Peterson

  3. #3
    Guest

    Re: "Start import at row"-limit when importing text to worksheet

    I'm not good at English, so I don't know whether this is what you expected or not.
    But I think this could be done by using vba, if I've not misunderstood your
    writing.
    in my case(Excel 2002), i could import textfile with about 120000 lines by using
    macros under though it took some time to import all data.

    Sub importtext()
    Dim fs, f, fss
    Dim startrow As Long, endrow As Long, inrow As Long, count As Long
    Dim retstring As String

    'the row number where data is first entered(this could be changed if you
    like )
    startrow = 1

    Selection.End(xlDown).Select
    'the row number where data is last entered(this could be changed if you like)
    endrow = Selection.Row
    Selection.End(xlUp).Select

    'the line number in a textfile where data start to be imported
    '(this could be changed if you like)
    startline = 1

    Set fs = CreateObject("Scripting.FileSystemObject")

    MsgBox "Select textfile to import"

    Set fss = Application.FileDialog(msoFileDialogFilePicker)

    If fss.Show = True Then

    Else
    Exit Sub
    End If

    On Error Resume Next

    Set f = fs.OpenTextFile(fss.SelectedItems.Item(1), 1, False, -2)

    If IsEmpty(f) Then
    MsgBox "Can't open " & fss.SelectedItems.Item(1)
    Exit Sub
    End If

    On Error GoTo 0

    inrow = startrow
    count = 1

    Do While f.AtEndOfStream <> True
    retstring = f.ReadLine

    If count >= startline Then
    'the cell where data is entered
    Cells(inrow, "a").Value = retstring
    Application.StatusBar = "processing line number = " & count
    inrow = inrow + 1
    End If

    count = count + 1

    If inrow > endrow Then
    Worksheets.Add after:=ActiveSheet
    inrow = startrow
    End If
    Loop

    End Sub


    "Mikkel Randorf Hegnhøj" <[email protected]> wrote in message
    news:[email protected]...
    > Hi there!
    >
    > I'm using Excel 2003 to do analyse large textfiles, to do this I regularly
    > imports text to Excel, but unfortunately I've realized that there are some
    > limitations when importing text files.
    >
    > I have a text file which has more that 65536 lines (which is the maximum row
    > numbers in one Excel worksheet), when I import this in Excel I first fill one
    > worksheet.
    > Now - the great thing is that I should be able to import the rest of the
    > textfile on another worksheet, so I start the "Text Impor Wizard", but when
    > using the feature "Start import at row" - the maximum row number I can type
    > is 32767... :-(
    >
    > Of course I can start deleting lines from my textfile - but...
    >
    > Please - Would you plan on changing this, so that this scenario would be
    > possible to solve?
    >
    > Best Regards!
    > --
    > Mikkel Randorff Hegnhøj


    --
    keizi kounoike


  4. #4
    Mikkel Randorf Hegnhøj
    Guest

    Re: "Start import at row"-limit when importing text to worksheet

    Great thanks!

    Although I hopefully can manage to get this to work, lot's of people don't
    know VBA, so I guess they would prefer the slight change in the GUI.

    :-)

    But thanks for the solution anyway!

    -Mikkel

    "[email protected]" wrote:

    > I'm not good at English, so I don't know whether this is what you expected or not.
    > But I think this could be done by using vba, if I've not misunderstood your
    > writing.
    > in my case(Excel 2002), i could import textfile with about 120000 lines by using
    > macros under though it took some time to import all data.
    >
    > Sub importtext()
    > Dim fs, f, fss
    > Dim startrow As Long, endrow As Long, inrow As Long, count As Long
    > Dim retstring As String
    >
    > 'the row number where data is first entered(this could be changed if you
    > like )
    > startrow = 1
    >
    > Selection.End(xlDown).Select
    > 'the row number where data is last entered(this could be changed if you like)
    > endrow = Selection.Row
    > Selection.End(xlUp).Select
    >
    > 'the line number in a textfile where data start to be imported
    > '(this could be changed if you like)
    > startline = 1
    >
    > Set fs = CreateObject("Scripting.FileSystemObject")
    >
    > MsgBox "Select textfile to import"
    >
    > Set fss = Application.FileDialog(msoFileDialogFilePicker)
    >
    > If fss.Show = True Then
    >
    > Else
    > Exit Sub
    > End If
    >
    > On Error Resume Next
    >
    > Set f = fs.OpenTextFile(fss.SelectedItems.Item(1), 1, False, -2)
    >
    > If IsEmpty(f) Then
    > MsgBox "Can't open " & fss.SelectedItems.Item(1)
    > Exit Sub
    > End If
    >
    > On Error GoTo 0
    >
    > inrow = startrow
    > count = 1
    >
    > Do While f.AtEndOfStream <> True
    > retstring = f.ReadLine
    >
    > If count >= startline Then
    > 'the cell where data is entered
    > Cells(inrow, "a").Value = retstring
    > Application.StatusBar = "processing line number = " & count
    > inrow = inrow + 1
    > End If
    >
    > count = count + 1
    >
    > If inrow > endrow Then
    > Worksheets.Add after:=ActiveSheet
    > inrow = startrow
    > End If
    > Loop
    >
    > End Sub
    >
    >
    > "Mikkel Randorf Hegnhøj" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi there!
    > >
    > > I'm using Excel 2003 to do analyse large textfiles, to do this I regularly
    > > imports text to Excel, but unfortunately I've realized that there are some
    > > limitations when importing text files.
    > >
    > > I have a text file which has more that 65536 lines (which is the maximum row
    > > numbers in one Excel worksheet), when I import this in Excel I first fill one
    > > worksheet.
    > > Now - the great thing is that I should be able to import the rest of the
    > > textfile on another worksheet, so I start the "Text Impor Wizard", but when
    > > using the feature "Start import at row" - the maximum row number I can type
    > > is 32767... :-(
    > >
    > > Of course I can start deleting lines from my textfile - but...
    > >
    > > Please - Would you plan on changing this, so that this scenario would be
    > > possible to solve?
    > >
    > > Best Regards!
    > > --
    > > Mikkel Randorff Hegnhøj

    >
    > --
    > keizi kounoike
    >
    >


+ 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