Excel – way to “save as” a pdf linked by hyperlink in an excel file

microsoft excelpdf

I am tasked with downloading hundreds of pdfs that are linked via hyperlink in a giant excel file. As of now, I click the hyperlink, which opens the file in adobe reader in internet explorer, and save the file to a folder on my computer. Is there a way to click the hyperlink within excel and "save as," instead of going through the tedious process of opening and saving each pdf, one by one? I apologize if this question seems a bit stupid or vague, I'm not tech-savvy at all!

Best Answer

I'll get you part way there. There isn't enough info to do it all but it would be easy to look up the rest.

This uses vba code to do what you requested.

Below is code from vbaexpress written by Mvidas. It takes an internet address and saves it to a local file.

Option Explicit 
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean 
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte 

     'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP") 
    oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
    oXMLHTTP.Send 'send request

     'Wait for request to finish
    Do While oXMLHTTP.readyState <> 4 
        DoEvents 
    Loop 

    oResp = oXMLHTTP.responseBody 'Returns the results as a byte array

     'Create local file and save results to it
    vFF = FreeFile 
    If Dir(vLocalFile) <> "" Then Kill vLocalFile 
    Open vLocalFile For Binary As #vFF 
    Put #vFF, , oResp 
    Close #vFF 

     'Clear memory
    Set oXMLHTTP = Nothing 
End Function

A macro to test it with

Sub TestingTheCode() 
     'This will save the Google logo to your hard drive, insert it into the
     ' active spreadsheet, then delete the local file
    SaveWebFile "http://www.google.com/intl/en/images/logo.gif", "C:\GoogleLogo.gif" 
    ActiveSheet.Pictures.Insert "C:\GoogleLogo.gif" 
    Kill "C:\GoogleLogo.gif" 
End Sub

With this function you need to setup a loop and get the address from your hyperlinks

You would need to setup a loop to go through your cells,Get the hyperlink address and run the function

For i = 1 to lastRow
    cellAddress = Replace(Range("A" & i).Hyperlinks(1).Address, "mailto:", "")
    'Something to get the file name from the whole file name here
    SaveWebFile cellAddress, destinationFolder & filename
Next
Related Question