使用模式文件提取数据-VBAPullingDatawithaSchemaFile

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

这评论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.

还有，为什么您的网站已经为您制作了模式，而您却要使用XML文件来获取模式?让excel在已有产品的情况下进行猜测是没有意义的。正如excel可能会做错，你可能永远不会发现它的错误。

---

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

让我尝试通过使用数据代理的模式创建一个XML映射到Excel，只需更改以下语句:

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:

然而，当我尝试做它(通过访问XML任务窗格，推源和XML映射)时，我得到了这个:

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

这怎么可能?我打开模式文件，看到根节点是1，它是模式

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.

虽然有理由:……让excel猜测一个(当您只导入一个XML文件时，excel为您创建一个模式)是没有意义的，因为已经生成了一个XML文件。

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.

当我导入XML文件时，生成了一个根节点。这是根节点xbrl，我很容易理解。现在我看到了第一张图片中出现的碎片加上一个令人惊讶的长时间运行。

• Do you believe i should/must refer to different root nodes when i want to import different data?

  当我想导入不同的数据时，您认为我应该/必须引用不同的根节点吗?

• How could i use the ready-made schema of the data agency which guards me against mistake?

  如何使用数据代理的现成模式来防止错误?

• How can i put all of these nodes under one root node (as excel does when i import in XML Maps the XML file) instead of having many root nodes and at the same time use the Schema?

  我如何将所有这些节点放在一个根节点下(当我导入XML映射XML文件时，excel就会这样做)，而不是有很多根节点，同时使用模式?

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.

我希望这个问题的编辑对我来说不会变成一个严重的惨败，因为前三次对这个问题的编辑的解决方案就在我眼前。

1 个解决方案