Save and Rename PDF via URL

craig80

Board Regular
Joined
May 18, 2010
Messages
51
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
ABCDEFG
1Account_NumberDocument_NumberPDF_DownloadFull_NameCreated_and_saved
29011992769409148www.bbc.co.uk/9409148901199276_9409148.PDFYeswww.bbc.co.uk/
39012001939409947www.bbc.co.uk/9409947901200193_9409947.PDFnnnn
49012060969409626www.bbc.co.uk/9409626901206096_9409626.PDF
59012301089427687www.bbc.co.uk/9427687901230108_9427687.PDF
69012328779437042www.bbc.co.uk/9437042901232877_9437042.PDF
79012331199437062www.bbc.co.uk/9437062901233119_9437062.PDF
89012391929437150www.bbc.co.uk/9437150901239192_9437150.PDF
Cross_Check
Cell Formulas
RangeFormula
C2:C8C2=HYPERLINK(HyperLink&[@[Document_Number]])
D2:D8D2=[@[Account_Number]]&"_"&[@[Document_Number]]&".PDF"
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top