Reading html file via vba

Question

In my project, I'm reading a html file with vba codes and I'm scannig it with some substrings then I'm creating a json file with these substrings. SUBTASK is one of my substrings. When I'm scanning the html file, it's finding 9 SUBTASK but in my html file there are more than 9. My codes like this.

            For i = 1 To lastrow                    
                txt = Cells(i, 1)                    
                If txt = "All" Or txt = "ALL" Or txt = "V2500-A5" Then   'Or txt = "V2500-A1"
                    engType = txt
                End If

                If txt = "About" Then GoTo skipNextRow

                If Left(txt, 1) <> "0" And Left(txt, 5) <> "TASK " And Left(txt, 4) <> "DMC:" And Right(Left(txt, 11), 8) <> "SUBTASK " Then GoTo skipNextRow                                                            

                If Left(txt, 5) = "TASK " Then
                    locationTASK = InStr(1, txt, "TASK ")

                ElseIf Left(txt, 4) = "DMC:" Then
                    locationDMC = InStr(1, txt, "DMC:")
                    locationIssueNo = InStr(1, txt, "Issue No:")
                    locationIssueDate = InStr(1, txt, "Issue Date:")

                ElseIf Right(Left(txt, 11), 8) = "SUBTASK " Then

                    Debug.Print "Subtask: " & txt
                    locationSUBTASK = InStr(1, txt, "SUBTASK ")

                End If                    

skipNextRow:

           Next i

           ReDim Preserve arrApplicability(w): arrApplicability(w) = engType
           ReDim Preserve arrPartNo(w): arrPartNo(w) = myTemp
           w = w + 1

My issue in SUBTASK section, there are no issue in other sections.

That's a lot of code. Can you narrow it down to a more manageable question? — braX
– braX, Commented Jan 13, 2020 at 6:30
@braX I edited my codes. There are only reading and scanning codes. — mustafa53
– mustafa53, Commented Jan 13, 2020 at 6:38

Sam · Accepted Answer · 2020-01-13 09:39:25Z

1

The way you are finding text is a bit shaky. There isn't much room for changes in the expected format. Without seeing your actual input, it will be a lot of guesswork, but here it goes.
I suspect that the word subtask is placed in different columns and you are only getting those that start in column 4. Here are a few tips that might make your debugging easier.

This will help you find the missed rows and where they are. Replace the End If with

    Else
        locationSUBTASK = InStr(1, txt, "SUBTASK")
        Debug.Print "SUBTASK found in position " & locationSUBTASK 
    End If

Mid is a function that will help you a bit. Replace Right(Left(txt, 11), 8) with Mid(txt, 4, 8). Doesn't really help your current issue, but still.
Learn about regular expressions. Here is a snippet that will help you get started:

    Dim RegExp As Object
    Dim Matches As Object

    Dim txt As String
    txt = "something SUBTASK yada yada"

    Set RegExp = CreateObject("VBScript.RegExp")

    RegExp.Pattern = ".*SUBTASK (.*)"
    Set Matches = RegExp.Execute(txt)
    Debug.Print Matches(0).SubMatches(0)

The pattern ".*SUBTASK (.*)" means "Find the word 'SUBTASK ' anywhere in a line and keep track of what's coming after it.". The (.*) part will be recognized as a submatch.

answered Jan 13, 2020 at 9:39

Sam

5,7411 gold badge21 silver badges36 bronze badges

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

2 Comments

Tom Robinson Over a year ago

Yup. Use Regular Expressions. Instead of generic objects, I prefer to use Dim regex As RegExp, matches As MatchCollection, sm As SubMatches

Sam Over a year ago

@TomRobinson, I agree. In this case I opted for generic objects to lower the entrance threshold. Not sure if that is good or bad.

Collectives™ on Stack Overflow

Reading html file via vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related