Solved

Read from text file and store in array

Posted on 2003-12-01
5
416 Views
Last Modified: 2010-05-01
I am trying to read from a text file and save the data in an array. This should be simple, but when I run it, I get the error- "Subscript out of range" or "Type mismatch."   What am I doing wrong?

x = 0
Open FileName For Input As #1
Do While Not EOF(1)
   Line Input #1, officeId(x)
   x = x + 1
Loop
Close #1
0
Comment
Question by:jennifere
5 Comments
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9852750
Here is one way to do it:

Private Sub Form_Load()
    Dim fileName As String
    Dim officeid() As String
    Dim x As Integer
   
    fileName = "C:\SomeFile.txt"
    x = 0
    Open fileName For Input As #1
    Do While Not EOF(1)
        ReDim Preserve officeid(x + 1)
        Line Input #1, officeid(x)
        x = x + 1
    Loop
    Close #1
End Sub

Here is a different way to do it with Collections:

Private Sub Form_Load()
    Dim fileName As String
    Dim inputLines As Collection
    Dim inputLine As Variant
    Dim curLine As String
    Dim x As Integer
       
    ' read them in
    inputLines = New Collection
    fileName = "C:\SomeFile.txt"
    Open fileName For Input As #1
    Do While Not EOF(1)
        Line Input #1, curLine
        inputLines.Add curLine
    Loop
    Close #1
   
    ' spit them back out
    For Each inputLine In inputLines
        Debug.Print inputLine
    Next inputLine
   
    ' spit them back out a different way (less effecient in collections)
    For x = 1 To inputLines.Count
        Debug.Print inputLines.Item(x)
    Next x
End Sub

Idle_Mind
0
 
LVL 26

Accepted Solution

by:
EDDYKT earned 125 total points
ID: 9852774
Or

Dim officeId
Dim ff As Integer, Filename As String, s As String
   
Filename = "File Name"
ff = FreeFile
Open Filename For Binary Access Read Lock Read Write As #ff
s = Space$(LOF(ff))
Get #ff, , s
Close ff

officeId = split(s, vbCrlf)
0
 

Expert Comment

by:enari
ID: 9852916
The size of the array needs to be set

DIM OfficeID() as varient

Then the size needs to be declared.

REDIM OfficeID(15) creates an array of 15.

If you don't know how big it is, you do what Idle_Mind did, and

REDIM PRESERVE OfficeID(x+1)

Be sure to keep track of X so you know the size once you finish!
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9852985
EDDYKT's code is simple, elegant and will theoretically work with any size file.

From the help file:

"A variable-length string can contain up to approximately 2 billion (2^31) characters."

So as long as you have enough memory and your file doesn't have more than 2^31 characters, you are good to go. =)

The code I wrote using the ReDim Preserve is easy to read, but will be EXTREMELY slow for large files.

Idle_Mind
0
 

Author Comment

by:jennifere
ID: 9853160
Thanks - that works!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now