Solved

Read from text file and store in array

Posted on 2003-12-01
5
420 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA, find a string in a column, update a cell 7 81
vb6 connector to mongodb 2 84
Zip Folders Using Chilkat Routines 1 41
Formula problem with Excel attachment 6 27
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

810 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