Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 09, 2017 06:26 PM
Hi all.
Does anyone have an example of how to call Airtable data into a VBA script to output in a Microsoft Excel spreadsheet?
Feb 15, 2017 01:21 AM
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
Feb 15, 2017 02:40 PM
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?
Feb 15, 2017 03:07 PM
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.
Feb 15, 2017 03:10 PM
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.
Feb 15, 2017 03:33 PM
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.
Feb 15, 2017 04:06 PM
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?