用于导航网页和下载 CSV 的 VBA 脚本 [英] VBA script to navigate webpage and download CSVs

问题描述

我正在寻求帮助开发在 Excel 中运行的 VBA 脚本.

在

选项显式公共子 GetData()Dim sResponse As String, body As Stringbody = "appliedFilters=*/INVESTMENT_COMPANY/NAME|Allianz"身体 = 身体 &&idolQueryParam=fund_prices"身体 = 身体 &"&orderedUIFields=officialName,priceUpdatedDate,buy,sell,priceChange,currency"身体 = 身体 &&mode=all"将 http 作为对象变暗设置 http = CreateObject("MSXML2.XMLHTTP")使用 http.打开POST"，https://www.fidelity.co.uk/product/securities/service/funds/download-funds"，False.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT".发送正文使用 CreateObject("ADODB.Stream").打开. 类型 = 1.write http.responseBody.SaveToFile "C:\Users\User\Desktop\Data.csv", 2.关闭结束于结束于结束子

<小时>

使用 Internet Explorer:

下面向您展示了如何执行这些操作中的每一个并为一个提供程序设置.您应该清楚如何设置循环以选择其他提供程序.请参阅 StackOverflow 上有关单击保存/打开"对话框的许多答案.

选项显式公共子下载()Dim ie 作为新的 Internet Explorer与即.可见 = 真.Navigate2 "https://www.fidelity.co.uk/fund-prices/"虽然 .Busy 或 .readyState <4:DoEvents:温德出错时继续下一步.document.querySelector(".button--accept").Click '<==Cookies出错时转到 0使用 .document.querySelector("#fund_type").selectedIndex = 0 '<== 所有..querySelector("#allfundsAvailability").点击'<==全部.querySelector("#fund_provider [value='AXA']").Selected = True '<== Select provider.querySelector("#filterBtn").Click '<== Apply filter.querySelector("#ofPrint").Click ' <==下载结束于停止'.退出结束于结束子

I'm looking for help in developing a VBA script to run in excel.

At this site, CSV files can be downloaded for each fund. The VBA script I'm after will:

• Navigate to the above URL
• Set Fund Type to 'ALL'
• Set Availablity to 'All'
• Select the Fund Provider from the drop down list
• Click the 'Filter Funds' button
• Click the 'Download data (.csv)' button

This process will need to be repeated for each item in the 'Fund provider' drop down.

I have little experience in using IE to navigate websites, so any pointers would be appreciated. My existing code is below. It allows me to access the Fund Type button but I'm not sure how to change it's values.

Option Explicit
Sub FidelityCSV()
    ' Create Internet Explorer object.
    Dim BaseURL As String
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")

    BaseURL = "https://www.fidelity.co.uk/fund-prices/"
    IE.Visible = True     ' Keep this hidden.
    IE.navigate BaseURL

    Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop

    'Wait for it to really finish loading
    Application.Wait (Now + TimeValue("0:00:15"))

    Dim oHEle 'As IHTMLElementCollection
    Dim oHDoc As HTMLDocument

    Set oHDoc = IE.document

    Set oHEle = oHDoc.getElementById("fund_type")

    ' Clean up.
    IE.Quit
    Set IE = Nothing
    Set oHEle = Nothing
    Set oHDoc = Nothing
End Sub

解决方案

XMLHTTP request:

You could avoid using a browser and mimic the pages POST request

Option Explicit
Public Sub GetData()
    Dim sResponse As String, body As String
    body = "appliedFilters=*/INVESTMENT_COMPANY/NAME|Allianz"
    body = body & "&idolQueryParam=fund_prices"
    body = body & "&orderedUIFields=officialName,priceUpdatedDate,buy,sell,priceChange,currency"
    body = body & "&mode=all"
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        .Open "POST", "https://www.fidelity.co.uk/product/securities/service/funds/download-funds", False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send body
        With CreateObject("ADODB.Stream")
            .Open
            .Type = 1
            .write http.responseBody
            .SaveToFile "C:\Users\User\Desktop\Data.csv", 2 
            .Close
        End With
    End With
End Sub

---

Using Internet Explorer:

The following shows you how to perform each of those actions and set for one provider. It should be clear how you can set up a loop to select other providers. Please refer to the many answers on StackOverflow regarding clicking the Save/Open Dialog.

Option Explicit    
Public Sub Download()
    Dim ie As New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "https://www.fidelity.co.uk/fund-prices/"

        While .Busy Or .readyState < 4: DoEvents: Wend

        On Error Resume Next
        .document.querySelector(".button--accept").Click '<==Cookies
        On Error GoTo 0

        With .document
            .querySelector("#fund_type").selectedIndex = 0 '<== All.
            .querySelector("#allfundsAvailability").Click '<== All
            .querySelector("#fund_provider [value='AXA']").Selected = True '<== Select provider
            .querySelector("#filterBtn").Click '<== Apply filter
            .querySelector("#ofPrint").Click ' <==Download
        End With
        Stop
        '.Quit
    End With
End Sub

这篇关于用于导航网页和下载 CSV 的 VBA 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！