Copy URL Images from Excel column to local folder

Press ATL+F11

Insert This code

Sub DownloadFileFromURL()

     Dim ws As Worksheet

     Dim FileUrl As String

     Dim objXmlHttpReq As Object

     Dim objStream As Object

     Dim DownloadFolder As String

     DownloadFolder = "Local_PC_Folder_Name_Here"

 

' Number of rows to process in For loop from starting row     

 For i = 2 To 319

   ' Column address of URL  - in this example cell address L

    FileUrl = Range("L" & i).Value

     Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")

     objXmlHttpReq.Open "GET", FileUrl, False, "username", "password"

     objXmlHttpReq.send

     If objXmlHttpReq.Status = 200 Then

          Set objStream = CreateObject("ADODB.Stream")

          objStream.Open

          objStream.Type = 1

          objStream.Write objXmlHttpReq.responseBody

         ' Relevant file name for storing the file read from column cell - in this example cell address A

          objStream.SaveToFile DownloadFolder & "\" & Range("A" & i).Value & ".jpg", 2

          objStream.Close

     End If

Next i

End Sub