VBA: download file without extension, get extension and add it to filename

Question

This VBA script uses column A as filename, B as image url and adds a ".jpg" as extension.

Problem is that many files are not in jpg format, so it's best to consider them having an unknown extension.

Is it possible to tweak the script so that it gets the real file extension before saving the image and add it to filename instead of the user defined ".jpg" ?

The Script

Option Explicit
'~~> This macro downloads images from urls. Column A=image title, Column B=image URL.
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:\Users\plus\Desktop\INPUT\"

Sub DOWNLOAD_image_XLS()
'~~> This is where text is divided into 2 columns right down the "|" delimiter
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1") _
                      , DataType:=xlDelimited _
                      , Other:=True _
                      , OtherChar:="|"
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    Set ws = ActiveSheet

    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 = "OK"
        Else
            ws.Range("C" & i).Value = "Failed!"
        End If
    Next i
    End Sub

Where is the "real file extension" to be found? Is it part of the link? Then you can easily extract it from there using `split` or `mid` together with `InStrRev`. Or do you want to write code which examines the files and looks for possible headers which identify the files? — Ralph, May 11 '16 at 11:39
@Ralph The real extension is not specified, that's the problem, most files are jpg, many png and some gif. The ideal solution would be the latter you mentioned, to have the script examine the file and get its extension. Is that doable in VBA? — Remus Sarnus, May 11 '16 at 11:53
As mentioned [in this post](http://stackoverflow.com/questions/55869/determine-file-type-of-an-image) it would be much easier to do this with existing libraries. Yet, you can certainly achieve this also with VBA only. But if you want to take this route then you'll have to write the code yourself first (while we can help you debug it) or pay someone to do this for you. Alternatively, just get a new picture viewing program which do that for you (recognize pictures regardless of their extensions). — Ralph, May 11 '16 at 12:22
For a `.net` implementation in Excel using VBA (as suggested in one of the answers to the above referenced question) you might want to have a look at the following: http://www.codeproject.com/Articles/555660/Extend-your-VBA-code-with-Csharp-VB-NET-or-Cpluspl — Ralph, May 11 '16 at 12:24

score 2 · Answer 1 · answered May 11 '16 at 13:26

2

One way would be to parse the Content-Type from the response:

Sub DownloadLink()

  Const imageLink = "https://i.stack.imgur.com/9w2PY.png?s=32"
  Const filePath = "C:\Temp\myimage"

  Dim req As Object, content() As Byte, extension$

  ' send the request
  Set req = CreateObject("Msxml2.ServerXMLHTTP.6.0")
  req.Open "GET", imageLink, False
  req.Send

  ' get the extension and data
  extension = "." & Split(req.getResponseHeader("Content-Type"), "/")(1)
  content = req.responseBody

  ' write the file
  Open filePath & extension For Binary Access Write As #1
      Put #1, 1, content
  Close #1

End Sub

answered May 11 '16 at 13:26

Florent B.

41,537
7
86
101

Genius! It gets the correct extension. I'll try my best to "merge" the 2 scripts together and have yours fetch image links from my csv file. THX! – Remus Sarnus May 11 '16 at 14:06
Hmm, I can't code, can't even read it =D. I ended up downloading images and saving them without extension, then add file extensions using a TrID batch. Two steps instead of one, but it does the job. – Remus Sarnus May 14 '16 at 18:00

VBA: download file without extension, get extension and add it to filename

1 Answers1