Download excel file from web

[ceptor54]

Hi,

New to the site and relatively new to Access.

I have a DB that I have created that correlates data from several sources.

These are currently downloaded manually and I was looking for a way to get access to do it for me.

I basically need to download the file from

"domain.com/feed/Feed_File.xlsx"

and save it to "C:\Users\Bob\Desktop\downloadedfile.xlsx"

I have found a couple of examples online however they do not appear to wk on a 64bit version of Access.

Any help or guidance would be appreciated.

 

[arnelgp]

here try this, put in separate module.

to use:

call DownloadBinary("domain.com/feed/Feed_File.xlsx", "C:\Users\Bob\Desktop\downloadedfile.xlsx")

Public Function DownloadBinary( _
   src As String, _
   dest As String, _
   Optional TimeoutMS As Long = 45000, _
   Optional ByRef Header As String _
   ) As Long
 'Currently provides no validation for src or dest
 'prelim version
 'returns 0 on success
 'returns -1 on timeout
 'returns httpRequestStatus on other errors
 
 Const HTTPREQ_TIMEOUT_CHECK = 50
 
 Dim req As Object
 Dim lTimer As Long
 Dim bFlag As Boolean
 Dim bTimeout As Boolean
 
 Dim vBytes As Variant
 Dim bBytes() As Byte
 
 Dim iFile As Integer
 
 Set req = CreateObject("MSXML2.XMLHTTP.3.0")
 req.Open "GET", src, True
 req.Send
 
 'timeout
 While bFlag = False
   DoEvents: DoEvents: DoEvents
   If req.ReadyState <> 4 Then
     'not done
     If lTimer >= TimeoutMS Then
       bFlag = True
       bTimeout = True
     End If
   Else
     bFlag = True
   End If
   Sleep HTTPREQ_TIMEOUT_CHECK
   lTimer = lTimer + HTTPREQ_TIMEOUT_CHECK
 Wend

 If bTimeout Then
   DownloadBinary = -1 'timeout
 Else
   If req.Status = 200 Then
     
     Header = req.GetAllResponseHeaders()
   
     vBytes = req.ResponseBody

     ReDim bBytes(0 To UBound(vBytes))
     bBytes = vBytes
     
     iFile = FreeFile()
     Open dest For Binary Access Write As #iFile
     Put #iFile, , bBytes
     Close #iFile
     
     DownloadBinary = 0
     
   Else
     DownloadBinary = req.Status
   End If
 End If
 
 Set req = Nothing
 
End Function

 

[ceptor54]

Thank you for your reply.

How do I call DownloadBinary("domain.com/feed/Feed_File.xlsx", "C:\Users\Bob\Desktop\downloadedfile.xlsx")

Apologies for the added assistance required. Have no experience with the VBA/modules side of Access at all.

 

[arnelgp]

if you have pasted the code in a module, you can make another private sub in that module to test it:

private sub test()
Call DownloadBinary("domain.com/feed/Feed_File.xlsx", "C:\Users\Bob\Desktop\downloadedfile.xlsx")
end sub

while the cursor is inside the Sub Test, press F5 to execute the sub.

 

[ceptor54]

Ok I have done that however I get a compile error sub or Function not defined

The word Sleep is highlighted

Sleep HTTPREQ_TIMEOUT_CHECK

 

[arnelgp]

oh, put again in separate module:

#If Win64 Then
Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongLong
#Else
Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If

Public Sub Sleep(dwMilliseconds As Long)
#If Win64 Then
Dim Time As LongLong
#Else
Dim Time As Long
#End If
Time = GetTickCount
Do Until GetTickCount - Time = dwMilliseconds
DoEvents
Loop
End Sub