Downloading Multiple Hypelinked PDFs

[Santiago Perez]

Hello and thanks for reading,

I'm trying to have a column of hyperlinks which lead to online pdf all downloaded at once. Mrice posted the following code as a solution on a similar thread http://www.excelforum.com/excel-gene...-document.html

I'm not familiar with where this should go to make it do its job. I'm entirely new to macros, VBA etc so a quick step by step on how to make it do its magic would be greatly appreciated. So far, I tried running it in macros and got the message: "Compile Error:Only comments may appear after End Sub, End Function, or End Property"
Here's the code:

Const TargetFolder = "C:\temp\"


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()
For Each Hyperlink In ActiveSheet.Hyperlinks
    For N = Len(Hyperlink.Address) To 1 Step -1
        If Mid(Hyperlink.Address, N, 1) <> "/" Then
            LocalFileName = Mid(Hyperlink.Address, N, 1) & LocalFileName
        Else
            Exit For
        End If
    Next N
    Call HTTPDownloadFile(Hyperlink.Address, TargetFolder & LocalFileName)
Next Hyperlink
End Sub


Sub HTTPDownloadFile(ByVal URL As String, ByVal LocalFileName As String)
Dim Res As Long
On Error Resume Next
Kill LocalFileName
On Error GoTo 0
Res = URLDownloadToFile(0&, URL, LocalFileName, 0&, 0&)
LocalFilename = “” 
End Sub

[BeachRock]

Hi Santiago,

Make sure you wrap code tags around any VBA code that you post. You can do that by pasting the code into the post, highlighting it and then click the # button that will be above your post. You can see below how it will look.

I created an example work book and attached it. Since I don't have any specific PDF file URLs I just used some pics of motorcycles I found on the web. It works for me to download specific pictures from the internet to the folder specified at C:\Temp\. I wish I could tell you some in depth information about the code you found that mRice wrote. Unfortunately, I'm only good enough to figure out approximately what they did and how to modify it to my needs. I was able to get this to work. I made some comments in the code below. You'll find this code in the list of macros for the workbook. The idea is pretty basic. Just plug in all of your URL's to each picture on Sheet 1, one per cell and then hit the "Download Pictures" button, which has the macro assigned to it. Then check the output folder for those pics.

'   This selects the folder where your files will be stored.
Const TargetFolder = "C:\temp\"

'   This declares some variables that my limited knowledge can't tell you about
'   other than they are referenced in the areas following it.  Some of them 
'   look like they reference the system registry in some way.

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

'   This is the actual macro that looks at the current sheet, reads all of the
'   hyperlinks and then calls the function to download the files located at
'   the hyperlink addresses.

Sub Test()

For Each Hyperlink In ActiveSheet.Hyperlinks
    For N = Len(Hyperlink.Address) To 1 Step -1
        If Mid(Hyperlink.Address, N, 1) <> "/" Then
            LocalFileName = Mid(Hyperlink.Address, N, 1) & LocalFileName
        Else
            Exit For
        End If
    Next N
    Call HTTPDownloadFile(Hyperlink.Address, TargetFolder & LocalFileName)
Next Hyperlink
End Sub

'   This section downloads and saves the files to the folder specified at the beginning.

Sub HTTPDownloadFile(ByVal URL As String, ByVal LocalFileName As String)
Dim Res As Long
On Error Resume Next
Kill LocalFileName
On Error GoTo 0
Res = URLDownloadToFile(0&, URL, LocalFileName, 0&, 0&)
End Sub