VBA使用模式文件拉取数据 [英] VBA Pulling Data with a Schema File

问题描述

我有以下代码

  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 
 
设置IE =新的InternetExplorer 
 
 IE.Visible = False 
 
 Ticker = Worksheets（Sheet1）。Range（A1）。 
 $ b LoadPage IE，https://www.sec.gov/cgi-bin/browse-edgar？ &安培; _ 
action = getcompany& CIK =&股票代码& type = 10-Q& _ 
& dateb =& owner = exclude& count = 20
 
设置els = IE.document.getElementsByTagName（a）
对于每个el 
如果修剪（el.innerText）=文档然后
 colDocLinks.Add el.href 
结束If 
下一个el 
 
 intCounter = 1 
 
对于每个lnk在colDocLinks 
 LoadPage IE，CStr（lnk）
对于每个el在IE.document.getElementsByTagName（a）
如果el.href赞* [0-9] .xml然后
 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 
尽管IE.Busy或IE.readyState< > READYSTATE_COMPLETE 
 DoEvents 
循环
 End Sub 
  

a href =https://stackoverflow.com/users/2140261/user2140261> user2140261

---

另外，为什么当您的网站已经为您制作时，为什么要使用XML文件来获取架构？当已经有一个制作时，让傻瓜猜测没有意义。 AS excel可以做错，你可能永远不会遇到错误。

---

让我尝试创建通过简单地更改此语句，使用数据代理的Schema将XML映射到Excel：

 如果el.href像* .xsd 然后
  

此语句

  ActiveWorkbook.XmlMaps.Add（el，Schema）。Name =xbrl Map& intCounter 
  

但是当我尝试这样做（通过转到XML任务窗格，推送Source，然后XML地图）我得到这个：

怎么可能？我打开时我有一个根节点。这是根节点 xbrl ，这使我很容易理解东西。现在我得到了在第一张图片中看到的碎片加上一个非常长的运行时间。

• 当我
  要导入不同的数据时，你是否相信/必须引用不同的根节点？ / p>




• 我如何使用数据机构的现成架构保护
  我的错误？




• 如何将所有这些节点放在一个根节点下（当我在XML Maps中导入XML文件时，excel为
  ），而不是有许多root
  节点同时使用模式？

我希望这个问题的编辑不会变成一个沉重的失败我再次作为前3次编辑这个问题的解决方案 - 线程在我的眼前。

解决方案

XML模式在该网站上引用的是一个野兽的地狱（考虑到XML-Schema容易被恶意构造）。此模式导入多个附加模式（进一步向下，xs：import ...）可能解释可用的其他模式。

另一方面：尽管Excel生成的自动XML模式确实有时不完整或完全准确（类型），但我仍然会使用该模式 - 如有必要进行一些更正（请参阅从Excel导出XML并保留日期格式）。

除此之外，我无法弄清楚你正在努力完成什么，虽然使用MSXML下载文件似乎是一个非常合理的建议。

对于肤浅的答案，对不起。我希望它有助于或提供一些线索。
Andreas

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:

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

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.

• 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?

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.

解决方案

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

这篇关于VBA使用模式文件拉取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！