More than 65536 entries...

[Mike]

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-

 

[Dave Hawley]

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 ***

 

[Mike]

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...

 

[Bernie Deitrick]

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]

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]

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]

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]

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]

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]

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