Excel macro/vba that will download url image to folder on desktop with file name

[jbrig]

[h=2][/h]I'm looking for help with a macro that will download a url image to a folder on the desktop and rename each image from another column. All image urls are in column B and the name for each would be in column A. Thanks to anyone that can help!!

 

[jkpieterse]

This should do nicely:

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
Private Sub DownloadFile(strWebFilename As String, strSaveFileName As String)
' Download the file.
     URLDownloadToFile 0, strWebFilename, strSaveFileName, 0, 0
End Sub

Sub Demo()
    Dim oCell As Range
    Dim sPath As String
    sPath = "c:\users\yourUserName\desktop\FolderName\"
    For Each oCell In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("B:B"))
        DownloadFile oCell.Value, oCell.Offset(, 1).Value
    Next
End Sub

 

[jbrig]

I am very new to this so it might be something I did wrong. But I am getting this error in this section - Compile error: Only comments may appear after End Sub, End Function, or End Property

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

 

[jkpieterse]

The declaration must be done at the top of a module. The remainder of the code can go anywhere in a module.

 

[jayymehta]

This function API don't work for me. It shows error about 64- bit computer. Is there any other solution except using this API..??

 

[jkpieterse]

Update the declaration to this:

#If VBA7 Then
Private Declare PtrSafe 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
 #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

 

[jayymehta]

Thanks, it worked for me!