Downloading dynamic data monthly

[mickeystanford_alumni]

Hi guys,

Happy to be back. Hope you all having a good start of Summer.

I am wondering if someone could help me on the following.
I am trying to download data from a website where basically first I have to select the data and then I can download it on excel/pdf etc. The website is: USDA/NASS QuickStats Ad-hoc Query Tool

I will need to automate and retrieve this data weekly/monthly and wondering if there would be any code to quickly do this. If for example I want to retrieve USDA/NASS QuickStats Ad-hoc Query Tool from this link

Also, might be impossible but in order not to have to go to the 1st link, then click on the desired products, year, etc...is there any way to put a vba code which automatically chooses the data I want? Impossible right?

Thanks a lot and hope my explanation was clear.

Mike

 

[Colo]

The first one worked on me, but I added an underscore "_" after "NATIONAL".
Btw, I made another sample code for using a common routine.

Sub Test()
'Run this procedure
'Sample code for giving arguments to the common routine.
    Call GetCsvViaApi("Pattern1.csv", "SURVEY", "CATTLE", "PRODUCTION", "NATIONAL", "2020")
    Call GetCsvViaApi("Pattern2.csv", "SURVEY", "CHICKENS", "PRODUCTION", "NATIONAL", "2020")
    MsgBox "Done"
End Sub


Sub GetCsvViaApi( _
    ByVal strFILE_NAME As String, _
    ByVal strSource_desc As String, _
    ByVal strCommodity_desc As String, _
    ByVal strStatisticcat_desc As String, _
    ByVal strAgg_level_desc, _
    ByVal strYear_)

'common routine

'Tools > Reffer > Microsoft WinHTTP Services, version X.X (my case vers 5.1)

'https://quickstats.nass.usda.gov/api
'Request API key then replace it with the following sample key.
'Note, the sample API key will not be available soon.

    Const key As String = "FD34620D-962B-3862-9B3D-D431625C8EDE"    'sample API key
    Const strFILE_PATH As String = "C:\Temp\"    'Need to prepare a folder named Temp in C drive
    Dim intFF As Integer    'free file
    Dim wbCSV As Workbook

    Dim Req As WinHttpRequest
    Set Req = New WinHttpRequest

    Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & key & _
                    "&source_desc=" & strSource_desc & _
                    "&commodity_desc=" & strCommodity_desc & _
                    "&statisticcat_desc=" & strStatisticcat_desc & _
                    "&agg_level_desc=" & strAgg_level_desc & _
                    "&year_=2020" & strYear & _
                    "&format=csv"
    Req.send

    intFF = FreeFile

    Open strFILE_PATH & strFILE_NAME For Output As #intFF
    Print #intFF, Req.responseText
    Close #intFF

    Set wbCSV = Workbooks.Open(strFILE_PATH & strFILE_NAME)

    'you can do whatever you want.
End Sub

 

[mickeystanford_alumni]

The first one worked on me, but I added an underscore "_" after "NATIONAL".
Btw, I made another sample code for using a common routine.

Sub Test()
'Run this procedure
'Sample code for giving arguments to the common routine.
    Call GetCsvViaApi("Pattern1.csv", "SURVEY", "CATTLE", "PRODUCTION", "NATIONAL", "2020")
    Call GetCsvViaApi("Pattern2.csv", "SURVEY", "CHICKENS", "PRODUCTION", "NATIONAL", "2020")
    MsgBox "Done"
End Sub


Sub GetCsvViaApi( _
    ByVal strFILE_NAME As String, _
    ByVal strSource_desc As String, _
    ByVal strCommodity_desc As String, _
    ByVal strStatisticcat_desc As String, _
    ByVal strAgg_level_desc, _
    ByVal strYear_)

'common routine

'Tools > Reffer > Microsoft WinHTTP Services, version X.X (my case vers 5.1)

'https://quickstats.nass.usda.gov/api
'Request API key then replace it with the following sample key.
'Note, the sample API key will not be available soon.

    Const key As String = "FD34620D-962B-3862-9B3D-D431625C8EDE"    'sample API key
    Const strFILE_PATH As String = "C:\Temp\"    'Need to prepare a folder named Temp in C drive
    Dim intFF As Integer    'free file
    Dim wbCSV As Workbook

    Dim Req As WinHttpRequest
    Set Req = New WinHttpRequest

    Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & key & _
                    "&source_desc=" & strSource_desc & _
                    "&commodity_desc=" & strCommodity_desc & _
                    "&statisticcat_desc=" & strStatisticcat_desc & _
                    "&agg_level_desc=" & strAgg_level_desc & _
                    "&year_=2020" & strYear & _
                    "&format=csv"
    Req.send

    intFF = FreeFile

    Open strFILE_PATH & strFILE_NAME For Output As #intFF
    Print #intFF, Req.responseText
    Close #intFF

    Set wbCSV = Workbooks.Open(strFILE_PATH & strFILE_NAME)

    'you can do whatever you want.
End Sub

That's actually great. Both ways work indeed, this one easier to then add new categories I find.

Can I ask 1 more please?
--> the code above gives you two csv's, pattern 1 and 2. What about putting all in the same excel sheet so CATTLE goes there, then CHICKENS go to the last row after CATTLE...etc (in case I add more)

Thank you so much again. Will mark the above one as solution as well.

 

[Colo]

Hi,

I’m out of office and no excel here with me but It’s easy. Just change

For Output As #intFF

To

For Append As #intFF
Then make the file name of csv same.

 

[mickeystanford_alumni]

Hi,

I’m out of office and no excel here with me but It’s easy. Just change

For Output As #intFF

To

For Append As #intFF
Then make the file name of csv same.

Hi Colo, thought on that at once but it didn't really worked then I asked...
No worries if out of office rn, let me know once you have some time. Have tried a couple of other possibilities leaving spaces on the file, not liking it...
And again, thank you so much man.

 

[Colo]

Hi,
It seems I forgot to change the file path in the code.
Give "Test2" a try!

Const strFILE_PATH As String = "C:\Temp\"    'Need to prepare a folder named Temp in C drive
Const strFILE_NAME = "Pattern1.csv"    'You can change here

Sub Test2()

    Dim wbCSV As Workbook

    Kill strFILE_PATH & strFILE_NAME    'Delete the CSV file

    Call GetCsvViaApi(strFILE_NAME, "SURVEY", "CATTLE", "PRODUCTION", "NATIONAL", "2020")
    Call GetCsvViaApi(strFILE_NAME, "SURVEY", "CHICKENS", "PRODUCTION", "NATIONAL", "2020")

    Set wbCSV = Workbooks.Open(strFILE_PATH & strFILE_NAME)

    'You can do what you like for the generated CSV file.
End Sub


Sub GetCsvViaApi( _
    ByVal strFILE_NAME As String, _
    ByVal strSource_desc As String, _
    ByVal strCommodity_desc As String, _
    ByVal strStatisticcat_desc As String, _
    ByVal strAgg_level_desc, _
    ByVal strYear_)

'Tools > Reffer > Microsoft WinHTTP Services, version X.X (my case vers 5.1)

'https://quickstats.nass.usda.gov/api
'Request API key then replace it with the following sample key.
'Note, the sample API key will not be available soon.

    Const key As String = "FD34620D-962B-3862-9B3D-D431625C8EDE"    'sample API key
    Dim intFF As Integer    'free file
    Dim Req As WinHttpRequest

    Set Req = New WinHttpRequest

    Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & key & _
                    "&source_desc=" & strSource_desc & _
                    "&commodity_desc=" & strCommodity_desc & _
                    "&statisticcat_desc=" & strStatisticcat_desc & _
                    "&agg_level_desc=" & strAgg_level_desc & _
                    "&year_=" & strYear & _
                    "&format=csv"
    Req.send

    intFF = FreeFile

    Open strFILE_PATH & strFILE_NAME For Append As #intFF
    Print #intFF, Req.responseText
    Close #intFF
End Sub

 

[mickeystanford_alumni]

Fantastic!
Thanks a lot Colo.