Need help getting API data to excel using VBA

I am trying to use VBA to import data from the API into excel. I have written code to format the ULR. But i am not sure how to send the request from the code to the API. I would also like the take the xml response and extract the information I want (ID,name,release date) to cells in excel. I am super new to any of this so feel free to dumb it down (however any help would be appreciated). Here is my code so far:

Private Sub CommandButton1_Click()

Dim strInput As String

strInput = TextBox1.Text

strInput = Replace(strInput, " ", "%20")

Dim myURL As String

myURL = "http://api.giantbomb.com/search/?query=" & strInput & "&api_key=ABCDEFG123456&format=xml&resources=game&field_list=id,name,original_release_date"

End Sub

@bmetaylor said:

I am trying to use VBA to import data from the API into excel. I have written code to format the ULR. But i am not sure how to send the request from the code to the API. I would also like the take the xml response and extract the information I want (ID,name,release date) to cells in excel. I am super new to any of this so feel free to dumb it down (however any help would be appreciated). Here is my code so far:

Private Sub CommandButton1_Click()

Dim strInput As String

strInput = TextBox1.Text

strInput = Replace(strInput, " ", "%20")

Dim myURL As String

myURL = "http://api.giantbomb.com/search/?query=" & strInput & "&api_key=ABCDEFG123456&format=xml&resources=game&field_list=id,name,original_release_date"

End Sub

I have written a JavaScript function that will export a HTML table to Excel. It only works in IE of course but may help with your need. If you can get the API data to a HTML table and give the table and ID then after the API load call the function. I know it is not VBA but hope it helps.

 function F_table_to_excel(tableid){var mybrowser; mybrowser = navigator.appName; mybrowser = mybrowser.toUpperCase(); if (mybrowser.indexOf("MICROSOFT") != -1){ alert ("After clicking the [OK] button the table will be exported to Excel. You may notice the web browser appears to be locked up during this process. Excel will be shown once the process is complete. This may take several seconds or minutes depending on the amount of data."); var x; var i; var j; var tmps; x = document.getElementById(tableid).rows; var xls = new ActiveXObject("Excel.Application"); xls.visible = false; xls.Workbooks.Add; for (i = 0; i < x.length; i++){ var y = x[i].cells; for (j = 0; j < y.length; j++){ tmps = y[j].innerText; tmps = tmps.replace(String.fromCharCode(13), ""); if (tmps.length != 0){ if (tmps.indexOf("=") == 0){ tmps = " " + tmps; } } xls.Cells(i+1, j+1).Value = tmps; } } xls.visible = true; xls.WindowState = 3; xls.Application.AppActivate; } else { alert("This feature only functions with the Microsoft Internet Explorer web browser."); } }