How To Read A .Txt File Into An Array (1 Viewer)

lhooker · Oct 23, 2014

I'm getting an error when trying to read a .txt file into an array for processing. The error and a snippet of the code are below.

Run-time error '9'
Subscript out of range

Dim iFileNum As String
Dim strLineInfo()
Dim i As Integer
Dim strExpensesFile As String

strExpensesFile = "c:\Finance\ExpensesIn.Txt"

iFileNum = FreeFile()
Open strExpensesFile For Input As iFileNum

Do While Not EOF(iFileNum)
Line Input #iFileNum, sBuf
strLineInfo(i) = sBuf
MsgBox strLineInfo(i)
Loop
Close iFileNum

CJ_London · Oct 23, 2014

you haven't initialised i

lhooker · Oct 24, 2014

CJ_London,

I found several examples of initialization, but the size was declared and the values were in variable string. I'm reading from a .Txt file and I will not know the size or the values. Please give an example. Thanks ! ! !

lhooker · Oct 24, 2014

CJ_London,

I did initialize 'strLineInfo()', but I still get the same error. Most recent code is below. Thanks ! ! !

Dim iFileNum As String
Dim strLineInfo() As String
Dim i As Integer
Dim strExpensesFile As String

strExpensesFile = "c:\Finance\ExpensesIn.Txt"

iFileNum = FreeFile()
Open strExpensesFile For Input As iFileNum

For i = 1 To 5
Line Input #iFileNum, sBuf
strLineInfo(i) = sBuf
MsgBox strLineInfo(i)
Next i

Close iFileNum

jdraw · Oct 24, 2014

This site has definitions and examples for text File I/O.

Suggest you use Option Explicit at top of your module.
Good luck.

lhooker · Oct 24, 2014

Jdraw,

FYI ! ! ! . . . There were no examples for reading a .Txt file into an array on that website.

essaytee · Oct 24, 2014

lhooker said:
CJ_London,

I did initialize 'strLineInfo()', but I still get the same error. Most recent code is below. Thanks ! ! !

Dim iFileNum As String
Dim strLineInfo() As String
Dim i As Integer
Dim strExpensesFile As String

strExpensesFile = "c:\Finance\ExpensesIn.Txt"

iFileNum = FreeFile()
Open strExpensesFile For Input As iFileNum

For i = 1 To 5
Line Input #iFileNum, sBuf
strLineInfo(i) = sBuf
MsgBox strLineInfo(i)
Next i

Close iFileNum

Just some thoughts, as I haven't tested what I'm about to say but it may point you in the right direction. I haven't got code handy at the moment but rather than a FOR/NEXT loop utilise an UNTIL EOF type loop on the file. Simply increment your counter variable (i) manually just prior to returning to the top of the loop.

Also, I believe you'll need to Redim your array and that is usually done at the top of the loop just prior to assigning any values to it. The Redim accounts for the fact you do not know how many elements you end up with.

Steve.

jdraw · Oct 24, 2014

Thanks! I thought you had problems with reading a text file. You should have said that working with Array was part of the issue. That link has almost all I/O commands.
Here is a sample procedure and some test data.

Sample procedure.

Code:

'---------------------------------------------------------------------------------------
' Procedure : LineInputStuff
' Author    : mellon
' Date      : 24/10/2014
' Purpose   :Sample vba procedure to read a text file and put records into an array.
'---------------------------------------------------------------------------------------
'
Sub LineInputStuff()
          Dim InputData As String
          Dim i As Integer
          Dim AWFArray() As String
10       On Error GoTo LineInputStuff_Error

20        i = 0
30        Open "C:\users\mellon\documents\custsp.txt" For Input As #1    ' Open file for input.
40        Do While Not EOF(1)    ' Check for end of file.
50            Line Input #1, InputData    ' Read line of data.
60            Debug.Print InputData       ' Print to the Immediate window.
70            ReDim Preserve AWFArray(i)   'redim the array dimension
80            AWFArray(i) = InputData     ' put record in array
90            i = i + 1                   ' increment array index

100       Loop
110       Close #1    ' Close file.
                      'SHOW THE ARRAY ELEMENTS AND CONTENTS
                      '~~~~~~~~~~~~~~~~~~~~~~~~~~~~

120       For i = 0 To UBound(AWFArray)
130           Debug.Print "AwFArray(" & i & ")  is " & AWFArray(i)
140       Next i

150      On Error GoTo 0
160      Exit Sub

LineInputStuff_Error:

170       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure LineInputStuff of Module AWF_Related"
End Sub

Sample text file.

Code:

CustomerNumber 	SP
647061 	AG
647061 	SP01
647061 	AG
208689 	AG
208689 	SP02
208689 	SP03
51982 	AG
51982 	GG
286432 	AG
286432 	AG
63482 	AG
63482 	SP01
63482 	SP02
63482 	SP03

Output generated:

Code:

CustomerNumber  SP
647061  AG
647061  SP01
647061  AG
208689  AG
208689  SP02
208689  SP03
51982   AG
51982   GG
286432  AG
286432  AG
63482   AG
63482   SP01
63482   SP02
63482   SP03
AwFArray(0)  is CustomerNumber  SP
AwFArray(1)  is 647061  AG
AwFArray(2)  is 647061  SP01
AwFArray(3)  is 647061  AG
AwFArray(4)  is 208689  AG
AwFArray(5)  is 208689  SP02
AwFArray(6)  is 208689  SP03
AwFArray(7)  is 51982   AG
AwFArray(8)  is 51982   GG
AwFArray(9)  is 286432  AG
AwFArray(10)  is 286432     AG
AwFArray(11)  is 63482  AG
AwFArray(12)  is 63482  SP01
AwFArray(13)  is 63482  SP02
AwFArray(14)  is 63482  SP03

Info specific to Arrays and vba
Good luck.

lhooker · Oct 26, 2014

JDraw,

That worked . . . Thanks ! ! !

jdraw · Oct 26, 2014

You are welcome. The link at bottom of last post has info on arrays and vba.

How To Read A .Txt File Into An Array (1 Viewer)

lhooker

Registered User.

CJ_London

Super Moderator

lhooker

Registered User.

lhooker

Registered User.

jdraw

Super Moderator

lhooker

Registered User.

essaytee

Need a good one-liner.

jdraw

Super Moderator

lhooker

Registered User.

jdraw

Super Moderator

Similar threads

Users who are viewing this thread