动态网页查询 [英] Dynamic Web Query

问题描述

对我来说容易，因为我是新的。我已经创建了一些宏，从以下提取搜索结果：Sec全文搜索： http：/ /searchwww.sec.gov/EDGARFSClient/jsp/EDGAR_MainAccess.jsp

现在我的数据都被清理并设置在不同的列中，我需要根据结果​​创建查询。在列BI中有一个公司名称列表，我想引用列B中的每个单元格，并在 http://www.marketwatch.com/tools/quotes/lookup.asp?siteID=mktw&Lookup=options + media& Country = us& Type = All ，用于与公司名称相关的股票代码。这将有助于我，因为最终我将要执行多个查询来提取股份结构，收入等信息。我非常感谢任何时候回答这个问题。

解决方案

请尝试以下代码。

 子网站（）
 
 Dim doc作为HTMLDocument 
 Dim htmTable作为HTMLTable 
 
设置doc =新的HTMLDocument 
使用CreateObject（MSXML2 XMLHTTP）
。打开GET，http://www.marketwatch.com/investing/stock/BAC
 .send 
 Do：DoEvents：循环直到.readyState = 4 
 doc.body.innerHTML = .responseText 
 .abort 
结束
 
设置htmTable = doc.getElementsByClassName（companyname）（0）
 
如果没有htmTable是没有
 MsgBox htmTable.innerText 
结束如果
 
设置htmTable = doc.getElementsByClassName（lastprice）（0）
 
如果不是htmTable是没有
 MsgBox htmTable.innerText 
如果
结束Sub 
  

Go easy on me as I am new at this. I have created a couple of macros that extract search results from:Sec full text search: http://searchwww.sec.gov/EDGARFSClient/jsp/EDGAR_MainAccess.jsp

Now that my data is all cleaned up and set in different columns, I need to create a query based off of the results. In column B I have a list of company names, I would like to reference each cell in column "B", and query market watch at http://www.marketwatch.com/tools/quotes/lookup.asp?siteID=mktw&Lookup=options+media&Country=us&Type=All for the ticker symbol related to the company name. That would help me out, as eventually I will want to perform multiple queries pulling information about share structure, revenues ect. I would greatly appreciate any time in answering this question.

解决方案

Try below code.

Sub website()

    Dim doc As HTMLDocument
    Dim htmTable As HTMLTable

    Set doc = New HTMLDocument
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://www.marketwatch.com/investing/stock/BAC"
        .send
        Do: DoEvents: Loop Until .readyState = 4
        doc.body.innerHTML = .responseText
        .abort
    End With

    Set htmTable = doc.getElementsByClassName("companyname")(0)

    If Not htmTable Is Nothing Then
        MsgBox htmTable.innerText
    End If

     Set htmTable = doc.getElementsByClassName("lastprice")(0)

    If Not htmTable Is Nothing Then
        MsgBox htmTable.innerText
    End If
End Sub

这篇关于动态网页查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！