Grab file size from URL

[YasserKhalil]

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]

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]

That's wonderful and great Mr. Kenneth
Thank you very much for this fascinating solution

[YasserKhalil]

I need to convert bytes to kilobytes. Is there UDF function that do that?

[Aussiebear]

Do you know how many bytes there are in a kilobyte?

[YasserKhalil]

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]

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]

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