vba - Download Files (PDF) from Web

Question

I am fresh-starter at VBA. How do I download PDF file using UrlDownloadToFile from http://cetatenie.just.ro/wp-content/uploads/?

Can anybody help with this? The code is searching the PDF files udner hyperlinks and matches them under some criteria, i.e. the current year under their name.

Function UrlDownloadToFile(lNum As Long, sUrl As String, sPath As String, _
                           lNum1 As Long, lNum2 As Long) As Long

    UrlDownloadToFile = 0

    End Function

---

    Sub DownPDF()
    ' This macro downloads the pdf file from webpage
    ' Need to download MSXML2 and MSHTML parsers and install

    Dim sUrl As String
    Dim xHttp As MSXML2.XMLHTTP
    Dim hDoc As MSHTML.HTMLDocument
    Dim hAnchor As MSHTML.HTMLAnchorElement
    Dim Ret As Long
    Dim sPath As String
    Dim i As Long

    sPath = "C:Documents and Settingsee28118Desktop"
    sUrl = "http://cetatenie.just.ro/wp-content/uploads/"

    'Get the directory listing
    Set xHttp = New MSXML2.XMLHTTP
    xHttp.Open "GET", sUrl
    xHttp.send

    'Wait for the page to load
    Do Until xHttp.readyState = 4
        DoEvents
    Loop

    'Put the page in an HTML document
    Set hDoc = New MSHTML.HTMLDocument
    hDoc.body.innerHTML = xHttp.responseText

    'Loop through the hyperlinks on the directory listing
    For i = 0 To hDoc.getElementsByTagName("a").Length - 1
        Set hAnchor = hDoc.getElementsByTagName("a").Item(i)

        'test the pathname to see if it matches your pattern
        If hAnchor.pathname Like "Ordin-*.2013.pdf" Then
            Ret = UrlDownloadToFile(0, sUrl & hAnchor.pathname, sPath, 0, 0)

            If Ret = 0 Then
                Debug.Print sUrl & hAnchor.pathname & " downloaded to " & sPath
            Else
                Debug.Print sUrl & hAnchor.pathname & " not downloaded"
            End If
        End If
    Next i

    End Sub

Answer 1

Sorry - I should have guessed that URLDownloadToFile was an API call and could have answered the whole question at SQL "%" equivalent in VBA.

Remove the function named URLDownloadToFile completely. Paste this at the top of the module where your Sample procedure is

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) As Long

Now change that one line in Sample to look like this

Ret = URLDownloadToFile(0, sUrl & hAnchor.pathname, sPath & hAnchor.pathname, 0, 0)

Then you should be good to go. If you want some different file name, then you'll have to code some logic to change it at each iteration.