Extract Excel workbook from website using vba

Question

I am trying to automate data input to a model of mine from PIMCO website for fund holding database.

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

I need to extract the Holdings Report data (xls workbook) from the site.

My Code:

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

the webservice link(hrefLink above) being called on clicking the Holding Report button is

https://www.pimco.com/handlers/displaydocument.ashx?c=72201P522&wd=Holdings Report&fn=PIMCO Funds Emerging Markets Corp Bond Fund Portfolio Holdings 709.XLS&id=QRQg2jRnd9AJrFn%2fOA2Sp0xR09EkWc64pyAEuordzHsARqqpDYmvlBBcIgDokEeCM6cdWs55%2f4wk9gu2ywfEdow%2fMGHlPUWvKY1XdSZmKrA3dh%2f4%2fXZQYr0OIvF2X7n9DExITdx0FiH2Zif6g0MZzESHcGg%2fc8NNWJtiJJ5XM0xuLVVJAXKxIy3Ss94TpsWkZGjcOl%2fqh3hyYNFIRkz2BWGmp7Kb5UUYnPq%2b2wOMX8SlnWx0bj9CCaPaZaoIBhSMcvumJSEOqtmDJhAa%2f1FWPWyayrohG3C%2b5QsHRW7w8onfUCq08RkCaaRpDafTPDobAtrczfeMSCHldvK2S5dv6v39eWx358pDYNilnNnjDXv7al%2bfXyjglUYZFabL210V

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Based on this: 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

Hope this help!

Extract Excel workbook from website using vba

1 Answers1