Introduction
Suppose you’re using Smartsheet to manage projects, tasks, or team collaboration and want to bring that data into Excel for reporting or analysis. In that case, integrating ODBC with Excel VBA macros is a powerful solution. With just one click, you can retrieve real-time Smartsheet data into Excel and build dynamic reports, dashboards, or audit tools.
In this tutorial, you’ll learn how to connect to Smartsheet using the ZappySys ODBC API Driver and automate data retrieval using Excel macros.
Prerequisites
- Download and install the ZappySys ODBC PowerPack.
- A Smartsheet account.
- Microsoft Excel (Windows version with macro support).
- Basic familiarity with the VBA editor in Excel.
Step-by-step guide
Step 1: Create a token to access the Smartsheet REST API
- Log in to your Smartsheet account.
- Navigate to Account > Personal Settings.
- Go to API Access and click Generate new access token. After generating the token, copy it securely for later use (treat this token as sensitive information).
Step 2: Configure the ZappySys ODBC driver
- Open the ODBC Data Source Administrator by searching for “ODBC” in the Windows Start menu.
- Go to the User DSN or System DSN tab and click Add to create a new data source.
- Select ZappySys API Driver, then choose Smartsheet from the list of connectors. Click Continue.
Step 3: Configure the Smartsheet connector
- Set Authentication Type to Static Token, paste your generated token, and click Test Connection.
- Go to the Preview tab, choose a table, and preview the data. Click OK to save the configuration.
Step 4: Write the Excel VBA macro
- Open Excel, go to the Developer tab, and click Visual Basic (or press
Alt + F11
).
- In the VBA editor, go to Insert > Module.
- Paste the following macro code:
Sub ImportDataFromODBC_Smartsheet()
Dim connection As Object
Dim recordset As Object
Dim connectionString As String
Dim sqlQuery As String
Dim col As Integer
Dim currentRow As Long
Set connection = CreateObject("ADODB.Connection")
Set recordset = CreateObject("ADODB.Recordset")
connectionString = "DSN=API Driver - Smartsheet" ' Replace with your DSN name
sqlQuery = "SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, Ur FROM Sheet1" ' Replace with your actual Smartsheet endpoint
connection.Open connectionString
connection.CommandTimeout = 900
recordset.Open sqlQuery, connection, 0, 1
If recordset.EOF Then
MsgBox "No records found.", vbExclamation
Else
With ThisWorkbook.Sheets("Sheet1")
.Cells.ClearContents
For col = 0 To recordset.Fields.Count - 1
.Cells(1, col + 1).Value = recordset.Fields(col).Name
Next col
currentRow = 2
Do Until recordset.EOF
For col = 0 To recordset.Fields.Count - 1
.Cells(currentRow, col + 1).Value = recordset.Fields(col).Value
Next col
currentRow = currentRow + 1
recordset.MoveNext
Loop
.Columns.AutoFit
End With
MsgBox "Smartsheet data imported successfully!", vbInformation
End If
recordset.Close: Set recordset = Nothing
connection.Close: Set connection = Nothing
End Sub
Step 5: Run the macro
- Close the VBA editor.
- Press
Alt + F8
, selectImportDataFromODBC_Smartsheet
, and click Run. - Your Excel sheet will be populated with Smartsheet data.
- Optionally, assign the macro to a button in Excel for quicker access.
Benefits of this integration
- Live access to Smartsheet project and task data
- Automated reporting — no manual downloads
- SQL filtering for precise data queries
- Macro-based refresh for repeatable updates
Troubleshooting tips
- Empty results? Check sheet visibility and API token permissions.
- Connection fails? Verify the token and test it in the connector setup.
- Macro not working? Ensure macros are enabled under Trust Center Settings.
Conclusion
Combining Excel VBA macros with ODBC integration provides a powerful and automated way to retrieve Smartsheet data into Excel. Whether you’re tracking tasks, managing resources, or building reports, this integration simplifies and accelerates your workflow.
Visit our official page to discover more connectors, powerful automation features, and real-time data integration options. Start building smarter, faster, and more scalable solutions today with ZappySys ODBC PowerPack.
References
- Smartsheet API Driver
- Smartsheet Connector documentation
- Smartsheet Connector | API Integration Hub
- Blog articles
- ODBC PowerPack
Contact us
If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.