VBA string split in Text File

Question

So here's the problem, I have a text file with all the information I need to input into my program (through VBA). But, there's one section I need to split, then use the second half of the split string for my program. BUT every time I run this code, I get an error stating the "subscript is out of range".

Here's the code:

Const modelList As String = "C:\modelList.txt"

Dim inFileNum As Integer
Dim strData As String
Dim strLine As Variant
Dim strSplit As Variant
Dim intCount As Integer

intFileNum = FreeFile
intCount = 0
Open modelList For Input As #intFileNum
Do Until EOF(intFileNum)
Input #intFileNum, strData
    Do Until strData = "[SPECS]"
    Input #intFileNum, strData
        Do Until strData = " "
        Input #intFileNum, strData
            strSplit = Split(strData, " ")
                For Each strLine In strSplit
                    SPECS.Value = strSplit(1)
                Next
        Loop
    Loop
Loop
Close #intFileNum

Please help.

Please use {} on the toolbar to format code correctly (highlight the code and click the button). — Fionnuala
– Fionnuala, Commented Nov 30, 2011 at 15:31
Why do you prefix a variant name with "str" ??? To poison the life of the next guy who will have to maintain your code ? — Patrick Honorez
– Patrick Honorez, Commented Nov 13, 2012 at 8:03
@iDevlop For the most part, it is much safer to prefix variables in VBA rather than risk ending up referring to both a control and a variable with the same name or using a reserved word or function as a variable. — Fionnuala
– Fionnuala, Commented Nov 15, 2012 at 12:42

mwolfe02 · Accepted Answer · 2011-11-30 16:05:07Z

1

Your problem is in this code here:

    Do Until strData = " "
    Input #intFileNum, strData
        strSplit = Split(strData, " ")
            For Each strLine In strSplit
                SPECS.Value = strSplit(1)
            Next
    Loop

You are not doing the check for strData = " " until after the Split function is run (ie, at the start of the next loop iteration). Try the following instead:

    Do 
        Input #intFileNum, strData
        If strData = " " Or InStr(strData, " ") = 0 Then Exit Do

        strSplit = Split(strData, " ")
        For Each strLine In strSplit
            SPECS.Value = strSplit(1)
        Next
    Loop

answered Nov 30, 2011 at 16:05

mwolfe02

24.3k10 gold badges102 silver badges174 bronze badges

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

Comments

teddy2 · Accepted Answer · 2017-11-15 18:26:24Z

0

Another approach would be checking for the upper bound of the split array.

strSplit = Split(strData, " ")
For Each strLine In strSplit
    '~~~Assuming that you always want the second element, if available
    If (UBound(strSplit)) > 0 Then
        SPECS.Value = strSplit(1)
    Else
        SPECS.Value = strSplit(0)
    End If
Next

answered Nov 15, 2017 at 18:26

teddy2

3904 silver badges16 bronze badges

Collectives™ on Stack Overflow

VBA string split in Text File

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related