By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

out of memory error

P: 27
I am getting "out of memory" (run time error 7) error on ReDim Preserve myArr(totalAmount). 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdImportRow_Click()
  2. Dim myPath, myFile As String
  3. Dim myLine As Integer
  4.  
  5. myPath = "\\server path\my folder\"
  6. myFile = myPath & "test.txt"
  7. myLine = 13
  8.  
  9. Me.Text5 = ExactLine2(myFile, myLine)
  10.  
  11.  
  12. End Sub
  13.  
  14.  
  15.  
  16. Function ExactLine2(ByVal fileName As String, ByVal lineNumber As Long) As String
  17. '
  18. Dim myArr() As String
  19. Dim totalAmount As Long
  20. Dim intFileHandle As Long
  21. Dim sLineIn As Variant
  22. '
  23. 'Use FreeFile it is good pratice
  24. intFileHandle = FreeFile()
  25. '
  26. 'Open the text file
  27. Open fileName For Input As #intFileHandle
  28. '
  29. 'Do only while there is text
  30. While Not EOF(intFileHandle)
  31. '
  32. 'Go through the text file and load it into sLineIn
  33. Line Input #intFileHandle, sLineIn
  34. '
  35. 'Make sure we create a zero based array
  36. 'If totalAmount <> "" Then
  37. totalAmount = totalAmount + 1
  38. 'Else
  39. ' totalAmount = 0
  40. 'End If
  41. ReDim Preserve myArr(totalAmount)
  42. myArr(totalAmount) = sLineIn
  43. Wend
  44. '
  45. 'This will obtain the line
  46. ExactLine2 = myArr(lineNumber)
  47. '
  48. 'Clean up
  49. Close #intFileHandle
  50. '
  51. End Function
Dec 12 '07 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Why do you store each line in an array while the function returns only the last one?
Dec 12 '07 #2

P: 27
i got the code from vbcity. i dont know what is doing. can you please offer a better solution.
Dec 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 12 '07 #4

P: 27
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=23815
Dec 12 '07 #5

NeoPa
Expert Mod 15k+
P: 31,661
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdImportRow_Click()
  2.   Dim myPath, myFile As String
  3.   Dim myLine As Integer
  4.  
  5.   myPath = "\\server path\my folder\"
  6.   myFile = myPath & "test.txt"
  7.   myLine = 13
  8.  
  9.   Me.Text5 = ExactLine2(myFile, myLine)
  10. End Sub
  11.  
  12. Function ExactLine2(ByVal fileName As String, ByVal lineNumber As Long) As String
  13.   Dim lngLine As Long
  14.   Dim intFileHandle As Long
  15.   Dim sLineIn As Variant
  16.  
  17. 'Use FreeFile as it is good pratice
  18.   intFileHandle = FreeFile()
  19. '
  20. 'Open the text file
  21.   Open fileName For Input As #intFileHandle
  22. '
  23. 'Do only while there is text
  24.   For lngX = 1 To lineNumber
  25.     If EOF(intFileHandle) Then
  26.       sLineIn = ""
  27.       Exit For
  28.     End If
  29.     Line Input #intFileHandle, sLineIn
  30.   Next lngX
  31. 'This will obtain the line
  32.   ExactLine2 = sLineIn
  33. '
  34. 'Clean up
  35.   Close #intFileHandle
  36. '
  37. End Function
Dec 12 '07 #6

P: 27
Thank you very much for the code. I am getting Ambigous name detected when I click on the command button.
Dec 12 '07 #7

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

Thanks a million.
Dec 12 '07 #8

NeoPa
Expert Mod 15k+
P: 31,661
No worries.
Glad I could help. That version should certainly be orders of magnitude less heavy on the resources :)
Dec 12 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.