"Out of Memory" when import text file
 

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\DataInterfaceFile20030 911025232078.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)

--
Message posted from http://www.ExcelForum.com

"Out of Memory" when import text file
 

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

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

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

I need to import the file into the workbook for the User
to edit
certain field. Is there any other way to bypass this
limitation or
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))


---
Message posted from http://www.ExcelForum.com/

.

"Out of Memory" when import text file
 

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

Has work arounds.

--
Regards,
Tom Ogilvy

"kokyan" wrote in message
...
I create a Excel Macro using array to import a large text file (many
columns) into a workbook. It come out as out of memory immediately.

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

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

The codes like shown below:

Workbooks.OpenText FileName:= _

"C:\WINDOWS\Desktop\ITIS_V1\DataInterfaceFile20030 911025232078.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))


---
Message posted from http://www.ExcelForum.com/

"Out of Memory" when import text file
 

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

--
Message posted from http://www.ExcelForum.com