Excel Vb for bulk image download and renaming.

September 17, 2018 at 07:54:20
Specs: Windows 8
I need a help on excel vb that will download bulk images each having link address such as "https://drive.google.com/open?id=1jxs_WRsxwtbdibXCQvZ5O35islp3ELEB" given in a cell in a spreadsheet in google forms and rename this image with a string ("12345678") in another cell in the sheet such that the downloaded image will have the name 12345678.jpg.


message edited by Thomas Armah


See More: Excel Vb for bulk image download and renaming.

Reply ↓  Report •

#1
September 19, 2018 at 00:52:31
I'd suggest looking into the URLDownloadToFile API:

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

szURL is the URL to download, szFileName is the local filename to save to. (pCaller, dwReserved, and lpfnCB should all be 0.)


Reply ↓  Report •

#2
September 19, 2018 at 05:12:33
Dear Eriksiers,

Thanks for your reply but can you review this script am using. It rather creates empty files.


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 = "C:\Records\"

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)

        If Ret = 0 Then
            ws.Range("C" & i).Value = "File successfully downloaded"
        Else
            ws.Range("C" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub


message edited by Thomas Armah


Reply ↓  Report •
Related Solutions


Ask Question