I've created a very small script in vba to parse Title from almost any website. I've checked it with few websites and it is providing with the accurate results. I used split function on responsetext to achieve the result. Websites should be placed in column A and the results will be displayed in column B adjacent to each site. I tried to make it flawless.

Sub Title_Data()
Dim http As New MSXML2.XMLHTTP60

For Each cel In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).row)
    With http
        .Open "GET", cel.Value, False

        L = L + 1
        Cells(L, 2) = Split(Split(.responseText, "<title>")(1), "</title>")(0)
    End With
Next cel
Set http = Nothing
End Sub

Error handling

What if there is no <title> in a web page, or if it's written as <TITLE>? The script will crash.

It we would be good to check the result of Split to make sure you don't access an invalid index.

Extracting substrings

Instead of Split, it would be better to use Instr to find the start and end positions of the title, and then Mid to extract the substring. This is better because Split creates an array of strings, and scan until the end of the input string. Instr can stop immediately when it finds a match and doesn't need to create new objects.

  • \$\begingroup\$ Thanks sir janos, for your suggestion. I didn't think of uppercase side. I'll follow your guideline. Thanks again. \$\endgroup\$ – SIM Jun 4 '17 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.