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.

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

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.

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.

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?