Follow, download, and rename hyperlinked files?

**robbyvegas** · 03-28-2013, 08:19 PM

Hi all,

I have a spreadsheet with 5 columns, the last column being a hyperlink to a PDF stored on the internet. I'd like to automatically download each PDF, and to rename the downloaded PDF to include the text in the first column. An example is attached.

Could someone please help me figure out how to do this?

Thanks,

Rob

**mrice** · 03-29-2013, 02:49 PM

Try this

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 DownloadFilesfromWeb()
    Dim URL As String, ext As String
    Dim buf, ret As Long, N As Long
    Const strSavePath = "C:\test\" '******  Edit this to reflect your chosen storage location for the local copies.
    
    For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        URL = Cells(N, 5).Hyperlinks(1).Address
        Filename = WorksheetFunction.Substitute(Cells(N, 1), "/", " ") & ".pdf" '*** to remove illegal characters from file name
        ret = URLDownloadToFile(0, URL, strSavePath & Filename, 0, 0)
    Next N
End Sub

Credit for the inspiration goes to http://www.mrexcel.com/forum/excel-q...te.htmlhttp://