out of memory error

[ozarka]

I am getting "out of memory" (run time error 7) error on ReDim Preserve myArr(totalAmou nt). When I put my mouse over this line it reads 65894. My text file has 780k lines (almost a meg.). Anything I can do to make this go away. I am trying to retrieve certain row from a large text file.

Private Sub cmdImportRow_Click()
Dim myPath, myFile As String
Dim myLine As Integer

myPath = "\\server path\my folder\"
myFile = myPath & "test.txt"
myLine = 13

Me.Text5 = ExactLine2(myFile, myLine)


End Sub



Function ExactLine2(ByVal fileName As String, ByVal lineNumber As Long) As String
'
Dim myArr() As String
Dim totalAmount As Long
Dim intFileHandle As Long
Dim sLineIn As Variant
'
'Use FreeFile it is good pratice
intFileHandle = FreeFile()
'
'Open the text file
Open fileName For Input As #intFileHandle
'
'Do only while there is text
While Not EOF(intFileHandle)
'
'Go through the text file and load it into sLineIn
Line Input #intFileHandle, sLineIn
'
'Make sure we create a zero based array
'If totalAmount <> "" Then
totalAmount = totalAmount + 1
'Else
' totalAmount = 0
'End If
ReDim Preserve myArr(totalAmount)
myArr(totalAmount) = sLineIn
Wend
'
'This will obtain the line
ExactLine2 = myArr(lineNumber)
'
'Clean up
Close #intFileHandle
'
End Function

[FishVal]

Why do you store each line in an array while the function returns only the last one?

[ozarka]

i got the code from vbcity. i dont know what is doing. can you please offer a better solution.

[NeoPa]

The simple answer is - Don't try to load the whole file into an array if you only need access to a single line.
This is a large amount of data to store in RAM and it appears it's not really of any benefit anyway.

If it's absolutely necessary to load it all up then pre-scan the number of lines in the file first then ReDim it once and once only. ReDim with PRESERVE is a very expensive process. Both in space and time.

[ozarka]

can you please tell me which line i need to remove. i got the expensive (lol) code from vbcity.http://vbcity.com/forums/topic.asp?tid=2 3815

[NeoPa]

Try :

Private Sub cmdImportRow_Click()
  Dim myPath, myFile As String
  Dim myLine As Integer

  myPath = "\\server path\my folder\"
  myFile = myPath & "test.txt"
  myLine = 13

  Me.Text5 = ExactLine2(myFile, myLine)
End Sub

Function ExactLine2(ByVal fileName As String, ByVal lineNumber As Long) As String
  Dim lngLine As Long
  Dim intFileHandle As Long
  Dim sLineIn As Variant

'Use FreeFile as it is good pratice
  intFileHandle = FreeFile()
'
'Open the text file
  Open fileName For Input As #intFileHandle
'
'Do only while there is text
  For lngX = 1 To lineNumber
    If EOF(intFileHandle) Then
      sLineIn = ""
      Exit For
    End If
    Line Input #intFileHandle, sLineIn
  Next lngX
'This will obtain the line
  ExactLine2 = sLineIn
'
'Clean up
  Close #intFileHandle
'
End Function

[ozarka]

Thank you very much for the code. I am getting Ambigous name detected when I click on the command button.

[ozarka]

I am sorry, I had the command button twice in my vba. it worked perfectly.

Thanks a million.

[NeoPa]

No worries.
Glad I could help. That version should certainly be orders of magnitude less heavy on the resources :)