"Out of Memory" when import text file

[kokyan]

I create a Excel Macro using array to import a large text file (man
columns) into a workbook. It come out as out of memory immediately.

I search around in the Internet and found many articles (especiall
from Microsoft) regarding this problem. The articles mention about th
fix memory allocation by Excel itself (not depending to the physica
memory).

I need to import the file into the workbook for the User to edi
certain field. Is there any other way to bypass this limitation o
other methods without using array?

The codes like shown below:

Workbooks.OpenText FileName:= _

"C:\WINDOWS\Desktop\ITIS_V1\DataInterfaceFile20030911025232078.txt"
Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth
FieldInfo:=Array(Array(0, _
1), Array(42, 1), Array(43, 1), Array(45, 1), Array(46, 1)
Array(51, 1), _
Array(58, 1), Array(65, 1), Array(73, 1), Array(78, 1)
Array(80, 1), Array(100, 1), Array( _
200, 1), Array(220, 1), Array(260, 1), Array(300, 1)
Array(340, 1), Array(360, 1), Array( _
365, 1), Array(450, 1), Array(452, 1), Array(465, 1)
Array(467, 1), Array(470, 1), Array( _
487, 1), Array(504, 1), Array(505, 1), Array(506, 1)
Array(507, 1), Array(508, 1), Array( _
509, 1), Array(569, 1), Array(629, 1), Array(638, 1)
Array(647, 1), Array(677, 1), Array( _
678, 1), Array(680, 1), Array(696, 1), Array(726, 1)
Array(746, 1), Array(747, 1), Array( _
776, 1), Array(806, 1), Array(836, 1), Array(866, 1)
Array(897, 1), Array(898, 1), Array( _
901, 1), Array(902, 1), Array( _
926, 1), Array(946, 1), Array(1146, 1)

 

[Kevin Beckham]

If there are field delimiters then use them instead.
Otherwise you try

Sub OpenBigFile(Optional fName As String _
= "C:\WINDOWS\Desktop\ITIS_V1" & _
"\DataInterfaceFile20030911025232078.txt")

Dim fId As Integer
Dim strInput As String
Dim myArray
Dim i As Integer
Dim rNo As Integer

myArray = Array(0, 42, 43, 45, 46, 51, 58, 65, 73, 78, _
80, 100, 200, 220, 260, 300, 340, 360, 365, 450, 452, _
465, 467, 470, 487, 504, 506, 507, 508, 509, 569, _
629, 638, 647, 677, 678, 680, 696, 726, 746, 747, _
776, 806, 836, 866, 897, 898, 901, 902, 926, 946, _
1146, 2000)


fId = FreeFile(0)
Open fName For Input As #fID
On Error GoTo errTrap
Workbooks.Add
rNo = 1

Line Input #fId, strInput
Do While Not EOF(fId)
For i = 1 To UBound(myArray)
Cells(rNo, i) = Mid(strInput, myArray(i - 1) + 1, _
myArray(i) - myArray(i - 1))
Next i
rNo = rNo + 1
Loop

closeFile:
On Error GoTo 0
Close #fId
Exit Sub

errTrap:
Resume closeFile
End Sub

(please excuse any errors, its Xmas eve here and code is
written untested - Merry Christmas to all!)

Kevin Beckham

 

[Tom Ogilvy]

http://support.microsoft.com/?kbid=134826
XL: "Out of Memory" Message Using the OpenText Method

Has work arounds.

 

[kokyan]

Thanks Tom Ogilvy. The hiperlink given by you solved my problem