Downloading dynamic data monthly

M

mickeystanford_alumni

Board Regular
Joined
May 11, 2022
Messages
129
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
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
 
Sort by date Sort by votes
The first one worked on me, but I added an underscore "_" after "NATIONAL".
Btw, I made another sample code for using a common routine.

VBA Code: 
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
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Colo said:
The first one worked on me, but I added an underscore "_" after "NATIONAL".
Btw, I made another sample code for using a common routine.

VBA Code: 
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
Click to expand...

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.
 
Upvote 0
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.

;-)
 
Upvote 0
Colo said:
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.

;)
Click to expand...

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.
 
Upvote 0
Hi,
It seems I forgot to change the file path in the code.
Give "Test2" a try!

VBA Code: 
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
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Locked
  • Question Question
Retrieve data from website with VBA
Replies
1
Views
716
Fluff
Fluff
M
  • Locked
  • Question Question
ADVANCED
Replies
1
Views
361
Peter_SSs
Peter_SSs
D
  • Question Question
VBA Download URL and rename file
Replies
6
Views
358
Jeffrey Mahoney
Jeffrey Mahoney
K
  • Question Question
Import Data from Multiple Web Pages
Replies
0
Views
269
Ka001
K
S
  • Solved
Date missing leading zero even with date format of dd/mm/yyyy in Excel
Replies
2
Views
523
Surreybloke
S

Forum statistics

Threads
1,224,520
Messages
6,179,266
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top