Connect Excel to your databases, APIs and business systems
Your data lives in SQL Server, Simpro, Xero, Shopify and a dozen other systems. We connect Excel to all of them — live SQL refreshes, REST APIs and structured export workflows — so your team works in the tools they know, without manual CSV exports.
Discuss your integrationThe integration problems we solve
Most businesses have data spread across systems that were never designed to talk to each other. Excel is often the practical bridge.
Your software cannot do what you need — and cannot be changed
Third-party platforms like Simpro, Xero, MYOB, and industry-specific tools are built for their core purpose — not yours. Requesting changes is slow, expensive, and often refused. The data exists in the system; getting it out and working with it is the problem.
Exports, manual re-entry, and re-uploads waste hours every week
Teams download data from one system, reformat it in Excel, perform analysis or data massaging, then re-upload it. Each step is manual, error-prone, and invisible to the source system. A structured integration eliminates the re-entry and the errors it introduces.
Excel is isolated from live data
Reports and dashboards built in Excel go stale the moment they are saved. Without a live connection to the database or platform driving your business, every decision is based on yesterday's data. Real-time or scheduled refreshes change this.
Multiple teams pull the same data and get different numbers
Separate CSV extracts, different filters and timing mean finance, operations and analysts work from conflicting figures. A shared live SQL or API connection gives everyone the same source of truth.
Multi-user data management hits Excel's limits
Shared workbooks are fragile. When multiple users need to read and write the same data simultaneously, Excel alone cannot handle it safely. Using a database as the data layer — with Excel as the front end — gives you multi-user capability without abandoning the tools your team knows.
E-commerce and web platform data lives in silos
Order data, inventory levels, customer records, and fulfilment status sit in platforms like Shopify, WooCommerce, or custom APIs. Pulling that data into Excel for analysis, reconciliation, or reporting requires an integration — not a manual export.
Legacy applications have no modern reporting layer
Older business systems often have poor or no native reporting. The underlying data is there — in SQL Server, Access, or an exportable format — but surfacing it requires someone to bridge the gap between the legacy system and a usable reporting environment.
What we connect and how
Different integration challenges require different approaches. We use VBA, Power Query, and direct database connections depending on what the situation calls for.
SQL Database Connectivity
Direct VBA and Power Query connections to SQL Server, MySQL, PostgreSQL, Oracle, SQLite, Azure SQL and Amazon RDS via ADO, ODBC or OLE DB. Read, write and update records from Excel without manual exports. We use parameterised queries, least-privilege accounts, error handling and connection design suited to your data volume and user count — on-premise or cloud.
- Live reporting dashboards pulling from SQL Server with one-click refresh
- Excel as a data entry front-end writing validated results back to a shared database
- Replacing Access databases with SQL Server-backed Excel applications
- On-premise and cloud SQL connections coordinated with your IT firewall and ODBC setup
Third-Party Software — Export / Re-upload Workflows
For platforms that cannot be directly connected to — Simpro, Procore, industry-specific tools, legacy ERP systems — we build structured download-process-upload workflows. Data is pulled via export, transformed and validated in Excel using VBA, then re-uploaded in the exact format the platform requires, with reconciliation checks at each step.
- Simpro job cost data downloaded, reclassified in Excel, re-uploaded with corrected cost codes
- MYOB transaction exports processed and reconciled against budget models
- Tender pricing tools that export in the exact format required by procurement platforms
REST API and Web Service Integration
VBA can call REST APIs using WinHTTP or XMLHTTP, parse JSON and XML responses, and write results directly into Excel. Power Query adds a no-code layer for APIs that support standard authentication. We use both depending on the complexity of the data transformation required.
- Pulling live currency rates, commodity prices, or market data into financial models
- Connecting to e-commerce APIs (Shopify, WooCommerce) to pull order, inventory, and customer data
- Calling weather, logistics, or freight APIs to enrich operational reports
Database-Backed Multi-User Excel Applications
Excel is an exceptional front-end for business users — familiar, flexible, and powerful. By storing data in SQL Server or another relational database and using VBA to handle all reads and writes, we build multi-user applications that look and feel like Excel but behave like proper database applications: concurrent access, record locking, audit trails, and no file corruption.
- Job management tools used by field and office teams simultaneously
- Pricing engines where multiple estimators work from a shared database of rates and materials
- Approval workflow tools where submitters and approvers see the same data in real time
SQL, API and platform integration examples
Live database connections and integration patterns we build most often for New Zealand businesses.
Job costing and cost code reclassification
Simpro is powerful for field service management but inflexible for cost reporting restructuring. We build Excel tools that download Simpro job data, allow finance teams to reclassify cost codes and margins at scale, and re-upload corrected records — a task that would take weeks of Simpro support tickets.
Budgeting, forecasting and variance reporting
Xero and MYOB handle transactional accounting well but offer limited modelling capability. We connect Excel directly to the Xero or MYOB API, pull actuals in real time, and build forecast models and variance reports that live-update without any manual export.
E-commerce analytics and inventory management
We pull order history, product performance, and inventory data from e-commerce APIs into Excel for analysis, margin calculation, and demand forecasting. Scheduled refreshes keep reports current without anyone touching a keyboard.
Multi-user front-end applications
When a business outgrows a shared workbook but is not ready for a full custom application, a database-backed Excel front-end is often the pragmatic solution. One to five concurrent users, full record history, and proper data integrity — built and running in weeks.
Live GL Reporting in Excel
Excel connects directly to the general ledger database via Power Query — finance team refreshes reports in one click without IT involvement.
Production Dashboard from ERP Database
VBA queries the ERP SQL database, pulls current production data and updates a formatted Excel dashboard that refreshes every morning automatically.
Claims Analysis Tool
Excel connected to the claims database via ADO — analysts can run custom queries from dropdown filters in Excel without writing SQL.
Stock and Sales Reporting
Power Query connects to the retail management SQL database, joins stock and sales tables and loads a pivot-ready dataset — replaces daily manual CSV extraction.
How we approach integrations
Integration Discovery
We map the data flow — what system holds the data, what format it is in, what transformation is needed, and where it needs to go. We identify whether VBA, Power Query, or a combination is the right tool, and whether a direct connection is possible or a structured export-process-upload model is needed.
Connection Architecture
We design the integration layer: connection strings, authentication method, query structure, error handling, and refresh schedule. For database-backed applications, we design the schema. For API integrations, we validate the authentication and rate limit constraints upfront.
Build and Test
The integration is built against real data — not sample files. We test for volume, edge cases, malformed records, connection failures, and concurrent access. Error handling and user-facing messages are built in from the start, not added later.
Handover and Documentation
You receive a working integration, documentation covering connection management, and guidance on maintaining the solution. For database-backed applications, we include a data dictionary and schema documentation your team can hand to any future developer.
Excel integration consulting across New Zealand
We work with New Zealand businesses in construction, field services, manufacturing, retail, finance, logistics, insurance and government — industries where SQL databases and third-party platforms hold critical data but lack the flexibility to report and analyse it properly. Based in Auckland and working with clients nationwide, we connect Excel to on-premise and cloud SQL environments and coordinate with your IT team on access, firewall and ODBC configuration.
Frequently asked questions
Which databases can Excel connect to?
Excel can connect to SQL Server, MySQL, PostgreSQL, Oracle, SQLite, Azure SQL, Amazon RDS, Google BigQuery and others via Power Query, ODBC, OLE DB or ADO. We advise on the right connection method for your specific database and environment — including on-premise networks where firewall and driver setup matter.
Can Excel connect directly to our SQL Server database?
Yes. VBA uses ADO (ActiveX Data Objects) to open a direct connection to SQL Server, MySQL, PostgreSQL, Oracle, and other databases. Excel can read, write, update, and delete records with full transactional control. Power Query provides an additional no-code layer for read-only queries where that is sufficient.
Is it secure to connect Excel directly to a database?
Yes, when configured correctly. We use connection strings with least-privilege database accounts, recommend read-only access for reporting connections, and can configure Windows Authentication rather than stored passwords. We document the security approach for your IT team.
Can Excel write data back to a SQL database?
Yes. Using VBA with ADO connections, Excel can insert, update or delete records. This is useful for data entry tools where validated data needs to flow back into a central system, and for multi-user applications where Excel is the front-end and the database is the source of truth.
Will a live database connection slow Excel down?
A well-designed connection uses parameterised queries that return only the data needed — this is generally faster than loading a full CSV export. We optimise queries for performance and test on your data volumes before delivery.
Simpro says we cannot access their API. What are our options?
Simpro and similar field service platforms often have limited or gated API access for their standard tiers. The practical alternative is a structured export-process-upload workflow: data is downloaded as CSV or Excel from Simpro, processed and transformed using VBA, then re-uploaded in the format Simpro accepts. We build these workflows to run reliably and include validation at every step to catch errors before they reach the system.
Can multiple people use the same Excel file at the same time?
Standard shared workbooks are unreliable for concurrent editing. The correct architecture for multi-user Excel applications is to store all data in a SQL database and use Excel purely as the front-end. VBA handles all reads and writes to the database, which supports concurrent access properly. The result looks and feels like Excel to users but behaves like a proper application.
How do you handle authentication for REST APIs?
VBA supports API key authentication, Basic authentication, OAuth 2.0 token-based flows, and custom header authentication via WinHTTP. For OAuth, we build a token refresh flow so credentials do not need to be re-entered. Power Query supports a similar range through its built-in web connector. We match the authentication method to what the API requires.
Can Excel pull live data from Shopify or WooCommerce?
Yes. Both platforms have well-documented REST APIs. We build VBA or Power Query connections that authenticate, paginate through results, and load order, product, inventory, or customer data directly into Excel. Refresh can be triggered manually or on a schedule using Task Scheduler.
What happens when the third-party software updates and breaks the integration?
API-based integrations are dependent on the API version and the provider's change management. We build integrations against stable API versions where available, include version pinning, and document all dependencies. For export-based workflows, changes to the export format are the most common break point — we design these to surface format mismatches clearly rather than silently processing incorrect data.
Do you work with cloud databases as well as on-premises SQL Server?
Yes. We connect Excel to cloud-hosted databases including Azure SQL Database, Amazon RDS, Supabase, PlanetScale, and others via standard ADO connection strings. The connection configuration differs slightly for cloud vs on-premises, and firewall and IP whitelisting requirements need to be managed — but the Excel and VBA layer is identical.
Let's talk about what you need
Big or small, we are happy to discuss it. Send us a message or book a free discovery call — we typically respond same business day.
What happens next
- 1
We review your enquiry carefully and reach out personally — usually same business day. No obligation, no hard sell.
- 2
We clarify scope, then provide a clear fixed-price quote and realistic delivery timeframe so you know exactly where you stand.
- 3
Once agreed, we build in stages, keep you updated, and hand over a solution your team can rely on — with support if you need it.