[API-users] Download ZIP-File via VBA

[Matthew Blissett]

Hi Michael,

It's been a long time since I used VBA, but this routine works when I 
run it in Excel ­— the zip file is downloaded to the local disc:

strDownload = "0211656-200613084148143"
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
strURL_Request = "https://api.gbif.org/v1/occurrence/download/" & 
strDownload
http.Open "GET", strURL_Request, False
http.Send
Debug.Print (http.Status)

' (I have not bothered to parse the JSON output.)
strURL_ZIP = 
"https://api.gbif.org/v1/occurrence/download/request/0211656-200613084148143.zip"
http.Open "GET", strURL_ZIP, False
http.Send
Debug.Print (http.Status)

' From 
https://stackoverflow.com/questions/59598416/vba-download-form-url-with-login-that-redirect-to-another-one
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.write http.ResponseBody
oStream.SaveToFile "C:\Users\…\Documents\download.zip", 2
oStream.Close
Debug.Print ("Completed")

Our servers don't block this query, so I think it's most likely to be a 
restriction on your own network / computer.  Note there is an HTTP 
redirect from the API server to the download server, but WinHttpRequest 
follows this automatically.

Kind regards,

Matt


On 12/05/2021 16:18, Reetz, Michael (NLPV) wrote:
>
> Hello,
>
> I’m trying to query and download occurences with some subs written in 
> VBA. While querying works fine, download leads to errors.
>
> At the end oft he query, I have a download link for a ZIP-archive. 
> Putting this into a browser address field lets me download the 
> archive. Giving it to a VBA-Object returns errors.
>
> E.g.,
>
> Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
>
> strURL_Request = "https://api.gbif.org/v1/occurrence/download/" & 
> strDownload
>
> http.setTimeouts 30000, 30000, 30000, 30000
>
> http.Open "GET", strURL_Request, False
>
> http.send
>
> returns a jason file that contains the download link. I extract this 
> link and use it as parameter in the next  step:
>
> http.Open "GET", strURL_ZIP, False
>
> http.send
>
> This results in a timeout. Using "Microsoft.XMLHTTP" alternativly 
> returns „Access denied“
>
> Is this a problem with our network restrictions or does the GBIF 
> server block the query?
>
> Kind regards
>
> Michael
>
> Mit freundlichen Grüßen
> Im Auftrag
>
> Michael Reetz
>
> *****************************************
> Nationalpark-Verwaltung Niedersächsisches Wattenmeer
> Virchowstr. 1
> 26382 Wilhelmshaven
> Tel.: +49 (0) 4421 911 291
> Fax.: +49 (0) 441 799 61039
> e-mail: michael.reetz at nlpv-wattenmeer.niedersachsen.de 
> <mailto:michael.reetz at nlpv-wattenmeer.niedersachsen.de>
> Web: http://www. 
> <http://www.nationalpark-wattenmeer.niedersachsen.de/>_nationalpark-wattenmeer.de_
>
> Flussgebietsmanagement Übergangs-/Küstengewässer
> NLWKN * Betriebsstelle Brake / Oldenburg
> e-mail: michael.reetz at nlwkn.niedersachsen.de 
> <mailto:michael.reetz at nlwkn.niedersachsen.de>
> Web:__http://www.nlwkn.niedersachsen.de 
> <http://www.nlwkn.niedersachsen.de/>
>
>
> _______________________________________________
> API-users mailing list
> API-users at lists.gbif.org
> https://lists.gbif.org/mailman/listinfo/api-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.gbif.org/pipermail/api-users/attachments/20210512/71202c43/attachment-0001.html>