10

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?

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Frank
  • 167
  • 1
  • 3
  • 9
  • ugh...My english is bad...sorry for that :) Its not all right. In my list i got my numbers, that url have the pictures and i want to download all the pictures to my folder. But as you see the site have a weerd ending/renaming (152499) that gives me problem. – Frank Apr 30 '12 at 12:39
  • They say that i need to use "GET /ProduktInfo.asmx/HentBildeLenke?sModulNr=string&sBilledStorrelse=string HTTP/1.1 Host: produktinfo.byggtjeneste.no" and so on. Btw: Thanks for your time Shegit :) – Frank Apr 30 '12 at 12:43
  • Okay, from whereever you got some help, that's nice. For your question here you should rewrite it to make clear what you mean. Place it inside your question, not only inside your comments. – Shegit Brahm Apr 30 '12 at 13:42

2 Answers2

27

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Hi Siddharth! VERY nice :) No my list is without the links...Only 23378847 and so on in one column. The links need to be inside the macro. – Frank May 02 '12 at 08:55
  • So how will the macro know what the links are for every ID? – Siddharth Rout May 02 '12 at 08:57
  • I make the links with .chain formel (not sure of the EN word for that formel) from column A – Frank May 02 '12 at 09:37
  • 3
    Awesome man @SiddharthRout You should get award for this seriously – Mowgli Feb 04 '13 at 03:43
  • @Mowgli Thank you for appreciating it. It is an award in itself ;) – Siddharth Rout Feb 04 '13 at 04:16
  • @SiddharthRout sorry this is office topic, but can you recommend a best book for VBA? which you use if you use any. – Mowgli Feb 04 '13 at 13:29
  • I don't use any VBA books any more. Many Many Many years ago I started off with john walkenbach's books and I believe he is a nice author :) – Siddharth Rout Feb 04 '13 at 13:34
  • If I want to use the actual file name itself, then what should I do? I dont want a custom file name in my case. @Siddharth Rout – harishannam May 14 '14 at 05:39
  • Super answer, @Siddharth! it's very helped me:) – Dmitry Pavliv Sep 27 '14 at 20:40
  • Hi @SiddharthRout, i know this is an old thread, but i am facing the following problem. Probably you might shed some light on my issue. how to download image in the case where authentication is required i.e. the protocol is https:// ? – sifar Mar 17 '17 at 14:55
3

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.

btargac
  • 392
  • 2
  • 10
  • And even if the URL does not have a file extension in it, this app will read it from the mime type and add a corresponding extension while saving to the filesystem, for example `https://www.buraktarga.com/sample` will be saved as `sample.jpg` or `sample.png` according to the mime type of the server response. – btargac Dec 27 '21 at 07:12