OpenText Method

[JH]

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!

 

[Eric White]

Check out:

http://support.microsoft.com/default.aspx?scid=kb;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.)

 

[crazybass2]

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