Auto save PDF file from Internet Explorer window using VBA

Question

I am using below code to automate saving the PDF document from Internet Explorer window. It's working fine, but I want it to be happened for multiple PDF files with multiple URL's. When I give URL's in column A and destination path with file format as .pdf in column B by taking URL from column A and save file with file name from column B.

Option Explicit 

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 z() 

    Dim strSource As String 
    Dim strDest As String 
    strSource = "http://www.cran.r-project.org/doc/manuals/R-intro.pdf" 
    strDest = "c:\temp\blah.pdf" 
    URLDownloadToFile 0, strSource, strDest, 0, 0 

End Sub

You need to read more about VBA - you just need a `For` loop statement to read from cells in column A and also column B ;). — shA.t, Jun 30 '15 at 11:30
No. We do not just "give you the code" here. If you want to *pay* a professional to write the code for you, there are plenty of places where you can do so. — RubberDuck, Jul 02 '15 at 14:56

score 0 · Accepted Answer · answered Jun 30 '15 at 14:45

As @shA.t was saying, all you need to do is to wrap those lines in a For Each loop.

Solution: Let's say, you have your URLs in A1 to A3 and your destinations in B1 to B3. Your Sub z() should look like this:

For Each source in Sheets("Sheet name").Range("A1:A3")
    URLDownloadToFile 0, source.Value, source.Offset(0,1).Value, 0, 0 
Next source

Explanation: For Each loops through all cell elements in range A1 to A3. In each round of the loop, source becomes that cell. Instead of hardcoding the source into your code, you can just refer to source.Value, the contents of the current cell in the loop. For the destination, you can use the .Offset method which references to a neighboring cell by its relative distance to the cell it is being called from. In this case, we want to get from A1 to B1 (and so on), i.e. zero rows down, one column right (Offset(0,1)).

one more task please... i want to give a break for 4 secs for each pdf file to save... can you give me code for that please... — ramesh bethapudi, Jul 07 '15 at 10:45
[How to pause for specific amount of time?](http://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time) — nicolaus-hee, Jul 07 '15 at 13:11

Auto save PDF file from Internet Explorer window using VBA

1 Answers1