Sharing some useful tips, solutions and notes for Geeks.

Wednesday, June 12, 2013

Download images and assign names using macros

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

Dim Ret As Long

'~~> This is where the images will be saved. Change as applicable
Const FolderName As String = "E:\SHOP_PROCESSING_FILES\image\images\"

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    '~~> Name of the sheet which has the list
    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow '<~~ 2 because row 1 has headers
        strPath = FolderName & ws.Range("A" & i).Value & ".jpg"

        Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, strPath, 0, 0)
    Next i
End Sub

Using above VB, u can easily download images to your desired folder from image url saved in excel.
Excel should be in below format to work properly.

Column A                                 Column B                                   
Image name 1                           Image url 1                                 
Image name 2                           Image url 2                                       :                                  :
     :                                  :

So using above VB, u can download image url 1, image url 2, etc and assign corresponding names in column A.
To download more columns, ie column c, column d etc and assign to name in column A, can be easily done by copy pasting For i-2  .... strPath, 0 0). please remember to change range B to C so that macro can identify your new column.

Hope this help someone..


(Never leave me alone.. Comments expected.. Also queries..)

No comments: