More than 65536 entries...

When trying to import a text file that has more than 65536 entries, I get a
message saying that there is more data than will fit on a single worksheet &
to get it all you can repeat the import on another worksheet & tell the
import wizard to exclude data already imported. I've tried Excel 2000 &
Excel 2003 & cannot find this option anywhere.
Can someone tell me where it can be found, or is there a way to "link"
worksheets so it can automatically flow to the next sheet?
Thanks-


0
mikey117 (63)
4/26/2004 3:02:57 AM
excel 39879 articles. 2 followers. Follow

9 Replies
671 Views

Similar Articles

[PageSpeed] 28

Hi Mike

The option is at step 1 of the Text Import  Wizard, "Start import at
row:"



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
0
dave2624 (251)
4/26/2004 3:39:17 AM
I would think you'd put the number 65537, right?
I try that & I get the message "Integer not valid".
It won't let me put a number larger than 32767...

"Dave Hawley" <dave@ozgrid.com> wrote in message
news:408c8465$0$199$75868355@news.frii.net...
> Hi Mike
>
> The option is at step 1 of the Text Import  Wizard, "Start import at
> row:"
>
>
>
> ** Posted via: http://www.ozgrid.com
> Excel Templates, Training, Add-ins & Business Software Galore!
> Free Excel Forum http://www.ozgrid.com/forum ***


0
mikey117 (63)
4/26/2004 12:59:54 PM
Mike,

Use the macro below. It will split the large file onto extra sheets as
needed.

HTH,
Bernie
MS Excel MVP

Sub LargeFileImport()
'Dimension Variables
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
'For xl95 change 65536 to 16384
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


"Mike" <mikey117@hotmail.com> wrote in message
news:uFOY6rzKEHA.3428@TK2MSFTNGP09.phx.gbl...
> When trying to import a text file that has more than 65536 entries, I get
a
> message saying that there is more data than will fit on a single worksheet
&
> to get it all you can repeat the import on another worksheet & tell the
> import wizard to exclude data already imported. I've tried Excel 2000 &
> Excel 2003 & cannot find this option anywhere.
> Can someone tell me where it can be found, or is there a way to "link"
> worksheets so it can automatically flow to the next sheet?
> Thanks-
>
>


0
Bernie
4/26/2004 1:46:47 PM
Thanks! That worked great, other than one small problem. It put everything
into one culumn, separated by boxes. When I just imported that data
originally, it split things into separate columns. Can I get the macro to
act the same way?
Thanks, again-

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%23Pz1sT5KEHA.2704@TK2MSFTNGP10.phx.gbl...
> Mike,
>
> Use the macro below. It will split the large file onto extra sheets as
> needed.
>
> HTH,
> Bernie
> MS Excel MVP
>
> Sub LargeFileImport()
> 'Dimension Variables
> 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
> 'For xl95 change 65536 to 16384
> 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
>
>
> "Mike" <mikey117@hotmail.com> wrote in message
> news:uFOY6rzKEHA.3428@TK2MSFTNGP09.phx.gbl...
> > When trying to import a text file that has more than 65536 entries, I
get
> a
> > message saying that there is more data than will fit on a single
worksheet
> &
> > to get it all you can repeat the import on another worksheet & tell the
> > import wizard to exclude data already imported. I've tried Excel 2000 &
> > Excel 2003 & cannot find this option anywhere.
> > Can someone tell me where it can be found, or is there a way to "link"
> > worksheets so it can automatically flow to the next sheet?
> > Thanks-
> >
> >
>
>


0
mikey117 (63)
5/2/2004 3:45:16 PM
Mike,

Try using Data | Test to coloumns... on your cells after the import.  Here's
the code to do it:

    Range("A:A").TextToColumns _
        Destination:=Range("A:A"), _
        DataType:=xlDelimited, _
        Other:=True, _
        OtherChar:="" & Chr(10) & ""

Of course, your "boxes"may be a different character than 10 - just change to
match. Insert that code just before the line:

ActiveWorkbook.Sheets.Add

HTH,
Bernie
MS Excel MVP

"Mike" <mikey117@hotmail.com> wrote in message
news:O1YS4xFMEHA.3664@TK2MSFTNGP10.phx.gbl...
> Thanks! That worked great, other than one small problem. It put everything
> into one culumn, separated by boxes. When I just imported that data
> originally, it split things into separate columns. Can I get the macro to
> act the same way?
> Thanks, again-
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:%23Pz1sT5KEHA.2704@TK2MSFTNGP10.phx.gbl...
> > Mike,
> >
> > Use the macro below. It will split the large file onto extra sheets as
> > needed.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > Sub LargeFileImport()
> > 'Dimension Variables
> > 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
> > 'For xl95 change 65536 to 16384
> > 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
> >
> >
> > "Mike" <mikey117@hotmail.com> wrote in message
> > news:uFOY6rzKEHA.3428@TK2MSFTNGP09.phx.gbl...
> > > When trying to import a text file that has more than 65536 entries, I
> get
> > a
> > > message saying that there is more data than will fit on a single
> worksheet
> > &
> > > to get it all you can repeat the import on another worksheet & tell
the
> > > import wizard to exclude data already imported. I've tried Excel 2000
&
> > > Excel 2003 & cannot find this option anywhere.
> > > Can someone tell me where it can be found, or is there a way to "link"
> > > worksheets so it can automatically flow to the next sheet?
> > > Thanks-
> > >
> > >
> >
> >
>
>


0
Bernie
5/3/2004 12:25:25 PM
I'm sure the "boxes" are just some kind of placeholder (?) for a tab
possibly? I've tried inserting a tab or even copying & pasting the box from
the Excel doc in place of the 10 in your example & when I try to run it, I
get an "Argument not optional" error & the Chr is highlighted, but nothing
shows in the parenthesis after the Chr. I'm sure you've figured out by now
I'm pretty ignorant as far as Macros go & I certainly appreciate your help &
patience...


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:Ov6i3mQMEHA.3472@TK2MSFTNGP09.phx.gbl...
> Mike,
>
> Try using Data | Test to coloumns... on your cells after the import.
Here's
> the code to do it:
>
>     Range("A:A").TextToColumns _
>         Destination:=Range("A:A"), _
>         DataType:=xlDelimited, _
>         Other:=True, _
>         OtherChar:="" & Chr(10) & ""
>
> Of course, your "boxes"may be a different character than 10 - just change
to
> match. Insert that code just before the line:
>
> ActiveWorkbook.Sheets.Add
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Mike" <mikey117@hotmail.com> wrote in message
> news:O1YS4xFMEHA.3664@TK2MSFTNGP10.phx.gbl...
> > Thanks! That worked great, other than one small problem. It put
everything
> > into one culumn, separated by boxes. When I just imported that data
> > originally, it split things into separate columns. Can I get the macro
to
> > act the same way?
> > Thanks, again-
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:%23Pz1sT5KEHA.2704@TK2MSFTNGP10.phx.gbl...
> > > Mike,
> > >
> > > Use the macro below. It will split the large file onto extra sheets as
> > > needed.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > Sub LargeFileImport()
> > > 'Dimension Variables
> > > 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
> > > 'For xl95 change 65536 to 16384
> > > 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
> > >
> > >
> > > "Mike" <mikey117@hotmail.com> wrote in message
> > > news:uFOY6rzKEHA.3428@TK2MSFTNGP09.phx.gbl...
> > > > When trying to import a text file that has more than 65536 entries,
I
> > get
> > > a
> > > > message saying that there is more data than will fit on a single
> > worksheet
> > > &
> > > > to get it all you can repeat the import on another worksheet & tell
> the
> > > > import wizard to exclude data already imported. I've tried Excel
2000
> &
> > > > Excel 2003 & cannot find this option anywhere.
> > > > Can someone tell me where it can be found, or is there a way to
"link"
> > > > worksheets so it can automatically flow to the next sheet?
> > > > Thanks-
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
mikey117 (63)
5/4/2004 4:41:04 PM
Mike,

Let's say that the box is the tenth character in cell A1. Use this formula
in another cell:

=CODE(MID(A1,10,1))

Whatever number that returns (let's just say 193) change your code to
reflect that number. So, from this

     Range("A:A").TextToColumns _
         Destination:=Range("A:A"), _
         DataType:=xlDelimited, _
         Other:=True, _
         OtherChar:="" & Chr(10) & ""

to this

    Range("A:A").TextToColumns _
         Destination:=Range("A:A"), _
         DataType:=xlDelimited, _
         Other:=True, _
         OtherChar:="" & Chr(193) & ""

> I'm pretty ignorant as far as Macros go

We all were at one time.  And Exel macros have a pretty steep learning
curve....

HTH,
Bernie
MS Excel MVP

"Mike" <mikey117@hotmail.com> wrote in message
news:#eo5YafMEHA.2064@TK2MSFTNGP12.phx.gbl...
> I'm sure the "boxes" are just some kind of placeholder (?) for a tab
> possibly? I've tried inserting a tab or even copying & pasting the box
from
> the Excel doc in place of the 10 in your example & when I try to run it, I
> get an "Argument not optional" error & the Chr is highlighted, but nothing
> shows in the parenthesis after the Chr. I'm sure you've figured out by now
> I'm pretty ignorant as far as Macros go & I certainly appreciate your help
&
> patience...
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:Ov6i3mQMEHA.3472@TK2MSFTNGP09.phx.gbl...
> > Mike,
> >
> > Try using Data | Test to coloumns... on your cells after the import.
> Here's
> > the code to do it:
> >
> >     Range("A:A").TextToColumns _
> >         Destination:=Range("A:A"), _
> >         DataType:=xlDelimited, _
> >         Other:=True, _
> >         OtherChar:="" & Chr(10) & ""
> >
> > Of course, your "boxes"may be a different character than 10 - just
change
> to
> > match. Insert that code just before the line:
> >
> > ActiveWorkbook.Sheets.Add
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "Mike" <mikey117@hotmail.com> wrote in message
> > news:O1YS4xFMEHA.3664@TK2MSFTNGP10.phx.gbl...
> > > Thanks! That worked great, other than one small problem. It put
> everything
> > > into one culumn, separated by boxes. When I just imported that data
> > > originally, it split things into separate columns. Can I get the macro
> to
> > > act the same way?
> > > Thanks, again-
> > >
> > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > news:%23Pz1sT5KEHA.2704@TK2MSFTNGP10.phx.gbl...
> > > > Mike,
> > > >
> > > > Use the macro below. It will split the large file onto extra sheets
as
> > > > needed.
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > > Sub LargeFileImport()
> > > > 'Dimension Variables
> > > > 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
> > > > 'For xl95 change 65536 to 16384
> > > > 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
> > > >
> > > >
> > > > "Mike" <mikey117@hotmail.com> wrote in message
> > > > news:uFOY6rzKEHA.3428@TK2MSFTNGP09.phx.gbl...
> > > > > When trying to import a text file that has more than 65536
entries,
> I
> > > get
> > > > a
> > > > > message saying that there is more data than will fit on a single
> > > worksheet
> > > > &
> > > > > to get it all you can repeat the import on another worksheet &
tell
> > the
> > > > > import wizard to exclude data already imported. I've tried Excel
> 2000
> > &
> > > > > Excel 2003 & cannot find this option anywhere.
> > > > > Can someone tell me where it can be found, or is there a way to
> "link"
> > > > > worksheets so it can automatically flow to the next sheet?
> > > > > Thanks-
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
Bernie
5/5/2004 12:30:00 PM
The position of the box isn't constant. Since this is a customer mailing
list & the first entry is the first name, or just an initial, it changes.

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%23EjcwypMEHA.936@TK2MSFTNGP11.phx.gbl...
> Mike,
>
> Let's say that the box is the tenth character in cell A1. Use this formula
> in another cell:
>
> =CODE(MID(A1,10,1))
>
> Whatever number that returns (let's just say 193) change your code to
> reflect that number. So, from this
>
>      Range("A:A").TextToColumns _
>          Destination:=Range("A:A"), _
>          DataType:=xlDelimited, _
>          Other:=True, _
>          OtherChar:="" & Chr(10) & ""
>
> to this
>
>     Range("A:A").TextToColumns _
>          Destination:=Range("A:A"), _
>          DataType:=xlDelimited, _
>          Other:=True, _
>          OtherChar:="" & Chr(193) & ""
>
> > I'm pretty ignorant as far as Macros go
>
> We all were at one time.  And Exel macros have a pretty steep learning
> curve....
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Mike" <mikey117@hotmail.com> wrote in message
> news:#eo5YafMEHA.2064@TK2MSFTNGP12.phx.gbl...
> > I'm sure the "boxes" are just some kind of placeholder (?) for a tab
> > possibly? I've tried inserting a tab or even copying & pasting the box
> from
> > the Excel doc in place of the 10 in your example & when I try to run it,
I
> > get an "Argument not optional" error & the Chr is highlighted, but
nothing
> > shows in the parenthesis after the Chr. I'm sure you've figured out by
now
> > I'm pretty ignorant as far as Macros go & I certainly appreciate your
help
> &
> > patience...
> >
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:Ov6i3mQMEHA.3472@TK2MSFTNGP09.phx.gbl...
> > > Mike,
> > >
> > > Try using Data | Test to coloumns... on your cells after the import.
> > Here's
> > > the code to do it:
> > >
> > >     Range("A:A").TextToColumns _
> > >         Destination:=Range("A:A"), _
> > >         DataType:=xlDelimited, _
> > >         Other:=True, _
> > >         OtherChar:="" & Chr(10) & ""
> > >
> > > Of course, your "boxes"may be a different character than 10 - just
> change
> > to
> > > match. Insert that code just before the line:
> > >
> > > ActiveWorkbook.Sheets.Add
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "Mike" <mikey117@hotmail.com> wrote in message
> > > news:O1YS4xFMEHA.3664@TK2MSFTNGP10.phx.gbl...
> > > > Thanks! That worked great, other than one small problem. It put
> > everything
> > > > into one culumn, separated by boxes. When I just imported that data
> > > > originally, it split things into separate columns. Can I get the
macro
> > to
> > > > act the same way?
> > > > Thanks, again-
> > > >
> > > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > > news:%23Pz1sT5KEHA.2704@TK2MSFTNGP10.phx.gbl...
> > > > > Mike,
> > > > >
> > > > > Use the macro below. It will split the large file onto extra
sheets
> as
> > > > > needed.
> > > > >
> > > > > HTH,
> > > > > Bernie
> > > > > MS Excel MVP
> > > > >
> > > > > Sub LargeFileImport()
> > > > > 'Dimension Variables
> > > > > 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
> > > > > 'For xl95 change 65536 to 16384
> > > > > 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
> > > > >
> > > > >
> > > > > "Mike" <mikey117@hotmail.com> wrote in message
> > > > > news:uFOY6rzKEHA.3428@TK2MSFTNGP09.phx.gbl...
> > > > > > When trying to import a text file that has more than 65536
> entries,
> > I
> > > > get
> > > > > a
> > > > > > message saying that there is more data than will fit on a single
> > > > worksheet
> > > > > &
> > > > > > to get it all you can repeat the import on another worksheet &
> tell
> > > the
> > > > > > import wizard to exclude data already imported. I've tried Excel
> > 2000
> > > &
> > > > > > Excel 2003 & cannot find this option anywhere.
> > > > > > Can someone tell me where it can be found, or is there a way to
> > "link"
> > > > > > worksheets so it can automatically flow to the next sheet?
> > > > > > Thanks-
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
mikey117 (63)
5/5/2004 4:11:07 PM
Mike,

The position of the box is irrelevant except for one cell, which you will
use to find out what ascii character the box actually is. That's all that
you need to do. When you change your macro to reflect that ascii character
value, the text to column part of the macro will take into account the
position of the character.

HTH,
Bernie
MS Excel MVP

"Mike" <mikey117@hotmail.com> wrote in message
news:O5kiSurMEHA.128@TK2MSFTNGP09.phx.gbl...
> The position of the box isn't constant. Since this is a customer mailing
> list & the first entry is the first name, or just an initial, it changes.
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:%23EjcwypMEHA.936@TK2MSFTNGP11.phx.gbl...
> > Mike,
> >
> > Let's say that the box is the tenth character in cell A1. Use this
formula
> > in another cell:
> >
> > =CODE(MID(A1,10,1))
> >
> > Whatever number that returns (let's just say 193) change your code to
> > reflect that number. So, from this
> >
> >      Range("A:A").TextToColumns _
> >          Destination:=Range("A:A"), _
> >          DataType:=xlDelimited, _
> >          Other:=True, _
> >          OtherChar:="" & Chr(10) & ""
> >
> > to this
> >
> >     Range("A:A").TextToColumns _
> >          Destination:=Range("A:A"), _
> >          DataType:=xlDelimited, _
> >          Other:=True, _
> >          OtherChar:="" & Chr(193) & ""
> >
> > > I'm pretty ignorant as far as Macros go
> >
> > We all were at one time.  And Exel macros have a pretty steep learning
> > curve....
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "Mike" <mikey117@hotmail.com> wrote in message
> > news:#eo5YafMEHA.2064@TK2MSFTNGP12.phx.gbl...
> > > I'm sure the "boxes" are just some kind of placeholder (?) for a tab
> > > possibly? I've tried inserting a tab or even copying & pasting the box
> > from
> > > the Excel doc in place of the 10 in your example & when I try to run
it,
> I
> > > get an "Argument not optional" error & the Chr is highlighted, but
> nothing
> > > shows in the parenthesis after the Chr. I'm sure you've figured out by
> now
> > > I'm pretty ignorant as far as Macros go & I certainly appreciate your
> help
> > &
> > > patience...
> > >
> > >
> > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > news:Ov6i3mQMEHA.3472@TK2MSFTNGP09.phx.gbl...
> > > > Mike,
> > > >
> > > > Try using Data | Test to coloumns... on your cells after the import.
> > > Here's
> > > > the code to do it:
> > > >
> > > >     Range("A:A").TextToColumns _
> > > >         Destination:=Range("A:A"), _
> > > >         DataType:=xlDelimited, _
> > > >         Other:=True, _
> > > >         OtherChar:="" & Chr(10) & ""
> > > >
> > > > Of course, your "boxes"may be a different character than 10 - just
> > change
> > > to
> > > > match. Insert that code just before the line:
> > > >
> > > > ActiveWorkbook.Sheets.Add
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > > "Mike" <mikey117@hotmail.com> wrote in message
> > > > news:O1YS4xFMEHA.3664@TK2MSFTNGP10.phx.gbl...
> > > > > Thanks! That worked great, other than one small problem. It put
> > > everything
> > > > > into one culumn, separated by boxes. When I just imported that
data
> > > > > originally, it split things into separate columns. Can I get the
> macro
> > > to
> > > > > act the same way?
> > > > > Thanks, again-
> > > > >
> > > > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > > > news:%23Pz1sT5KEHA.2704@TK2MSFTNGP10.phx.gbl...
> > > > > > Mike,
> > > > > >
> > > > > > Use the macro below. It will split the large file onto extra
> sheets
> > as
> > > > > > needed.
> > > > > >
> > > > > > HTH,
> > > > > > Bernie
> > > > > > MS Excel MVP
> > > > > >
> > > > > > Sub LargeFileImport()
> > > > > > 'Dimension Variables
> > > > > > 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
> > > > > > 'For xl95 change 65536 to 16384
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "Mike" <mikey117@hotmail.com> wrote in message
> > > > > > news:uFOY6rzKEHA.3428@TK2MSFTNGP09.phx.gbl...
> > > > > > > When trying to import a text file that has more than 65536
> > entries,
> > > I
> > > > > get
> > > > > > a
> > > > > > > message saying that there is more data than will fit on a
single
> > > > > worksheet
> > > > > > &
> > > > > > > to get it all you can repeat the import on another worksheet &
> > tell
> > > > the
> > > > > > > import wizard to exclude data already imported. I've tried
Excel
> > > 2000
> > > > &
> > > > > > > Excel 2003 & cannot find this option anywhere.
> > > > > > > Can someone tell me where it can be found, or is there a way
to
> > > "link"
> > > > > > > worksheets so it can automatically flow to the next sheet?
> > > > > > > Thanks-
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
Bernie
5/5/2004 4:20:11 PM
Reply:

Similar Artilces:

Removing Duplic Contact Entries
May have your suggestion for an application that will help rid my Outlook 2003 of duplicate Contact entries? I would be especially grateful for an application that that also identifies NEAR duplicate entries, as I have a number of duplicates that differ only by a single rather innocuous entry in the Notes for the contact. Thanks in advance for your prompt replies. Hi Tom, What I have done in the past is export the contacts to a CSV file, import into Excel, sort by the suspect duplicate field ... then delete the dups (you can write a fairly simple VBA macro to do this if you want to autom...

Check for duplicate entries???
Sorry in advance for the newbie type question :) I'm going to be making a long list of items and prices. I will be getting to the point I will come across the same item again and want to make sure I don't have duplicate entries. Is there a way in Excel to check and see if that entry has already been entered in the workbook? I assume there are a couple ways, but how would you set things up to check to prevent duplicate entries? Thanks (I'm using Excel 2002) You may want to look at Chip Pearson's site. He has lots of techniques to work with duplicates: http://www.cp...

Missing credit entry in Cashbook Management
A problem occured when a Payables transaction was entered to capture both purchases and payment. They were both entered and posted using the same document. Stangely, the payment does not display onTransaction Enquiry/Void.i.e the CR that should go to bank (Note I use Cashbook Management). To correct this I voided the payment in Purchasing module, sadly the voided transaction appears in Transaction Enquiry/Void Enquiry. Please could someone help me out on how to correct this? Thanks, DD ...

Restoring Calendar Entries w/ Exmerge
Good Morning All, I have a quick and easy question. First, the setup: Client: Outlook 2003 Server: Exchange 2003 I have a user who deleted and duplicated many calendar entries in their calendar. They would like entries restored from the previous day's backups (backups are done in MSBACKUP) I assume that I should. 1. Restore the mailbox store her account resides on for the previous day 2. Mount the DB in the Recovery Storage Group 3. Use Exmerge to extract the mailbox contents to PST 4. Import PST back into mailbox for user This sound like the best way to handle this issue? ...

Duplicate Journal Entry #
Under what circumstances can a duplicate J/E # be created? I have one created and posted to Dec, 2009 and the same J/E number created and posted to Jan, 2010. Looking at the GL20000 and GL30000 records (1-DR, 1-CR row in each table), they appear to be the reversal of a reversal. Each has the same TRXSORCE and ORTRXSRC codes, but there are differences in the Sequence number and the PSTGNMBR/PPSGNMBR columns. Ken Ken, Reversing entries and recurring GL entries will have the same journal entry number. -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? ...

GL Entry
Does anyone out there know how to do a one sided gl entry in dynamiics gp 10.0, please let me know my email is jims@em-powered.com, Thank You so much No one sided entries of posting accounts, allocation accounts, or variable allocation accounts is possible (principle of double-entry accounting). This is only possible with Unit accounts, also known as statistical accounts. However, unit accounts will have no impact on your financials and are only used as reference on your FRx reports. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobal...

Three table data entry form
Hello, I have created three tables: a Person table, a Guardian table, and a PersonGuardian table. The PersonGuardian table is made up of just 2 fields - a foreign key to a PersonId and a foreign key to a GuardianId. (If a person is over 18, the "Self" record would be the Guardian; several children from the same family may have the same guardian, etc - that is why a seperate table with just the 2 fields in it. Trying to get to 3NF.) Anyway - how do I create a form that allows entry of a new person with a drop-down showing existing guardians from which a name may be chosen, and then ...

Data Entry
I don't know if this can be done using access, or if I would have to write a program to do it.... I am trying to make a database to track the work I do. I can process 8 'samples' at a time, which get stored in groups of 96. I would like to make a form that will allow me to enter my 8 'samples' into a record. Then, when I reopen the form I would like the 8 'samples' to be blank, but when I enter new data I would like it to be posted in the next 8 'samples' in the record (which will track all 96 in the group, such that I will have a report that will...

Returns Transaction Entry Error
I'm trying to do a Return. Just a plain Return not Return w/Credit. I'm getting the error message: "There are no available quantities for this item or vendor item. Please select a different item or vendor item." The PO has been received but not invoiced. There are quantities on-hand for this item but many have been sold. Why am I getting this error? Thanks. Michelle Michelle, Apparently, the quantity that was received for this item/vendor combination have been sold or otherwise consumed. Do you buy this item from more than one vendor? Could...

outlook capability to password protect entry to email program
we are new users to Outlook/Outlook Express, we would like to use Outlook if we can prevent our kids from being able to click entry into outlook and viewing emails that have already been downloaded, etc. without a password. Outlook Express doesn't offer this protection, does Outlook? In Outlook; Rightclick Outlook Today-> Properties-> button Advanced-> button Change Password In OE; File-> Indentities-> Manage Identities...-> Properies-> Require Password -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Creating Signatures -Create ...

Excel counting problem where there might be 2 entries for one location?
Hi there, I have a spreadsheet in which Column A looks like this; 0034-MILTON KEYNES 0034-MILTON KEYNES 0035-MARBLE ARCH 0035-MARBLE ARCH 0039-EAST HAM 0040-REGENT ST 0043-STAINES 0051-PENGE 0052-BEDFORD You will notice there may be either one entry for a particular location or there may be two entries for the same location. Now I need to total up the amount of individual locations taking into account in the example above for example there are a total of 9 entries but only 7 of those are individual locations. Is this even possible and if so how would I achieve this...

Entry Point Not Found
While compiling my firefox plugin using good old (read: buggy :) VC++ 6.0 I don't get any error messages. But when I load my plugin into firefox I got this error: --------------------------- firefox.exe - Entry Point Not Found --------------------------- The procedure entry point ??RnsQueryInterface@@UBEIABUnsID@@PAPAX@Z could not be located in the dynamic link library xpcom.dll. --------------------------- OK --------------------------- By examining my dll (plugin) I can see that it exports: ??RnsQueryInterface@@QBEIABUnsID@@PAPAX@Z (it differes in only 1 letter). Well, I got the ...

Macro Pause during Dialog Box data entry
How can I make a macro pause for user input into a dialog box. As a simple example, If I have the DATA>VALIDATION menu open, how can i make it pause while I set the validation options, and then continue on with the rest of the macro after the user hits OK? Thanks Sub a() MsgBox "Before show" Application.Dialogs(xlDialogDataValidation).Show MsgBox "After show" End Sub -- Jim "kayabob" <kayabob@discussions.microsoft.com> wrote in message news:466DA9EA-35D0-4058-841D-80DF09B7D640@microsoft.com... | How can I make a macro pause for user i...

401(k) deductions
Realizing I need to be more responsible financially led me to buy Money 05 Deluxe and get all my banking records in order. That's done, but now I want to add my 401(k) information. I have been contributing (through payroll deductions) to the 401(k) for about 3 years now; I should also mention I have most of my paper statements since the plan began. I did enter payroll deduction info for this year -- hopefully that will make tax time more bearable -- and part of that was tracking each paycheck dedcution going to my 401(k).That having been said, is there any benefit to being anal about th...

Problems transferring Autocorrect entries to another computer
Hi all. I have been attempting to transfer Autocorrect entries from an old laptop to a new one, and have had only partial success. I have some Word files on the new laptop that I transferred from the old. When I open one of those old files on the new laptop, the transferred Autocorrect entries work. But when I open a new Word file on the new laptop, the transferred Autocorrect entries DO NOT work. Any suggestions? -- D4P If AutoCorrect works on some documents but not others then check your proofing language. AutoCorrect entries aren't document specific bu...

Excel -how do I edit an entry that's an email address
Excel has that annoying function that makes all email addresses clickable (like it or not), but as such, are extremely difficult to edit. How do I easily select it only to make changes? -- To reply by email, remove the word "space" Select the cell that the email address is in just by arrowing over to it if you don't want to accidentally click the email and then go to Insert- Hyperlink and on the bottom left of the box click "remove link" and then the email address won't be clickable anymore, thus your problem solved. This is annoying and in word I think you can...

Receiving Transactions Entry: Over-receipt
Hi All, Does anyone know how I can block over-receipt on Purchasing Receiving doc. I don't want users to ever receive more than the quantity on the PO. I haven't come across any setup option that prevents this. Thank you. LR LR, there is no setup option to control this. There is a VBA customization available from MBS Professional Services for $750 that allows you to set a tolerance on over-receipts. You set the tolerance to 0 to prevent over-receiving on the PO. Why MS doesn't incorporate this as standard functionality puzzles me. Frank Hamelly MCP-GP, MCT, MVP East Coas...

text (c) entry
How do I create the text label (c) in a cell? Every time I enter it alone, or as part of a longer text string, it shows as a c inside a circle, the copyright symbol, I presume. I just want left bracket, small c, right bracket. Excel from Office XP. Thanks -- Ron Carruthers Lonely Pine Consulting It's an Autocorrect substitution. Type CTRL-z immediately after the substitution to reverse it. Or you can disable autocorrect via Tools/Autocorrect and uncheck the Replace text as you type checkbox (or jut remove the (c) entry). Note, however, that changes to Autocorrect apply Office-wide,...

Counting Text Entries
Hi everyone, I have a large database of information which includes columns of text entries. Is there a way of counting these text entries? e.g.: HBO SL SL MN RTA HBO SL SL MN RTA HBO SL SL MN RTA Counting the number of HBO, RTA,SL entries etc. The database consists of 26 worksheets, is it also possible to get the total results from all worksheets to appear on a single summary page? Thanks very much for any help. Jack -- JackJJW ------------------------------------------------------------------------ JackJJW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid...

Sales Transaction Distribution Entry
Transaction --> Sales --> Transaction Entry : When credit card/cash is selected to show the revenue the distribution tab will show the following details : Debit Credit Sale A/c 1250.00 Credit Card A/c 1250.00 Account Receivable A/c 1250.00 Account Receivable A/c 1250.00 Why is it showing the "Accounts Receivable" when the transaction has been done by Credit Card? Do you have Separate Payment Distributions checked in Company Setup Options? (I...

Autonumbering in Bank Transaction Entry
Hi everyone, A user from the RM dpt today asked why the number populated in the field 'Number' (which starts with DAJ, IAJ or WDL depending on the selection of transaction type) is not increasing automatically anymore ? Usually this number changes after each posting, and all of sudden, she realized that it was no longer the case, so she did some transactions by changing manually this number... Where is this number managed and how can I check the consistency of this number ? Thanks in advance for all help that can be provided. Best regards, B. Bucher Sr. IT Architect Pelican Intl...

Having common calendar entries on server..
Is it possible to have calendar entries applied on the Exchange 2000 server that all Outlook clients can see in their calendars ? For example, instead of all clients importing holidays, we would like to centrally manage this and add additional days and events that are common for the entire corporation. /Patrik ...

Out to remove entries from the quick fill list..
Outlookers, When you create a new email message in Outlook and type a character in the "To:" field, Outlook immediately pops up with a quick list of all the names that match the character. In my case, I have one person with two email addresses: bob (bob@xyc.com) bob (bob@abc.com) I am used to just typing "bob" and sending the email out. As a result, the email ends up going to Bob's home (the first email address) and that is not desirable. How do I get rid of the first entry altogether? This entry is definitely not in my address book. It appears each time an emai...

Quick Journal Entry Segment Sort
I have 400 G/L accounts set up in a quick journal. The accounts are made up of three segments. Is there a way I could sort the account string display by a particular segment? I need to add several accounts to the entry and I want to make sure they don't already exist. I'd hate to have to scroll through all accounts one by one to check if the accounts exist. Thanks. -- Bob ...

Bills and Deposits entries disappeared
I'm using Money 2004 Small Business. Recently a number of my scheduled Bills and Deposits entries disappeared and I am not sure why. I've re-entered the Deposits entries that were gone, but I'm not sure how to re-enter my scheduled loan payments since this was handled automatically when I first set up my loan. Has this happened to anyone else or does anyone know how to re-enter my scheduled loan payments? Thanks in advance, - J. ...