File size of web file via url

[Guest]

Is there anyway, programmatically with VBA in Excel 2003, that I can retrieve
the filesize of a file on our intranet when the file can only be accessed via
its URL?

I've tried using the FileSystemObject, FileLen, BuiltinDocumentProperties,
but nothing seems to work when the path to the file is a URL.

Thanks!

 

[Guest]

Anyone have any suggestions?

 

[Tim Williams]

Use xmlhttp and a "HEAD" request. Part of the response is the file size.

'***********************************************
Sub Tester()
Msgbox FileSize("http://www.joelonsoftware.com/i/head.jpg")
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
'***********************************************




Tim