Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opentext method of excel workbook object | Excel Programming | |||
OpenText Method failure | Excel Programming | |||
OpenText Method of Workbook Object Question | Excel Programming | |||
How to use Opentext method with xlFixedWidth? | Excel Programming | |||
Pass string as Parameter in OpenText method | Excel Programming |