VBA: How to read strings into a dynamic array with size to be determined (by the number of lines in the text file)

Question

I'm having trouble finding how I can read the number of lines in a text file I'm reading without reopening the file again. Thanks in advance for your time. I apologize in advance if you manage to find what I'm talking about easily (which I have done my due diligence already).

Sub CreateMessage()
    Dim filepath As String

    ' Read filepath in spreadsheet
    filepath = Cells(6, 8)

    ' Parse string contents from script
    ' Open file
    Open filepath For Input As #1

How do I read each line of the text file into a dynamic array, with size to be determined by the number of lines in the text file right at this line?

    ' Close file
    Close #1
End Sub

paxdiablo · Accepted Answer · 2011-06-26 02:29:20Z

1

If you're reading them into an array, you can just keep the current maximum size of the array in a variable and use ReDim (redimension) to expand it when you need more space. ReDim changes the size of an array on the fly and you probably want to make sure you do it in steps rather than one at a time.

Something like (unchecked, so you'll need to verify):

option explicit
option base 0
dim numlines as integer
dim maxlines as integer
numlines = 0
maxlines = 0
dim lines() as string

for every line in file             ' not actually valid syntax '
    if numlines = maxlines then
        maxlines = maxlines + 100
        redim preserve lines (maxlines)
    end if
    lines(numlines) = line
    numlines = numlines + 1
end for
redim preserve lines (numlines)

And that for loop is not actually valid syntax, but it has no bearing on the method used for for redimensioning. You should replace it with whatever code you're getting your information from the file.

edited Jun 26, 2011 at 2:29

answered Jun 25, 2011 at 23:24

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

stanigator Over a year ago

How can I tell if my array needs more space? Is there a function that tells me that all memory slots of the array are carrying value?

paxdiablo Over a year ago

@stanigator, see the update (with some code). You maintain two variables, the currently used space and the maximum available space. When the former tries to become larger than the latter, you redim and change the latter.

stanigator Over a year ago

Thanks for the revision and detailed explanations. I guess I should submit a different entry on how to find out the number of lines in a file (that's what I'm having more trouble with).

paxdiablo Over a year ago

@stanigator, you can't really do that without reading the lines, which means you'll be reading them twice (once to find the count, again to read them in). I think the idea of redim'ing is a better one since you can get away with reading them once.

stanigator Over a year ago

Thanks. I have a workaround solution for my other problem. However, the compiler does not seem to like the parameter maxlines as a non-constant. Know of any way to get around that?

Tim Williams · Accepted Answer · 2011-06-27 04:18:50Z

1

You could read in the entire file and then use

arr = Split(contents, vbcrlf)

To get the array. Assuming it has the typical windows line delimiter.

answered Jun 27, 2011 at 4:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

VBA: How to read strings into a dynamic array with size to be determined (by the number of lines in the text file)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related