Airtable Table Data to Excel VBA

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.

1Private Sub CommandButton1_Click()
2
3Dim W As Worksheet
4Set W = ActiveSheet
5
6'Read column names from row 1. Should match Airtable column names. No empty columns.
7Dim fields As String
8colCount = 1
9Do Until IsEmpty(W.Cells(1, colCount))
10fields = fields & "&fields[]=" & W.Cells(1, colCount).Value
11colCount = colCount + 1
12Loop
13
14'Get the data from airtable, using
15'    AAAA is airtable id, e.g. applasl3409sdfsdl
16'    TTTT is table name in readable format, e.g. Customers
17'    VVVV is viewname in readable format, e.g. Prospects
18'    KKKK is API-key
19Dim http As New WinHttpRequest
20Dim resp As String
21Dim url As String
22
23url = "https://api.airtable.com/v0/AAAA/TTTT?api_key=KKKK&view=VVVV" & fields
24http.Open "GET", url, False
25http.Send
26
27Dim json As Object
28Set json = JsonConverter.ParseJson(http.ResponseText)
29
30respRecord = 1
31On Error GoTo Exit_Loop
32
33Do Until json("records")(respRecord)("fields")(W.Cells(1, 1).Value) = ""
34
35For respCol = 1 To colCount - 1
36    cellValue = json("records")(respRecord)("fields")(W.Cells(1, respCol).Value)
37    W.Cells(respRecord + 1, respCol).Value = cellValue
38Next
39respRecord = respRecord + 1
40Loop
41
42Exit_Loop:
43
44End 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

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?

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.

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

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.

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?