Download file with progress meter in VBA

Question

I've found numerous examples for doing this in several languages, but none that are VBA specific. This question, How to download multiple files in VB6 with progress bar?, addresses three different approaches to do this in VB6.

1. Use the ASyncRead property of the VB6 UserControl/UserDocument objects
2. Use type library olelib.tlb and the IBindStatusCallback interface
3. Use wininet.dll to write your own download to file function

None of these approaches work for me because:

1. The UserControl/UserDocument objects are not available from VBA
2. I'd rather not have to maintain and distribute a large external dependency
3. I did not see an obvious way to get the current file download progress

Number 2 above seemed the most promising. I'm wondering if I can create an IBindStatusCallback interface using a class module from within my VBA project?

Or maybe there are properties/methods available using Number 3 above that would provide the current progress. Any help is much appreciated.

Answer 1

I have done this using the wininet.dll functions. Unfortunately I cannot paste my code as it is owned by my employer.

Use InternetOpen and InternetOpenUrl to start the download, HttpQueryInfoLong to get the content length and then repeatedly call InternetReadFile to read data into a buffer (I use a 128k buffer), writing the data to a file and updating the progress bar as you go.

Declarations to get you started:

Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Private Declare Function HttpQueryInfo Lib "wininet.dll" Alias "HttpQueryInfoA" (ByVal hHttpRequest As Long, ByVal lInfoLevel As Long, ByRef sBuffer As Any, ByRef lBufferLength As Long, ByRef lIndex As Long) As Long
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
Private Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByRef Buffer As Any, ByVal lNumberOfBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternet As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long

Private Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Private Const INTERNET_FLAG_RELOAD = &H80000000
Private Const INTERNET_FLAG_KEEP_CONNECTION = &H400000 ' use keep-alive semantics - required for NTLM proxy authentication
Private Const HTTP_QUERY_CONTENT_LENGTH = 5
Private Const HTTP_QUERY_FLAG_NUMBER = &H20000000

If you need any clarification, post a comment.

Answer 2

You want a progress bar in VBA, wouldn't one of these approaches work?

Progress bar in VBA Excel

Seems a lot simpler than doing it as you describe, or am I not understanding?

OK, try this. Get the headers from the URL and parse them for Content-Length. Then you can set your progress bar accordingly.

Function GetFileSize(URL As String) As Long

Dim xml As Object ' MSXML2.XMLHTTP60
Dim result As String

Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

With xml
  ' get headers only
  .Open "HEAD", URL, False
  .send
End With

result = xml.getResponseHeader("Content-Length")

GetFileSize = CLng(result)

End Function

Now just call the function with the URL of the file you want to download. It should give you the number of bytes of the file.