Using VBA to extract SIC Code from XML

[mchac]

I'd like to extract the SIC code from a given webpage such as this:

EDGAR Search Results

You can see the SIC in the blue band near the top (SIC: 5331).

In IE you can View Source (right click on the page above) and search for SIC in the XML and this is what I'd like to return to a VBA variable so I can store it in an Excel sheet.

So far I've been able to cobble this together, though I can't tell if it's correct, from reading various posts but I can't figure out how to search for the the SIC. Any help or direction is appreciated.

Sub test3()
    Dim xDoc As MSXML2.DOMDocument30
    Set xDoc = New MSXML2.DOMDocument30
    xDoc.async = False
    xDoc.Load ("EDGAR Search Results")
    
    'search for SIC
    
    Set xDoc = Nothing
End Sub

 

[rjwebgraphix]

I didn't see this till just now. I posted something on one of the other posts in regards to this. Where did you get that code from? I'd like to study it further. I'm not sure what its trying to do, but there is something missing.

In the meantime. This will pull the information directly into Excel. It isn't fast and even slower because it has to load the whole page. (Although might be faster for you since I'm pulling data via a dialup connection). The information you said in the other post that you would be looking for isn't inside of a table, so you can't be specific on which table to pull. Because of which, the following code pulls the whole page, but what you're looking for is within the first 10 lines or so. You can use normal VBA to extract the data from what is pulled in, but because it's not laid out in a table, you'll have to use string manipulations to get what you want out of it. Unfortunately, the string manipulations Len, Right, Left, Mid and the like are my nemesis, so you'd probably need additional assistance to pull the specific strings.

Referencing post from: http://www.mrexcel.com/forum/excel-questions/699272-how-check-if-file-exists-web-2.html

No variables needed to test with the hard coded website.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.sec.gov/cgi-bin/browse-edgar?company=&match=&CIK=34088&filenum=&State=&Country=&SIC=&owner=exclude&Find=Find+Companies&action=getcompany", Destination:= _
        Range("A1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With