Does anyone have an example of how to call Airtable data into a VBA script to output in a Microsoft Excel spreadsheet?
Hi. Your question gave me an idea, thanks, and prompted me to come up with the following. Works for me, though my coding is probably dubious. Could use some error checking probably and would fall over on big tables.
The first row contains column names which match the names in the view. The table gets populated below that. Initiated via a click of CommandButton1.
Private Sub CommandButton1_Click() Dim W As Worksheet Set W = ActiveSheet 'Read column names from row 1. Should match Airtable column names. No empty columns. Dim fields As String colCount = 1 Do Until IsEmpty(W.Cells(1, colCount)) fields = fields & "&fields=" & W.Cells(1, colCount).Value colCount = colCount + 1 Loop 'Get the data from airtable, using ' AAAA is airtable id, e.g. applasl3409sdfsdl ' TTTT is table name in readable format, e.g. Customers ' VVVV is viewname in readable format, e.g. Prospects ' KKKK is API-key Dim http As New WinHttpRequest Dim resp As String Dim url As String url = "https://api.airtable.com/v0/AAAA/TTTT?api_key=KKKK&view=VVVV" & fields http.Open "GET", url, False http.Send Dim json As Object Set json = JsonConverter.ParseJson(http.ResponseText) respRecord = 1 On Error GoTo Exit_Loop Do Until json("records")(respRecord)("fields")(W.Cells(1, 1).Value) = "" For respCol = 1 To colCount - 1 cellValue = json("records")(respRecord)("fields")(W.Cells(1, respCol).Value) W.Cells(respRecord + 1, respCol).Value = cellValue Next respRecord = respRecord + 1 Loop Exit_Loop: End Sub
Hey @Chris_Bouwmeester, thanks for the response.
Having issues with the
http.Send request timing out.
Here’s a step-by-step of what I did.
Using Excel 2010
Copied your code into VBA
Replaced url with
"https://api.airtable.com/v0/appmyyIm86get23kg/tblEqnjM1Hy4wYECC?api_key=********&view=viwXzXZKlpfVaNYxz" & fields
Added references to Microsoft WinHTTP Services, version 5.1 and Microsoft Scripting Runtime
Imported JsonConverter.bas and Dictionary.cls into project
Clicked CommandButton1, waited, and got error
Here are the references I have selected. Any you think I’m missing?
You could use F8, breakpoints and other debug tools to check where the error is. Also msgbox is a useful debug command.
Seems that the table and view names need to be the ‘readable’ names, not the codes. For example my table is Centres and the view is Current. Didn’t think of this in the instructions.
Sorry - I see you already know where the error is. Not sure why there is a timeout. I’m using the latest excel but shouldn’t matter.
Probably should’ve read the additional comments you had added in the script. Alas, tried with the same result.
Could you screenshot your Available References to see if there is any differences between us? Or better yet, attach your workbook (minus the URL)?
References seem to be the same other than versions. I don’t use the Dictionary class module at all.
My table only import 60 or so rows, 15 columns.
Can’t see how I can add the workbook here. Only allows pics. Is there another way?