使用vba从网站中提取Excel工作簿

Question

我试图自动化的数据输入到我的一个模型，从PIMCO网站的基金持有数据库。

网址：https://www.pimco.com/investments/mutual-funds/emerging-markets-bond-fund/inst

我需要从网站中提取控股报告数据(xls工作簿)。

我的守则：

Set elements = HTMLDoc.getElementsByTagName("a")

For Each element In elements
    If InStr(element.innerText, "Holdings Report") Then
        hrefLink = element.href

        IE.navigate hrefLink // This creates a new pop up and a message asking if the file must be saved/opened

        Set wb = Workbooks.Open(element.href) // Throws an error
    End If
Next

单击Holding Report按钮时调用的webservice链接(HrefLink)是

709.XLS&id=QRQg2jRnd9AJrFn%2fOA2Sp0xR09EkWc64pyAEuordzHsARqqpDYmvlBBcIgDokEeCM6cdWs55%2f4wk9gu2ywfEdow%2fMGHlPUWvKY1XdSZmKrA3dh%2f4%2fXZQYr0OIvF2X7n9DExITdx0FiH2Zif6g0MZzESHcGg%2fc8NNWJtiJJ5XM0xuLVVJAXKxIy3Ss94TpsWkZGjcOl%2fqh3hyYNFIRkz2BWGmp7Kb5UUYnPq%2b2wOMX8SlnWx0bj9CCaPaZaoIBhSMcvumJSEOqtmDJhAa%2f1FWPWyayrohG3C%2b5QsHRW7w8onfUCq08RkCaaRpDafTPDobAtrczfeMSCHldvK2S5dv6v39eWx358pDYNilnNnjDXv7al%2bfXyjglUYZFabL210V https://www.pimco.com/handlers/displaydocument.ashx?c=72201P522&wd=Holdings Report&fn=PIMCO基金新兴市场公司债券基金

Answer 1

基于此：How do i download a file using VBA (Without internet explorer)

    Sub DownloadFile()

    Dim myURL As String
    myURL = "https://YourWebSite.com/?your_query_parameters"

For Each element In elements
    If InStr(element.innerText, "Holdings Report") Then

    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False, "username", "password"
    WinHttpReq.send

    myURL = WinHttpReq.responseBody
    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.responseBody
        oStream.SaveToFile "C:\file.csv", 2 ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If
Next    
End Sub

希望能帮上忙！