"Out of Memory" when import text file

  • Thread starter Thread starter kokyan
  • Start date Start date
K

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)
 
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
 
Back
Top