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@nlpv-wattenmeer.niedersachsen.de<mailto:michael.reetz@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@nlwkn.niedersachsen.de<mailto:michael.reetz@nlwkn.niedersachsen.de> Web: http://www.nlwkn.niedersachsen.de<http://www.nlwkn.niedersachsen.de/>
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...." http.Open "GET", strURL_ZIP, False http.Send Debug.Print (http.Status) ' From https://stackoverflow.com/questions/59598416/vba-download-form-url-with-logi... 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@nlpv-wattenmeer.niedersachsen.de <mailto:michael.reetz@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@nlwkn.niedersachsen.de <mailto:michael.reetz@nlwkn.niedersachsen.de> Web:__http://www.nlwkn.niedersachsen.de <http://www.nlwkn.niedersachsen.de/>
_______________________________________________ API-users mailing list API-users@lists.gbif.org https://lists.gbif.org/mailman/listinfo/api-users
Hi Matt, thanks for your help. Since I can put the URL in a browsers address field and download a file there without problems, it seems to be a restriction for MS Office and VBA. I've sent a question regarding this problem to our IT department. Best 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@nlpv-wattenmeer.niedersachsen.de<mailto:michael.reetz@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@nlwkn.niedersachsen.de<mailto:michael.reetz@nlwkn.niedersachsen.de> Web: http://www.nlwkn.niedersachsen.de<http://www.nlwkn.niedersachsen.de/> Von: API-users <api-users-bounces@lists.gbif.org> Im Auftrag von Matthew Blissett Gesendet: Mittwoch, 12. Mai 2021 17:56 An: api-users@lists.gbif.org Betreff: Re: [API-users] Download ZIP-File via VBA ACHTUNG!! Diese E-Mail erreicht Sie von einem Absender außerhalb der niedersächsischen Landesverwaltungs-Infrastruktur ohne TLS-Verschlüsselung. Bitte klicken Sie auf keine Links oder öffnen Sie keine E-Mail-Anhänge, falls Sie den Absender nicht kennen und nicht wissen, ob der Inhalt sicher ist. 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/"<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"<https://smex-ctp.trendmicro.com:443/wis/clicktime/v1/query?url=https%3a%2f%2fapi.gbif.org%2fv1%2foccurrence%2fdownload%2frequest%2f0211656%2d200613084148143.zip&umid=80cb9736-359f-40eb-816d-a014d8f099b4&auth=4456e5223a495a0240a5eada979a61a7fdbe9da6-b4c1e3909bf198573829b11759f8dd7372547122> http.Open "GET", strURL_ZIP, False http.Send Debug.Print (http.Status) ' From https://stackoverflow.com/questions/59598416/vba-download-form-url-with-logi... 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/"<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@nlpv-wattenmeer.niedersachsen.de<mailto:michael.reetz@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@nlwkn.niedersachsen.de<mailto:michael.reetz@nlwkn.niedersachsen.de> Web: http://www.nlwkn.niedersachsen.de<http://www.nlwkn.niedersachsen.de/> _______________________________________________ API-users mailing list API-users@lists.gbif.org<mailto:API-users@lists.gbif.org> https://lists.gbif.org/mailman/listinfo/api-users
Dear Matt, I've solved the problem with the help of a colleague. The correct proxy settings where missing. Set http = CreateObject("WinHttp.WinHttpRequest.5.1") http.setProxy 2, strProxy where strProxy contains the proxy string. Best 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@nlpv-wattenmeer.niedersachsen.de<mailto:michael.reetz@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@nlwkn.niedersachsen.de<mailto:michael.reetz@nlwkn.niedersachsen.de> Web: http://www.nlwkn.niedersachsen.de<http://www.nlwkn.niedersachsen.de/> Von: API-users <api-users-bounces@lists.gbif.org> Im Auftrag von Matthew Blissett Gesendet: Mittwoch, 12. Mai 2021 17:56 An: api-users@lists.gbif.org Betreff: Re: [API-users] Download ZIP-File via VBA ACHTUNG!! Diese E-Mail erreicht Sie von einem Absender außerhalb der niedersächsischen Landesverwaltungs-Infrastruktur ohne TLS-Verschlüsselung. Bitte klicken Sie auf keine Links oder öffnen Sie keine E-Mail-Anhänge, falls Sie den Absender nicht kennen und nicht wissen, ob der Inhalt sicher ist. 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/"<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"<https://smex-ctp.trendmicro.com:443/wis/clicktime/v1/query?url=https%3a%2f%2fapi.gbif.org%2fv1%2foccurrence%2fdownload%2frequest%2f0211656%2d200613084148143.zip&umid=80cb9736-359f-40eb-816d-a014d8f099b4&auth=4456e5223a495a0240a5eada979a61a7fdbe9da6-b4c1e3909bf198573829b11759f8dd7372547122> http.Open "GET", strURL_ZIP, False http.Send Debug.Print (http.Status) ' From https://stackoverflow.com/questions/59598416/vba-download-form-url-with-logi... 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/"<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@nlpv-wattenmeer.niedersachsen.de<mailto:michael.reetz@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@nlwkn.niedersachsen.de<mailto:michael.reetz@nlwkn.niedersachsen.de> Web: http://www.nlwkn.niedersachsen.de<http://www.nlwkn.niedersachsen.de/> _______________________________________________ API-users mailing list API-users@lists.gbif.org<mailto:API-users@lists.gbif.org> https://lists.gbif.org/mailman/listinfo/api-users
participants (2)
-
Matthew Blissett -
Reetz, Michael (NLPV)