VBA pulls data using a schema file

Question

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 is user2140261

---

Also why are you using XML files to get the schema when your site has already done for you? It makes no sense to let succeed in guessing about the creation, when already done. AS excel may do it wrong, and you may never catch its errors.

---

made me try to create an XML map in Excel using a data agency schema, just changing this statement:

 If el.href Like "*.xsd" Then 

and this statement

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

However, when I try to do this (by going to the XML task pane, clicking "Source" and then "XML Maps"), I get the following:

How is this possible? I open the Schema File and sees that the root of the node is one, and this is Schema

While it is reasonable that: ... It makes no sense to allow you to prefer to do this (Excel does the schema for you when you import only the XML file) when there is already done.

When I imported the XML file , I had one root node. This was the root of node xbrl , and it was very easy for me to understand things. Now I get the fragmentation seen in the first shot, and an amazingly long time.

• Do you think that I should / should refer to different root nodes when i want to import different data?

• How can I use a ready-made data agency scheme that I guard against error?

• How can I put all these nodes under the same root node (as excel does when I import an XML file into an XML map) instead of having many node roots and at the same time using a schema?

I hope that this editing of the question will not turn into a difficult fiasco for me again, since the solution for the first three editions of this question-stream was soon before my eyes.

Answer 1

The XML schema referenced on this site is a damn beast (given that the XML schema is subject to brutal constructions). This schema imports several additional schemas (further down, xs: import ...), which may explain the additional schemas available.

On the other hand: although the automatic XML schema generated by Excel is sometimes sometimes not complete or completely accurate (types), I would use this schema if necessary with some corrections (see Exporting XML from Excel and saving the date format ) .

Also, I couldn’t understand what you were trying to accomplish, although using MSXML to upload files seems like very reasonable advice.

Sorry for the superficial answer. Hope this helps, though, or gives some clues. Andreas