Airtable Table Data to Excel VBA

[Stephen_Chapman]

Hi all.
Does anyone have an example of how to call Airtable data into a VBA script to output in a Microsoft Excel spreadsheet?

[Chris_Bouwmeest]

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.

See https://github.com/VBA-tools/VBA-JSON for the json converter. https://www.youtube.com/watch?v=CFFLRmHsEAs shows how to use it.

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

[Stephen_Chapman]

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

1. Copied your code into VBA

2. Replaced url with "https://api.airtable.com/v0/appmyyIm86get23kg/tblEqnjM1Hy4wYECC?api_key=********&view=viwXzXZKlpfVaNYxz" & fields

3. Added references to Microsoft WinHTTP Services, version 5.1 and Microsoft Scripting Runtime

4. Imported JsonConverter.bas and Dictionary.cls into project
   

5. Clicked CommandButton1, waited, and got error

Here are the references I have selected. Any you think I’m missing?

[Chris_Bouwmeest]

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.

[Chris_Bouwmeest]

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.

[Stephen_Chapman]

Chris_Bouwmeester:

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.

https://api.airtable.com/v0/appmyyIm86get23kg/Issues?api_key=********&view=AllIssues

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)?

Cheers mate.

[Chris_Bouwmeest]

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?