CODEWITHSUNDEEP
xmlvbaexcel
ExoticBirdsMerchant
ExoticBirdsMerchant

Reputation: 1516

VBA Pulling Data with a Schema File

I have this code below

Option Explicit

Sub MadMule2()
    Dim IE As InternetExplorer
    Dim el
    Dim els
    Dim colDocLinks As New Collection
    Dim Ticker As String
    Dim lnk
    Dim intCounter as Integer    

    Set IE = New InternetExplorer

    IE.Visible = False

    Ticker = Worksheets("Sheet1").Range("A1").Value

    LoadPage IE, "https://www.sec.gov/cgi-bin/browse-edgar?" & _
                  "action=getcompany&CIK=" & Ticker & "&type=10-Q" & _
                  "&dateb=&owner=exclude&count=20"

    Set els = IE.document.getElementsByTagName("a")
    For Each el In els
        If Trim(el.innerText) = "Documents" Then
            colDocLinks.Add el.href
        End If
    Next el

    intCounter = 1

     For Each lnk In colDocLinks
        LoadPage IE, CStr(lnk)
        For Each el In IE.document.getElementsByTagName("a")
            If el.href Like "*[0-9].xml" Then
                ActiveWorkbook.XmlMaps.Add(el, "xbrl").Name = "xbrl Map"
            End If
        Next el
    Next lnk
End Sub

Sub LoadPage(IE As InternetExplorer, URL As String)
    IE.navigate URL
    Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
End Sub

This comment of user2140261

Also why are you using the XML files to get the Schema when your website already has one made for you? Doesn't make sense to let excel guess at making one when there is already one made. AS excel could do it wrong and you might never catch its mistakes.

got me into trying to create an XML Map into Excel by using the data agency's Schema by simply changing this statement:

If el.href Like "*.xsd" Then

and this statement

ActiveWorkbook.XmlMaps.Add(el, "Schema").Name = "xbrl Map " & intCounter

However when i am trying to do it (by going to XML task pane, pushing Source and then XML Maps) I get this:

enter image description here

How is this possible? I open the Schema file and is see that the root node is one and it is Schema

enter image description here

While it is reasonable that : ...Doesn't make sense to let excel guess at making one (Excel makes a Schema for you when you import only an XML file) when there is already one made.

When i imported the XML file i had one root node made. This was the root node xbrl and it made quite easy for me to understand things. Now i get the fragmentation seen in the first picture plus an amazingly long running time.

I hope this edit of the question doesn't turn into a heavy fiasco for me again as the solution for the former 3 edits of this question-thread was rather before my eyes.

Upvotes: 8

Views: 3380

Answers (1)

Andreas J
Andreas J

Reputation: 546

The XML-Schema that is referenced on that site is a hell of a beast (considering XML-Schema is prone to beastly constructs). This schema imports several additional schemas (further down, xs:import ...) what might explain the additional schemas available.

On the other hand: Although the automatic XML-Schema generated by Excel is indeed sometimes not complete or fully accurate (types) I would use this schema nevertheless -- if necessary with some corrections (see Exporting XML from Excel and Keeping Date Format).

Apart from that I couldn't figure out what you were trying to accomplish, although using MSXML to download the files seems a very reasonable advise.

Sorry for the superficial answer. I hope it helps nonetheless or gives some clues. Andreas

Upvotes: 1

Related Questions