Excel and Access refreshing from SQL, not from lists

Last updated 23 October 2026

An Excel workbook or Access database linked to a SharePoint list takes ages to refresh, and large lists sometimes fail to open at all.

The regional sales manager’s weekly workbook has a data connection to the team’s Opportunities list. When the list was small, Refresh All took a few seconds. Now she starts it, makes a cup of tea, and sometimes comes back to an error. Excel’s SharePoint connection, like the linked tables in an Access database, resolves lookup columns one at a time, so the refresh gets slower with every row and every lookup, and on a large list it can fail outright. The team has started working from last week’s copy, because the refresh itself is now the bottleneck.

Keep the workbook, change its source

SPSync replicates the list into a database table on a schedule, and the workbook connects to that table as Excel and Access connect to any SQL database, which both do well. The layout, the pivots and the charts stay where they are. Only the data connection changes, from a SharePoint list to SQL Server, Postgres or a similar database, and a refresh takes as long as a database query.

The one change to formulas

Each list arrives as a table with the SharePoint list’s columns, plus a stable primary key. A lookup arrives as two columns: {col}_id, a key into the table for the list it points at, and {col}_value, the display text. A formula or query that used a lookup’s text needs a one-time update to read the _value column, or a join when it needs more fields from the related list. After that, every refresh is fast and the workbook behaves as before.

Multi-value columns are child tables, which Excel and Access usually find easier to work with than the semicolon-separated text a SharePoint export sometimes produces. Deleted items keep their row with sp_deleted_at set, so “everything that existed this quarter” still adds up.

Moving the connection

Pick the site, the list behind the workbook, and the lists its lookups point to. Choose a SQL destination Excel or Access already knows how to reach, and set a schedule that matches how often the report is read; for a weekly pack, every hour is plenty. Then open the workbook’s data connection, or the Access linked tables, and point them at the database.

If several people share the workbook, move the sync off any one person’s laptop: SPSync in your Azure, or the command line on a server, keeps the table current whoever has the file open.

If the workbook started life as a weekly Export to Excel or a Power Automate recipe, our post on exporting a SharePoint list to Excel automatically sets those routes and their Microsoft limits against this one, and walks through the refresh-on-open setting in Excel.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name excel_reports_from_sharepoint --prefix excel_reports_from_sharepoint --destination-kind mssql --connection-string-env SPSYNC_DB_CONNECTION --dataset-name excel_reports_from_sharepoint --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: excel_reports_from_sharepoint
prefix: excel_reports_from_sharepoint
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: mssql
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: excel_reports_from_sharepoint
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist