Retrieve data from website with VBA

[mickeystanford_alumni]

Hi gents,

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. A python code I could use is this:

data = {

"key": api_key,

"source_desc": "SURVEY",

"commodity_desc": commo,

"year": year,

"short_desc": items,

"agg_level_desc": "COUNTY",

"format": "JSON"

}

r = requests.get("http://quickstats.nass.usda.gov/api/api_GET/", params = Dat

a)

BUT I WANT TO DO IT WITH VBA

For the moment, I have used this code, but this forces me to automatically pick the product, source, year etc, and then ‘get data’, and then copy the link into my code.

Is there any way to do that automatically?

THANKS A LOT.


Dim thisWb, downloadWb As Workbook
Set thisWb = ActiveWorkbook

Set downloadWb = Workbooks.Open("http://quickstats.nass.usda.gov/data/spreadsheet/4C43034A-0EAA-3171-B4FC-84CC95FC6E0C.csv")

downloadWb.Worksheets(1).Range("A1:U2613").Copy Destination:=thisWb.Worksheets(1).Range("A2")

downloadWb.Close

End Sub

Thank you guys

 

[Fluff]

Duplicate to: Downloading dynamic data monthly

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.