Excel get dimensions and filesize from jpg url

Question

I have jpg url:

I want to get:

1. the jpg dimensions

2. the jpg file size

like this:

Answer 1

Paste this code in a new module on your Excel file and run the Tester function to test it out.

For testing, replace the URL in the Tester function with a URL of your own to test.

Sub Tester()
MsgBox FileSize("https://www.google.com/images/logo.png")
End Sub

Function FileSize(sURL As String)
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "HEAD", sURL, False
oXHTTP.send
If oXHTTP.Status = 200 Then
FileSize = oXHTTP.getResponseHeader("Content-Length")
Else
FileSize = -1
End If
End Function

Once this is pasted in a module in your Excel fille, you can use it through VBA as shown in the Tester function or you can use it as below:

If column A contains all the URLs, and you want to show the file size in column B, type this in cell B1 and drag it down:

=FileSize(A1)

The above will give the file size in bytes. If you want to display the file size in kilo bytes rounded to 2 decimal places and have "KB" displayed next to the number, you can update the following line of code:

FileSize = oXHTTP.getResponseHeader("Content-Length")

to the new one below:

FileSize = CStr(Round(oXHTTP.getResponseHeader("Content-Length") / 1024, 2)) + " KB"