CODEWITHSUNDEEP
excelvbaimage
Frank
Frank

Reputation: 177

GET pictures from a url and then rename the picture

I have a excel list with a lot of article numbers, eg. "23378847". And I want the pictures of all my article numbers in the list stored in my folder.

But the result will be as under. It should be 23378847.jpg not 152499

http://media.byggtjeneste.no/media/bilde/152499/LargeThumbnail
or
http://www.nobb.no/Nobbnr/OrginalBilde/23378847/152499

Is there a way that I can make a scrips that read my file and save the pic with the same article number as in the list?

Upvotes: 10

Views: 36040

Answers (2)

btargac
btargac

Reputation: 402

For those who don't want to deal with VBA or any other programming language there is a desktop web app that makes it super simple.

Just drop in the excel file, it will download all the images (or files) in an excel file to the folder you select, and if there are names on the B column it will also rename the file.

The latest release can be found on https://github.com/btargac/excel-parser-processor.

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149295

Here is a sample which will help you.

I am assuming that your Excel file will look like this. Please amend the code as applicable.

enter image description here

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:\Temp\"

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

Upvotes: 27

Related Questions