# Gotchas & Known Limits

Real architectural and behavioral limits you'll encounter. Knowing these saves debugging time.

## PivotTable Custom Formatting Doesn't Persist

User formatting (colors, bold, borders, etc.) on PivotTables is erased on refresh or when the workbook recalculates.

**Why:** PivotTables are generated by Excel's layout engine, which reapplies default formatting on every refresh.

**Workaround:** Use `pivottable(action: 'set-style')` to apply table-level styles. Styles are persisted across refreshes. Avoid `range_format()` on PivotTable cells.

## Data Model Hidden Objects Invisible to COM

Columns, relationships, and measures marked **"Hidden from client tools"** in Power Pivot cannot be detected, listed, or accessed via Excel COM API. No workaround.

**Why:** Microsoft intentionally hides these objects from automation to prevent breaking dependent calculations.

**What this means:**
- You cannot enumerate ALL columns in a Data Model table (hidden ones don't appear)
- You cannot detect relationships that are marked hidden
- You cannot list all measures (hidden measures are invisible)
- If an expected object doesn't appear in `datamodel` list calls, check Power Pivot UI (right-click table → check "Hidden from client tools" status)

**Mitigation:** If you need to work with hidden objects, ask the user to unhide them in Power Pivot first.

## Large Power Query Refreshes Timeout

Default timeout for `powerquery(action: 'refresh')` is **5 minutes**. Power Query operations querying large datasets often exceed this.

**Symptoms:** Operation appears to succeed but data doesn't load, or timeout error after 5 minutes.

**Fix:** Increase timeout when opening the session:

```json
{
  "action": "open",
  "filePath": "C:\\path\\to\\workbook.xlsx",
  "timeoutSeconds": 900
}
```

Set `timeoutSeconds` to match your expected query duration (900 = 15 min, 1800 = 30 min).

## Session Concurrency Requires STA Thread

Multiple Excel sessions on non-STA (Single-Threaded Apartment) threads can deadlock during initialization. The COM engine requires all sessions to run on the same STA thread pool.

**When this matters:** Only if you're building your own multi-threaded host. MCP Server and CLI handle this automatically.

**What you need to know:** If implementing custom session management, ensure all Excel COM calls happen on a single dedicated STA thread.

## Connection Strings Are Case-Sensitive

OLEDB and ODBC connection strings require exact case for parameter keys (Provider, Data Source, User ID, Password, etc.). Excel does NOT validate syntax when creating the connection — failures appear at **refresh time**, not create time.

**Example that fails at refresh:**
```
provider=SQLOLEDB;data source=server;Initial Catalog=db;
```

Should be:
```
Provider=SQLOLEDB;Data Source=server;Initial Catalog=db;
```

**Fix:** Always validate connection string syntax before creating connections. Test with `connection(action: 'test-connection')` after creation.

## Formulas Return 0 Until Calculation Completes

When you write formulas with `range(action: 'set-formulas')`, they don't automatically calculate. If you read them back immediately with `range(action: 'get-values')`, you'll see 0 (or #VALUE!) instead of the calculated result.

**Why:** Excel calculates formulas in background or on-demand depending on calculation mode.

**Fix:** After writing formulas, call `calculation_mode(action: 'calculate', scope: 'workbook')` before reading values back:

```
1. range(action: 'set-formulas', ...)     → Formula written, not calculated
2. calculation_mode(action: 'calculate')  → Now recalculate
3. range(action: 'get-values', ...)       → Read calculated results
```

## Python in Excel Requires Microsoft 365 Entitlement

`pythoninexcel` runs code in Microsoft's cloud sandbox. It requires a licensed Microsoft 365 account with Python in Excel enabled and internet access; perpetual-license Excel and offline sessions cannot run `=PY()` formulas.

When Excel evaluates a `PY()` formula as `#NAME?`, both `set-formula` and `get-result` return a clear unavailable-feature error. Do not retry that condition as if calculation were pending. `#BUSY!`, `#CONNECT!`, and `#BLOCKED!` are transient cloud states and retain retry semantics.

Python cloud startup can take several minutes. Prefer an asynchronous workflow: set the formula, continue other workbook work, then call `get-result` later. For the CLI, `--max-wait-seconds` must be at least 1 and shorter than the session's operation timeout; do not use a polling wait equal to or longer than the session timeout.

## File Locking Across Sessions

If multiple Excel sessions have the same workbook open, operations on one session can lock the file and timeout on other sessions (30-second timeout).

**Prevention:** Only open each file in one session at a time. Use `file(action: 'list')` to check if a file is already open before opening it again.

## Excel Visible/Hidden State is Session-Global

When you call `window(action: 'show')` or `window(action: 'hide')`, it affects the entire Excel application, not just your session. If multiple sessions are open, this will show/hide Excel for all of them.

**What you need to know:** If you have multiple sessions open and need one visible while others work silently, call `window(action: 'show')` only when needed and call `window(action: 'hide')` to clean up.

## Power Query Doesn't Support External Data Refresh on Save

Power Query connections that reference external files or URLs have a "refresh on open" setting, but changes to external files won't be detected until you manually refresh or the user opens the file in Excel.

**Workaround:** Always call `powerquery(action: 'refresh')` explicitly after updating external data sources.

## Special Characters in Range Addresses

Range addresses in M1 notation (e.g., `A1:D10`) work fine, but range names with spaces, hyphens, or special characters must be enclosed in single quotes or backticks:

```
rangeAddress: 'Sales Data'      ← Correct (with spaces)
rangeAddress: Sales Data        ← WRONG (fails)
rangeAddress: `Sales Data`      ← Also correct (backticks)
```

Same rule applies for sheet names: `'Sheet Name'!A1:D10`.

## DATE Conversion from Power Query

When Power Query returns dates, they come back as **OLE date numbers** (e.g., 45300 for 2024-01-01). Excel handles conversion automatically in cells, but if you're reading these values programmatically, parse them as:

```
actual_date = datetime.fromordinal(int(ole_date_value) + 1)  # Python example
```

This is rarely an issue in normal workflows but matters if you're doing calculations with the dates outside Excel.