0

I adopted code from How do I download a file using VBA (without Internet Explorer). It works fine unless when there is no answer from the device. I will get an error ("-2146697211, The system cannot locate the resource specified.") after approx 18 seconds, but during that time the PC is almost dead.

As the device is local I think a timeout of 300..500 ms will do.

Function netDownloadFile(ByVal sURL As String, _
                        ByVal sLocalFile As String, _
                        ByRef pCallbackFunc As Long, _
                        ByRef uTimeoutMillis As Long) As Long
' https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer
  On Error GoTo Err_netDownloadFile
Dim oStream As Object
Dim WinHttpReq As Object
  netDownloadFile = 0
  Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
  WinHttpReq.SetTimeouts uTimeoutMillis, uTimeoutMillis, uTimeoutMillis, uTimeoutMillis
  WinHttpReq.Open "GET", sURL, False
  'WinHttpReq.Open "GET", sURL, False, "username", "password"
  WinHttpReq.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"     'disallow caching to get realtime results
  WinHttpReq.send
  
  netDownloadFile = WinHttpReq.status
  Debug.Print WinHttpReq.getAllResponseHeaders
  If WinHttpReq.status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile sLocalFile, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
    GoTo Exit_netDownloadFile
  Else
    gErrDescription = WinHttpReq.getAllResponseHeaders
  End If

Exit_netDownloadFile:
  Exit Function
  
Err_netDownloadFile:
  netDownloadFile = Err.Number
  gErrDescription = Err.Description
  Resume Exit_netDownloadFile
End Function

The call to this will be:

lngDownloadResult = netDownloadFile("http://192.168.56.42/status/meters", "C:\Temp\Jacuzzi.txt", 0, 300)

I'll get a runtime-error for WinHttpReq.SetTimeouts: "438 - Object doesn't support this property or method". So this seems not supported by Microsoft.XMLHTTP

How do I need to recode (or to use which library), to use a short timeout?

Blacksmith
  • 27
  • 7
  • Can this help https://stackoverflow.com/questions/11407010/how-to-vba-callback-function-when-xmlhttp-ontimeout? – jbud Sep 18 '20 at 16:29
  • [WinHttpRequest](https://learn.microsoft.com/en-us/windows/win32/winhttp/winhttprequest) supports `SetTimeouts`, but of course an [async request](http://dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/) prevents system not responding as jbud suggest. You may also consider using the command-line (Shell/WScript.Shell) with [curl](https://ec.haxx.se/usingcurl/usingcurl-downloads) or [wget](https://stackoverflow.com/questions/16678487/wget-command-to-download-a-file-and-save-as-a-different-filename) – ComputerVersteher Sep 19 '20 at 06:16
  • @ComputerVersteher - [WinHttpRequest](https://learn.microsoft.com/en-us/windows/win32/winhttp/winhttprequest) seems to be based on ActiveX. I thought that is outdated. Further I'm not sure if that really works from VBA as it is a COM object. Is using curl or wget really a good idea? First cmd is invoked and an afterwards an executable loaded? Seems a horrible overhead just to download a page! – Blacksmith Sep 19 '20 at 10:28
  • As long as you don't need any of the missing features, that are available in `XmlHttp` always use `WinHttp`! See [WinINet vs. WinHTTP](https://learn.microsoft.com/en-us/windows/win32/wininet/wininet-vs-winhttp) – ComputerVersteher Sep 19 '20 at 10:43
  • Using curl (build-in on win10) or wget, saves you lots of coding (look at their features) and they are reliable. – ComputerVersteher Sep 19 '20 at 10:47

0 Answers0