VBA: Download PDF from URL

[ssingh75]

Hi All,

I am using below code to donwload pdf from website but nothing is heppening.
Url : - http://www.barodapioneer/Downloads/NoticesAddendums/BOB Change in OPAs - Tirupati-Notice Ad.pdf

Code:

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


Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

Debug.Print DownloadFile(strPDFLink.Text, strPDFFile)

Please suggest

 

[Andrew Poulsom]

What's strPDFLink and strPDFFile? By the way that URL doesn't work for me.

 

[ssingh75]

Hi,

Below is the strPDFLink and file
http://www.bnpparibasmf.in/Uploads/Downloads/Requirement under PMLA Act 2002 and Rules frame thereunder.pdf


File - Requirement under PMLA Act 2002 and Rules frame thereunder

I want to download pdf using the given href of source in few cases its not working like if the href haging "," in between their link then it will not downloading the pdf.

eg.

<a href='../Uploads/Downloads/BNPP MF - Dividend Declaration in BNPP DYF - Oct 23, 2013.pdf' target="_blank"><

Source of page...

<span id="gvShowData_ctl02_lblTitle">Requirement Under PMLA Act 2002 and Rules Frame Thereunder</span>
<INPUT class=SYN_LNN value=265 type=label unselectable="on"><INPUT id=syn_ctrl265 class=SYN_BCH role=button value=- type=label unselectable="on">
</td><td style="width:120px;">


<INPUT class=SYN_LNN value=266 type=label unselectable="on"><INPUT id=syn_ctrl266 class=SYN_BCH role=button value=- type=label unselectable="on">
<span id="gvShowData_ctl02_lblTitle">06/11/2013</span>


<INPUT class=SYN_LNN value=267 type=label unselectable="on"><INPUT id=syn_ctrl267 class=SYN_BCH role=button value=- type=label unselectable="on">
</td><td style="width:120px;">


<INPUT class=SYN_LNN value=268 type=label unselectable="on"><INPUT id=syn_ctrl268 class=SYN_BCH role=button value=- type=label unselectable="on">
<a href='../Uploads/Downloads/Requirement under PMLA Act 2002 and Rules frame thereunder.pdf' target="_blank"><img alt="" src='../Images/ico_PDF.gif' title="Download" /></a>


<INPUT class=SYN_LNN value=269 type=label unselectable="on"><INPUT id=syn_ctrl269 class=SYN_BCH role=button value=- type=label unselectable="on">
</td>


<INPUT class=SYN_LNN value=270 type=label unselectable="on"><INPUT id=syn_ctrl270 class=SYN_BCH role=button value=- type=label unselectable="on">
</tr><tr style="background-color:White;">


<INPUT class=SYN_LNN value=271 type=label unselectable="on"><INPUT id=syn_ctrl271 class=SYN_BCH role=button value=- type=label unselectable="on">
<td class="tal">


<INPUT class=SYN_LNN value=272 type=label unselectable="on"><INPUT id=syn_ctrl272 class=SYN_BCH role=button value=- type=label unselectable="on">
<span id="gvShowData_ctl03_lblTitle">Declaration of Dividend </span>


<INPUT class=SYN_LNN value=273 type=label unselectable="on"><INPUT id=syn_ctrl273 class=SYN_BCH role=button value=- type=label unselectable="on">
</td><td style="width:120px;">


<INPUT class=SYN_LNN value=274 type=label unselectable="on"><INPUT id=syn_ctrl274 class=SYN_BCH role=button value=- type=label unselectable="on">
<span id="gvShowData_ctl03_lblTitle">23/10/2013</span>


<INPUT class=SYN_LNN value=275 type=label unselectable="on"><INPUT id=syn_ctrl275 class=SYN_BCH role=button value=- type=label unselectable="on">
</td><td style="width:120px;">


<INPUT class=SYN_LNN value=276 type=label unselectable="on"><INPUT id=syn_ctrl276 class=SYN_BCH role=button value=- type=label unselectable="on">
<a href='../Uploads/Downloads/BNPP MF - Dividend Declaration in BNPP DYF - Oct 23, 2013.pdf' target="_blank"><img alt="" src='../Images/ico_PDF.gif' title="Download" /></a>


<INPUT class=SYN_LNN value=277 type=label unselectable="on"><INPUT id=syn_ctrl277 class=SYN_BCH role=button value=- type=label unselectable="on">
</td>


<INPUT class=SYN_LNN value=278 type=label unselectable="on"><INPUT id=syn_ctrl278 class=SYN_BCH role=button value=- type=label unselectable="on">
</tr><tr>


<INPUT class=SYN_LNN value=279 type=label unselectable="on"><INPUT id=syn_ctrl279 class=SYN_BCH role=button value=- type=label unselectable="on">
<td class="tal">


<INPUT class=SYN_LNN value=280 type=label unselectable="on"><INPUT id=syn_ctrl280 class=SYN_BCH role=button value=- type=label unselectable="on">
<span id="gvShowData_ctl04_lblTitle">EUIN and Valuation Policy</span>


<INPUT class=SYN_LNN value=281 type=label unselectable="on"><INPUT id=syn_ctrl281 class=SYN_BCH role=button value=- type=label unselectable="on">
</td><td style="width:120px;">


<INPUT class=SYN_LNN value=282 type=label unselectable="on"><INPUT id=syn_ctrl282 class=SYN_BCH role=button value=- type=label unselectable="on">
<span id="gvShowData_ctl04_lblTitle">24/09/2013</span>


<INPUT class=SYN_LNN value=283 type=label unselectable="on"><INPUT id=syn_ctrl283 class=SYN_BCH role=button value=- type=label unselectable="on">
</td><td style="width:120px;">


<INPUT class=SYN_LNN value=284 type=label unselectable="on"><INPUT id=syn_ctrl284 class=SYN_BCH role=button value=- type=label unselectable="on">
<a href='../Uploads/Downloads/BNPP MF - EUIN and Valuation Policy - September 24, 2013.pdf' target="_blank"><img alt="" src='../Images/ico_PDF.gif' title="Download" /></a>

 

[Andrew Poulsom]

This works for me:

Sub Test()
    Dim strPDFLink As String
    Dim strPDFFile As String
    Dim Result As Boolean
    strPDFLink = "http://www.bnpparibasmf.in/Uploads/Downloads/Requirement under PMLA Act 2002 and Rules frame thereunder.pdf"
    strPDFFile = "Requirement under PMLA Act 2002 and Rules frame thereunder.pdf"
    Result = DownloadFile(strPDFLink, strPDFFile)
    MsgBox Result
End Sub

The file will be saved in the current folder.

 

[ssingh75]

Thanks for your Time...
But i am getting Error.
Compile Error
ByRef argument type error on "strPDFLink"

 

[Andrew Poulsom]

Here is my entire code:

Option Explicit

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

Sub Test()
    Dim strPDFLink As String
    Dim strPDFFile As String
    Dim Result As Boolean
    strPDFLink = "http://www.bnpparibasmf.in/Uploads/Downloads/Requirement under PMLA Act 2002 and Rules frame thereunder.pdf"
    strPDFFile = "Requirement under PMLA Act 2002 and Rules frame thereunder.pdf"
    Result = DownloadFile(strPDFLink, strPDFFile)
    MsgBox Result
End Sub

Function DownloadFile(URL As String, LocalFilename As String) As Boolean
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
End Function

If you have a control named strPDFLink you will need to amend it to suit.

 

[vinnie100]

....
The file will be saved in the current folder.

Thank you tonnes for this! It's a really quick approach compared to opening the hyperlink and saving that as xxx.
However, one question/problem I have is where does this downloaded file save to? You mentioned "current folder" - what is that? As it seems to be randomly saving this to odd folders (main ones on my desktop, but no idea where without searching for filename in c drive).

Is there a way to save to a particular location - e.g. say I have a folder name, can I save it there? If so, what amendments do I need to make to the above code?

(Right now I have 100 or so hyperlinks to http pdfs in excel spreadsheet column A, folder names for each link in Column B and filenames for these to be saved as in Column C....my plan is to save them to a particular location, and get a looping mechanism working - I think I should be able to amend the above code for the loop to work at least - e.g. by setting the strPDFLink and strPDFFile to point to cell values rather than hardcode and use offset function)

Any help you/anyone can provide, I would be most grateful