Trying to Do my first API with VBA

[Photomofo]

I'm trying to translate some Python code into VBA. I've tried adapting multiple VBA codes relating to APIs but nothing is going through. Can someone point me in the right direction.

<code style="box-sizing: border-box; font-size: inherit; padding: 0px; color: inherit; background-color: transparent; border-radius: 0px; line-height: 12px;">import requests

url = "http://developer.nrel.gov/api/solar/nsrdb_psm3_download.json?api_key=yourapikeygoeshere"

payload = "names=2012&leap_day=false&interval=60&utc=false&full_name=Honored%2BUser&email=honored.user%40gmail.com&affiliation=NREL&mailing_list=true&reason=Academic&attributes=dhi%2Cdni%2Cwind_speed%2Cair_temperature&wkt=MULTIPOINT(-106.22%2032.9741%2C-106.18%2032.9741%2C-106.1%2032.9741)"

headers = {
'content-type': "application/x-www-form-urlencoded",
'cache-control': "no-cache"
}

response = requests.request("POST", url, data=payload, headers=headers)

print(response.text)</code>

 

[hateme28]

I’d recommend looking into winhttprequest for vba. That should get you started.

 

[Photomofo]

Thanks... It turns out the URL string I was using was the problem. DOH!

 

[Photomofo]

Here's the code I put together. It works OK but it's running 3 to 5 times slower than I expected. Any obvious reason why?

 Option Explicit


Sub GET_NSRDB()


Dim Latitude As String
Dim Longitude As String
Dim Year_x As Integer
Dim Attributes As String
Dim i As Long
Dim Latitudes As Variant
Dim Longitudes As Variant


Dim URL As String
Dim URL1 As String
Dim URL2 As String
Dim ARG As String
Dim WinHttpReq As Object
Dim oStream As Object


Longitudes = Sheets("Solar Plants").Range("I1:I2257")
Latitudes = Sheets("Solar Plants").Range("J1:J2257")
Attributes = "attributes=air_temperature,dew_point,dhi,dni,ghi,relative_humidity,surface_albedo,wind_direction,wind_speed&leap_day=false&utc=false&interval=30"


For i = 2 To 2257


    Longitude = Longitudes(i, 1)
    Latitude = Latitudes(i, 1)


    For Year_x = 1995 To 2017
        
        ARG = "C:\Users\XYZ\Desktop\NSRDB\" & Latitude & "_" & Longitude & "_" & Year_x & ".csv"


        If Dir(ARG) <> "" Then
        
            'Skip
        
        Else
        
            URL1 = "https://developer.nrel.gov/api/solar/nsrdb_psm3_download.csv?api_key=XYZ&full_name=XYZ&email=XYZ&"
            URL2 = "wkt=POINT(" & Latitude & "+" & Longitude & ")&names=" & Year_x & "&" & Attributes
            URL = URL1 + URL2


            Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
            WinHttpReq.Open "GET", URL, False, "username", "password"
            WinHttpReq.send
            
            URL = WinHttpReq.responseBody
            If WinHttpReq.Status = 200 Then
                Set oStream = CreateObject("ADODB.Stream")
                oStream.Open
                oStream.Type = 1
                oStream.Write WinHttpReq.responseBody
                oStream.SaveToFile ARG, 2 ' 1 = no overwrite, 2 = overwrite
                oStream.Close
            End If
            
            Application.Wait Now + TimeValue("00:00:01")
        
        End If
        
    Next Year_x
    
Next i


End Sub

 

[hateme28]

The application wait is probably what’s doing it.

 

[Photomofo]

Thanks hateme28... I have to insert the 1 second delay because the server only lets you make a call once a second. I've run this program from work and home and in both cases the speed is about the same. I've fixed the code some to eliminate bad calls but this hasn't improved the speed. Basically I think the server is the choke point. This particular data set (the National Solar Radiation Database) is serving a ton of data so I think they've got things set up to budget the load. But again... Thanks...

You guys here on Mr. Excel are awesome. I've come a long way with Excel/VBA thanks to your help.