Data call from a website usingVBA

[ADBELQMICH]

Hello everyone, I have to write VBA codes that will allow me to call data from a website, download it in excel format then put it in a current worksheet *database*. I also have to call the data from different URLs at the same time.
The idea is, I will have a button called (update), and when I click on it, it should update the data everytime by reaching out to the URLs and downloading the data then update the worksheet. (I am sorry If Im making this confusing)

These are some URLs that I will be using (example)

Unemployment Rate

View data of the unemployment rate, or the number of people 16 and over actively searching for a job as a percentage of the total labor force.

fred.stlouisfed.org

Consumer Price Index: Total All Items: Wage Earners for the United States

Could you help me with this please, I am quite new to VBA programming and m stuck on this particular task. I would really appreciate it

 

[QuietRiot]

It looks like you can use the IDs for the different data needed. For example https://fred.stlouisfed.org/graph/fredgraph.xls?id= [the id here]. Then you could probably just open the excel file and copy the data somewhere and do what you need with it.

Really crude example here, but might help you get started or come up with ideas:

Sub Test()
    Dim URL As String
    Dim id As String
    Dim wb1 As Workbook
    Dim wb2 As Workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set wb1 = ActiveWorkbook
    
    id = "CPALWE01USQ661N"       ' OR   "UNRATE"
    URL = "https://fred.stlouisfed.org/graph/fredgraph.xls?id=" & id

   ' Uses a sheet called Data
    wb1.Sheets("Data").Cells.ClearContents
    Set wb2 = Workbooks.Open(FileName:=URL)
    wb2.ActiveSheet.Cells.Copy
    wb1.Activate
    Range("A1").Select
    ActiveSheet.Paste
    wb2.Close
    
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[ADBELQMICH]

thank you so much, it really helped me to start. Just a little question, This will still work even if the data is updated every month (for example), I mean the ID would still remain the same despite the update in the website

 

[QuietRiot]

thank you so much, it really helped me to start. Just a little question, This will still work even if the data is updated every month (for example), I mean the ID would still remain the same despite the update in the website

It should yes. If you wanted to you could even send other parameters. For example https://fred.stlouisfed.org/graph/f...cosd=2019-01-01&coed=2019-07-01&&fq=Quarterly

If you don't specify other parameters it should just use the defaults though. Same as if you were going to the site and I don't see the IDs changing since their main site uses them:
fred.stlouisfed.org/series/CPALWE01USQ661N
fred.stlouisfed.org/series/UNRATE

Also in my example I'm just using Workbooks.Open(FileName:=URL) to get it, but there at least a few other was of accomplishing it that might look smoother and work quicker.

 

[ADBELQMICH]

Can you help me integrate the dynamic selection here please, I tried this but doesn't seem to work.

Sub Test()
Dim URL As String
Dim id As String
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wb1 = ActiveWorkbook


id = "CPALWE01USQ661N" ' OR "UNRATE"
URL = "https://fred.stlouisfed.org/graph/fredgraph.xls?id=" & id

' Uses a sheet called Data
Set wb2 = Workbooks.Open(Filename:=URL)
Set sht = Worksheets("FRED Graph")
Set StartCell = Range("A12:B12")

LastRow = wb2.sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = wb2.sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

'Select Range
wb2.sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

wb1.Activate
Range("A1").Select
ActiveSheet.Paste
wb2.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[QuietRiot]

Sub Test()
Dim URL As String
Dim id As String
Dim wb1 As Workbook
Dim wb2 As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wb1 = ActiveWorkbook


id = "CPALWE01USQ661N" ' OR "UNRATE"
URL = "https://fred.stlouisfed.org/graph/fredgraph.xls?id=" & id

' Uses a sheet called Data
Set wb2 = Workbooks.Open(Filename:=URL)
    Rows("1:10").Delete Shift:=xlUp
    Cells.Copy
wb1.Activate
ActiveSheet.Paste
wb2.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub