Save and Rename PDF via URL

[craig80]

Morning Everyone,

I have looked all over for a solution, or even one I can cobble together, but it has been too many years, and it seems a lot has changed in terms of the complexity of offered solutions since I last visited!

I have list of URLs in excel that all have a PDF as an endpoint, I am looking to automate (but control) using excel steps to download, throttle, rename and save in daily download folders.

Sometimes I will have 100 PDFs to rename and save, sometimes 1000s, but so as not to over load the network, I wanted to be able to add a variable so that at some times in the day I could ask it to run X, others Y iterations.

In addition to renaming these files, I would like to save them in the default download location, but within a folder for that day, and subsequent iterations saved in the folder of the day.

My workbook contains 3 named ranges:

1. URL (C:C)
2. New_File_Name (D:D)
3. Row_Limit (G3) (Off page variable)

What I need from a macro is:

1. Check Created_and_Saved is BLANK,
   • If not BLANK, start from row which is first BLANK
2. Download PDF at the endpoint of defined URL on list Range: URL
3. Rename with value in Range: New_File_Name
4. Create download folder with "PDF_YYYYMMDD" format
   • Moved into download folder if folder in 3 already exisits
5. Populate Created_and_Saved with "YES"
6. Preformed a number of times defined in Range: Row_Limit

Hope that all make sense, and would love any assistance anyone can give.

Many thanks
Craig

PDF Convert 0.3.xlsx
	A	B	C	D	E	F	G
1	Account_Number	Document_Number	PDF_Download	Full_Name	Created_and_saved
2	901199276	9409148	www.bbc.co.uk/9409148	901199276_9409148.PDF	Yes		www.bbc.co.uk/
3	901200193	9409947	www.bbc.co.uk/9409947	901200193_9409947.PDF			nnnn
4	901206096	9409626	www.bbc.co.uk/9409626	901206096_9409626.PDF
5	901230108	9427687	www.bbc.co.uk/9427687	901230108_9427687.PDF
6	901232877	9437042	www.bbc.co.uk/9437042	901232877_9437042.PDF
7	901233119	9437062	www.bbc.co.uk/9437062	901233119_9437062.PDF
8	901239192	9437150	www.bbc.co.uk/9437150	901239192_9437150.PDF
Cross_Check

Cell Formulas
Range	Formula
C2:C8	C2	=HYPERLINK(HyperLink&[@[Document_Number]])
D2:D8	D2	=[@[Account_Number]]&"_"&[@[Document_Number]]&".PDF"

 

[Kenneth Hobson]

I don't know what some of your Range: parts as they do not correlate to your column headings.

First off, I would use the API routines for URLDownloadToFile(). IF you are using MAC version, I have no advice for that part.

Try getting that to work first and then post back if more help is needed. Here is one set of API code to do that. Excel & VBA: Download Internet Files Automatically - My Engineering World