PDA

View Full Version : Grab file size from URL



YasserKhalil
10-29-2016, 01:39 AM
Hello everyone

In the attachment I have a code for Mr. Leith Ross that downloads the files by their URL in column A and name the downloaded file by column B
I don't need to download the file ..
Just need to grab the file size to column B

Is it possible?

Kenneth Hobs
10-29-2016, 08:46 AM
Yes.


Sub Main()
Dim c As Range, glb_origCalculationMode As Integer

On Error GoTo EndSub
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = "Running Main()..."
.EnableCancelKey = xlErrorHandler
End With

For Each c In Range("A2", Range("A2").End(xlDown))
c.Offset(, 1).Value = FileSize(c.Value2)
Next c

EndSub:
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub


'http://www.pcreview.co.uk/threads/file-size-of-web-file-via-url.3233105/
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

YasserKhalil
10-29-2016, 10:04 AM
That's wonderful and great Mr. Kenneth
Thank you very much for this fascinating solution

YasserKhalil
10-29-2016, 10:16 AM
I need to convert bytes to kilobytes. Is there UDF function that do that?

Aussiebear
10-29-2016, 02:04 PM
Do you know how many bytes there are in a kilobyte?

YasserKhalil
10-29-2016, 02:42 PM
Yes I know 1024 bytes
and I have this formula

=IF(B1>=1048576,ROUND(B1/1048576,2)&" MB",IF(B1>=1024,ROUND(B1/1024,2)&" KB",B1&" bytes"))
But I need UDF that exactly convert bytes to kilobytes

Kenneth Hobs
10-29-2016, 05:21 PM
Function ByteFormat(b As Double) As String
Dim s As String
Select Case True
Case b >= 1024 And b < 1024 ^ 2
s = Round(b / 1024, 2) & " kb"
Case b >= 1024 ^ 2 And b < 1024 ^ 3
s = Round(b / 1024 ^ 2, 2) & " mb"
Case b >= 1024 ^ 3 And b < 1024 ^ 4
s = Round(b / 1024 ^ 3, 2) & " gb"
Case b >= 1024 ^ 4 And b < 1024 ^ 5
s = Round(b / 1024 ^ 4, 2) & " tb"
Case Else
s = b & " bytes"
End Select
ByteFormat = s
End Function

YasserKhalil
10-29-2016, 10:17 PM
Thank you very much Mr. Kenneth for this great support
You are awesome and I like your solutions a lot

To your knowledge, sometimes (not always of course) I have solutions for specific issues but I am seeking all the time to improve my issues and so I posted a lot of threads because I know that I will receive fascinating solutions from such great people like you
Best Regards