How to fetch size of file in excel?

Question

I have sheet with lot of data in one cell and URLs of documents in next cell. I want to fetch the size of document. Is it possible in Excel?

For example:

--------------------------------------------
| Cell A                      | Cell B     |
|------------------------------------------|
| http://m---.com/myfile.txt  |   4kbs     |
|------------------------------------------|
| http://m---.com/myfile.txt  |   10kbs    |
|------------------------------------------|
| http://m---.com/myfile.doc  |  104kbs    |
|------------------------------------------|
| http://m---.com/myfile.jpg  |   102kbs   |
|------------------------------------------|

I just have URL lists in column A and I want to have file size in column B.

I tried VBA code for this but its not working. One more thing, I just got this code from internet and its not mine, but it didn't work. Here is the code:

print file path Cells(i + 1, 1) = objFile.Path 'print size Cells(i + 1, 2) = objFile.Size

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"