本文介绍了使用Excel vba在Investing.com中进行Web抓取的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我不了解vba.仅使用宏记录器.我需要将数据从网页下载到Excel电子表格,而据我所知,我无法使用vba.

I have no knowledge of vba. Only the macro recorder is used.I need to download the data from a web page to an Excel spreadsheet and with my knowledge of vba I am not capable.

特别是,我想执行一个宏以将页面的数据表下载到Excel: https://www.investing.com/equities/cellnex-telecom-historical-data

In particular, what I want to do a macro to download to Excel a data table of the page: https://www.investing.com/equities/cellnex-telecom-historical-data

此下载必须按照时间,日期范围和顺序进行配置.

This download would have to be configured in terms of time, date range and ordering.

步骤如下:1.-目的是将数据从"CLNX历史数据"表复制到Excel电子表格.2.-该下载应通过事先通过调用术语"在下拉菜单中选择每月"来完成.3.-下载是通过预先选择过去2年的日期范围进行的.4.-最后,按最大"列的降序对表格进行排序.5.-选择术语,日期范围和顺序后,将数据从"CLNX历史数据"表复制到Excel电子表格.

The steps would be the following:1.- The objective is to copy the data from the "CLNX historical data" table to an Excel spreadsheet.2.- That download should be done by previously selecting "Monthly" in the drop-down menu by calling "Term".3.- That the download is made by previously selecting the range of dates for the last 2 years.4.- Finally, order the table in descending order by the column "Maximum".5.- Once the term, the date range and the order are selected, copy the data from the "CLNX historical data" table to an Excel spreadsheet.

我已经尝试过使用宏记录器,但是无法配置术语,日期范围或顺序.

I have tried with the macro recorder but I am not able to configure the term, the date range or the ordering.

有人可以帮我吗?

感谢您的帮助.

代码:

Sub DataInvesting()

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")

IE.navigate "https://www.investing.com/equities/cellnex-telecom-historical-data"

Do Until IE.readyState = 4

DoEvents

Loop

IE.Document.getElementsByClassName("newInput selectBox float_lang_base_1")(0).Value = "Monthly"

IE.Visible = True

Set IE = Nothing

Set appIE = Nothing

End Sub

推荐答案

我刚刚测试了以下代码,并且可以运行,而不是每次需要运行此宏时都创建Internet Explorer实例,而是使用xmlhttp请求.只需复制整个代码并将其粘贴到vba中的模块中即可.不要忘记添加对Microsoft HTML对象库和Microsoft XML v6.0的引用(工具/引用).

I have just tested the following code and it works, instead of creating an instance of internet explorer every time we need to run this macro, we will use xmlhttp requests. Just copy the entire code and paste it into a module in vba. Don't forget to add references (Tools/References) to Microsoft HTML Object Library and Microsoft XML v6.0.

Option Explicit
Sub Export_Table()

'Html Objects---------------------------------------'
 Dim htmlDoc As MSHTML.HTMLDocument
 Dim htmlBody As MSHTML.htmlBody
 Dim ieTable As MSHTML.HTMLTable
 Dim Element As MSHTML.HTMLElementCollection


'Workbooks, Worksheets, Ranges, LastRow, Incrementers ----------------'
 Dim wb As Workbook
 Dim Table As Worksheet
 Dim i As Long

 Set wb = ThisWorkbook
 Set Table = wb.Worksheets("Sheet1")

 '-------------------------------------------'
 Dim xmlHttpRequest As New MSXML2.XMLHTTP60  '
 '-------------------------------------------'


 i = 2

'Web Request --------------------------------------------------------------------------'
 With xmlHttpRequest
 .Open "POST", "https://www.investing.com/instruments/HistoricalDataAjax", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "X-Requested-With", "XMLHttpRequest"
.send "curr_id=951681&smlID=1695217&header=CLNX+Historical+Data&st_date=01%2F01%2F2017&end_date=03%2F01%2F2019&interval_sec=Monthly&sort_col=date&sort_ord=DESC&action=historical_data"


 If .Status = 200 Then

        Set htmlDoc = CreateHTMLDoc
        Set htmlBody = htmlDoc.body

        htmlBody.innerHTML = xmlHttpRequest.responseText

        Set ieTable = htmlDoc.getElementById("curr_table")

        For Each Element In ieTable.getElementsByTagName("tr")
            Table.Cells(i, 1) = Element.Children(0).innerText
            Table.Cells(i, 2) = Element.Children(1).innerText
            Table.Cells(i, 3) = Element.Children(2).innerText
            Table.Cells(i, 4) = Element.Children(3).innerText
            Table.Cells(i, 5) = Element.Children(4).innerText
            Table.Cells(i, 6) = Element.Children(5).innerText
            Table.Cells(i, 7) = Element.Children(6).innerText

            i = i + 1
        DoEvents: Next Element
 End If
End With


Set xmlHttpRequest = Nothing
Set htmlDoc = Nothing
Set htmlBody = Nothing
Set ieTable = Nothing
Set Element = Nothing

End Sub

Public Function CreateHTMLDoc() As MSHTML.HTMLDocument
    Set CreateHTMLDoc = CreateObject("htmlfile")
End Function

这篇关于使用Excel vba在Investing.com中进行Web抓取的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 02:48
查看更多