Downloading Images from URL and Renaming

Question

I have an excel sheet with 2 columns, A and B. Column A has a name, and column B has the image URL.

I want to download all the images and have them renamed to what's in column A. I've searched on here and it appears that there has been a previous solution, but the code doesn't work on my version of excel/PC as I get an error:

"Compile Error

The code in the project must be updated for use on 64 bit systems. Please review and update Declare statements then mark them with the PtrSafe Attribute".

Here's the previous post: GET pictures from a url and then rename the picture

Would appreciate and love any help regarding this!

@Amorpheuses: replace you "64 bit machine with the latest version of office installed." with "64 bit machine with the latest 32 bit version of office installed." — Axel Richter, Feb 12 '17 at 10:18
Wait, is it possible that I installed the wrong version of office for my Windows version? — Kelvin Chua, Feb 15 '17 at 04:47
I would not call a 64-bit Office version the wrong version for a 64-bit Windows system. But explicitly using `Declare`statements it is different from a 32-bit Version. See [Compatibility Between the 32-bit and 64-bit Versions of Office](https://msdn.microsoft.com/en-us/library/office/ee691831(v=office.14).aspx). @Amorpheuses had stated that it works for him using 64-bit Windows. But that can only be true if he has running 32-bit Office in 64-bit Windows. — Axel Richter, Feb 15 '17 at 05:00
I have provided a `Sub` which does not need system declarations. So this should be independent of whether 32-bit or 64-bit Office is used. — Axel Richter, Feb 15 '17 at 08:25

score 8 · Answer 1 · edited May 23 '17 at 11:45

The following Sub should do the same as the one in GET pictures from a url and then rename the picture. But since it does not uses system functions but only native Excel VBA, it should be independent of whether 32-bit or 64-bit Office is used.

The Sheet1:

The code:

Const FolderName As String = "P:\Test\"

Sub downloadJPGImages()

 Set ws = ActiveWorkbook.Sheets("Sheet1")
 lLastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

 Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
 Set oBinaryStream = CreateObject("ADODB.Stream")
 adTypeBinary = 1
 oBinaryStream.Type = adTypeBinary

 For i = 2 To lLastRow
  sPath = FolderName & ws.Range("A" & i).Value & ".jpg"
  sURI = ws.Range("B" & i).Value

  On Error GoTo HTTPError
  oXMLHTTP.Open "GET", sURI, False
  oXMLHTTP.Send
  aBytes = oXMLHTTP.responsebody
  On Error GoTo 0

  oBinaryStream.Open
  oBinaryStream.Write aBytes
  adSaveCreateOverWrite = 2
  oBinaryStream.SaveToFile sPath, adSaveCreateOverWrite
  oBinaryStream.Close

  ws.Range("C" & i).Value = "File successfully downloaded as JPG"

NextRow:
 Next

 Exit Sub

HTTPError:
 ws.Range("C" & i).Value = "Unable to download the file"
 Resume NextRow

End Sub

Downloading Images from URL and Renaming

1 Answers1

Linked