Save PDF from URL in Excel

[ForrestGump01]

Hello all,
It's been a long time since I've used any VBA and it's not coming back like riding a bicycle... Hoping for some help on hopefully easy task.

I have a sheet with tens of thousands of rows of invoice data -- each row is a discrete invoice with various columns of information, including a column which includes a URL to a .pdf copy of the invoice. I would like to write a macro that opens the pdf, downloads it, and saves it using a reference to a row/column.

Example: Row 2 is an invoice for XYZCompany Invoice #123456 -- I would like the script to save the file to C:\Desktop\Invoices as "XYZCompany_#123456" and then proceed to the next line of data. I already have a column to concatenate the future file name, I just could use some help with the VBA.

Thanks in advance!

 

[Dan_W]

No problem.

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
    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
#End If

Function Download(ByVal URL As String, ByVal Destination As String) As Long
    Download = URLDownloadToFile(0, URL, Destination, 0, 0)
End Function

This needs to go in its own module, or at the top of one, at least. Then you can call it with Download PDFFilename, DestinationFilename

Does that help?

 

[ForrestGump01]

No problem.

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
    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
#End If

Function Download(ByVal URL As String, ByVal Destination As String) As Long
    Download = URLDownloadToFile(0, URL, Destination, 0, 0)
End Function

This needs to go in its own module, or at the top of one, at least. Then you can call it with Download PDFFilename, DestinationFilename

Does that help?

Thank you!

When you say "Call it" you mean set the Sub declarations?

I've just reorg'd my data. Column A are all the hyperlinks, Column B is the "save as file name". What's the loop I should write to run your recommended code for all the instances in these two columns and save to C:\Desktop\Invoices?

 

[Dan_W]

Thank you!

When you say "Call it" you mean set the Sub declarations?

I've just reorg'd my data. Column A are all the hyperlinks, Column B is the "save as file name". What's the loop I should write to run your recommended code for all the instances in these two columns and save to C:\Desktop\Invoices?

I am so very sorry - I am certain that I responded to this because I remember writing the a short piece of code for you because it was straight forward. I can't apologise enough/ Were you able to work it out? Do you need any help with it now?