How to Connect Excel to QuickBooks Desktop with an ODBC Driver

Excel is still one of the most common places teams want QuickBooks Desktop data. The usual goal is simple: refresh invoices, customers, payments, items, or open balances without exporting CSV files from QuickBooks every week.

An ODBC driver gives Excel a reusable data source. Once it is configured, finance and operations teams can refresh the workbook instead of repeating a manual export.

This guide shows the practical path using Syntra ODBC, but the checks around 32-bit vs 64-bit Excel, DSNs, and SQL queries apply to most QuickBooks ODBC driver setups.

Before you start

You need:

• QuickBooks Desktop Pro, Premier, or Enterprise 2018 or newer.
• Windows 10, Windows 11, or Windows Server 2019 or newer.
• Microsoft Excel on the same machine, or on a machine that can reach the Syntra SQL server.
• Syntra ODBC installed from the download page.

Syntra installs both 64-bit and 32-bit ODBC driver entries and creates a System DSN named Syntra QuickBooks. That matters because modern Excel is usually 64-bit, while older Office installs may still be 32-bit.

Step 1: Install and authorize QuickBooks access

Install Syntra ODBC on the machine that runs QuickBooks Desktop. The first time Syntra connects, QuickBooks will show an Integrated Applications authorization prompt.

Choose the option that allows Syntra ODBC to access the company file in future sessions. If you only allow access one time, QuickBooks may ask again later and automated refreshes can fail.

For the full product setup, use the installation guide and QuickBooks setup guide.

Step 2: Confirm the right ODBC Data Source Administrator

Windows has two ODBC administrator tools:

• 64-bit apps: C:\Windows\System32\odbcad32.exe
• 32-bit apps: C:\Windows\SysWOW64\odbcad32.exe

The names look backwards because of Windows filesystem history. For Excel, the important question is the bitness of Office, not the bitness of Windows.

Open Excel, go to File > Account > About Excel, and check whether it says 64-bit or 32-bit. Then open the matching ODBC administrator and look under System DSN for Syntra QuickBooks.

If you need DSN-less configuration, the driver connection string is:

Driver={Syntra ODBC - QuickBooks ODBC};Server=127.0.0.1;Port=5433;Database=qbconnect;Uid=qbconnect;Pwd=<your-password>;

For more options, see the ODBC driver setup guide and connection string examples.

Step 3: Connect Excel through ODBC

In Excel:

1. Go to Data > Get Data > From Other Sources > From ODBC.
2. Pick Syntra QuickBooks.
3. Choose Advanced options if you want to paste a SQL query.
4. Load the result into a worksheet or into Power Query.

For a quick test, start with a small query:

SELECT
  name,
  full_name,
  is_active
FROM customers
ORDER BY full_name
LIMIT 100;

If that loads, the connection is working. Then move to the data you actually need.

Useful Excel queries for QuickBooks Desktop

Open invoices:

SELECT
  customer_ref_full_name,
  ref_number,
  txn_date,
  due_date,
  balance_remaining
FROM invoices
WHERE COALESCE(balance_remaining, 0) > 0
ORDER BY due_date, balance_remaining DESC;

Sales by customer this year:

SELECT
  customer_ref_full_name,
  SUM(total) AS total_sales
FROM invoices
WHERE txn_date >= DATE '2026-01-01'
GROUP BY customer_ref_full_name
ORDER BY total_sales DESC;

Active inventory items:

SELECT
  full_name,
  sales_price,
  quantity_on_hand
FROM item_inventories
WHERE COALESCE(is_active, true) = true
ORDER BY full_name;

These queries are intentionally worksheet-friendly: clear column names, stable filters, and no hidden workbook logic. If you need heavier modeling, load the ODBC query into Power Query and shape the data there.

Refresh behavior

Excel starts the request. Syntra answers the SQL query from its local cache or, when configured, from live QuickBooks access. Cached reads can continue even when QuickBooks is closed. Live reads and writes require QuickBooks Desktop to be running.

For operational workbooks, use cached reads by default. They are faster and avoid putting unnecessary load on QuickBooks. Use live reads only when the workbook must reflect the very latest state.

Common setup problems

The DSN does not appear in Excel.
You are probably looking at the wrong ODBC bitness. Check whether Excel is 32-bit or 64-bit, then open the matching ODBC administrator.

The driver appears, but connection fails.
Confirm Syntra is running. By default, the local SQL server listens on 127.0.0.1:5433.

Excel says authentication failed.
The username or password in the connection string does not match the Syntra config.toml auth settings.

QuickBooks asks for permission every time.
Open QuickBooks Desktop and check Edit > Preferences > Integrated Applications. The application should be approved for repeat access.

The first query is slow.
The initial sync may still be building the local cache. After the first sync, repeat queries should be much faster.

When Excel is the wrong tool

Excel is excellent for ad hoc analysis and small reporting workbooks. If you need scheduled dashboards, row-level security, or shared reports, use the same Syntra connection from Power BI, Tableau, or a database client.

The important part is that the QuickBooks data source stays the same. You can start with Excel and move to a BI tool without changing the underlying QuickBooks ODBC driver setup.