---

# Business Logic — modExport.RunExport() [57 LOC]

| Field | Value |
|-------|-------|
| Fully Qualified Name | `modExport.RunExport(Optional batchID As Long = -1) As String` |
| Layer | Utility |
| Module | `modExport` (Package: `vba-source/modExport.bas`) |

## 1. Role

### modExport.RunExport()

`RunExport` generates a reconciliation report file (XLSX) from data that has already been imported and reconciled in the Microsoft Access database. It serves as the **report output** step in the Import-Export-Workflow system: after `modImport.RunImport()` ingests bank or branch transaction files into `tblTransactions` and `tblBankStatement`, and `modReconcile.RunReconcile()` matches transactions against bank statements (producing `tblReconcileResult` records with match status and variance), `RunExport` takes the reconciled result set and writes a human-readable, formatted Excel spreadsheet for accounting review.

The method implements a **builder pattern** — it constructs an Excel workbook in memory, populates it row-by-row with the reconciliation query results, applies conditional formatting (light-red highlight for non-zero variance rows), and saves the file to the project directory. It is a **shared utility** callable from any screen or form that needs to produce a reconciliation report, and it accepts an optional `batchID` to target a specific import batch (defaulting to the most recent batch via the global `modUtil.gCurrentBatchID`).

If no reconciliation data exists for the given batch, the method produces a minimal Excel file with headers only, gracefully handling the empty-recordset case.

## 2. Processing Pattern (Detailed Business Logic)

```mermaid
flowchart TD
    START(["Start: RunExport(batchID)"])
    
    START --> BATCH["Resolve effectiveBatchID"]
    
    BATCH --> DB["Open DB connection: CurrentDb()"]
    
    DB --> QUERY["Execute: SELECT t.RefNo, t.Branch, t.TxnDate, t.Amount, r.MatchStatus, r.Variance FROM tblReconcileResult r INNER JOIN tblTransactions t ON r.TransID = t.TransID WHERE t.ImportBatchID = effectiveBatchID"]
    
    QUERY --> EXCEL["Create Excel.Application (visible=False)"]
    
    EXCEL --> WB["Add new Workbook, get Sheet1"]
    
    WB --> HEADERS["Write column headers to row 1: RefNo, Branch, TxnDate, Amount, MatchStatus, Variance"]
    
    HEADERS --> INITROW["Set r = 2"]
    
    INITROW --> WHILE["While Not rs.EOF"]
    
    WHILE --> WRITEFIELDS["Write rs fields to row r, columns 1-6"]
    
    WRITEFIELDS --> CHECKVAR{rs!Variance <> 0?}
    
    CHECKVAR -->|Yes| HIGHLIGHT["Highlight cell (row r, col 6) with light red background"]
    CHECKVAR -->|No| SKIPHL{"Skip highlighting"}
    
    HIGHLIGHT --> INCR["r = r + 1"]
    SKIPHL --> INCR
    
    INCR --> MOVE{"rs.MoveNext"}
    
    MOVE --> WHILE
    
    WHILE --> CLOSERS["Close Recordset"]
    
    CLOSERS --> AUTOFIT["AutoFit columns A:F"]
    
    AUTOFIT --> SAVEPATH["Build path: ReconcileReport_yyyymm.xlsx"]
    
    SAVEPATH --> SAVE["SaveAs (format 51 = xlOpenXMLWorkbook)"]
    
    SAVE --> CLEANUP["Close workbook, Quit Excel, release objects"]
    
    CLEANUP --> RETURN(["Return: outPath"])
```

**Processing Flow:**

1. **Batch Resolution** — Determines the effective batch ID. If the caller passes `-1`, it delegates to `modUtil.gCurrentBatchID` (the most recently imported batch). Otherwise it uses the explicit `batchID`.

2. **Data Retrieval** — Opens a SQL query that joins `tblReconcileResult` with `tblTransactions` on `TransID`, filtering by the effective batch ID. Retrieves 6 fields: `RefNo`, `Branch`, `TxnDate`, `Amount`, `MatchStatus`, and `Variance`.

3. **Excel Preparation** — Launches an invisible Excel Application instance, adds a new workbook, and writes column headers to row 1.

4. **Row-by-Row Export** — Iterates the recordset. For each row, writes all 6 fields to the corresponding Excel columns. If `Variance` is non-zero, applies a light red background (`RGB(255, 199, 199)`) to the Variance cell to flag discrepancies for the accountant.

5. **File Save & Cleanup** — After exhausting the recordset, auto-fits columns A through F, builds an output file path (`ReconcileReport_yyyymm.xlsx`), saves as XLSX (format code `51` = `xlOpenXMLWorkbook`), closes the workbook, quits the Excel process, and releases all COM object references.

## 3. Parameter Analysis

| No | Parameter Name | Type | Business Description |
|----|---------------|------|---------------------|
| 1 | `batchID` | `Long` | Import batch identifier. Specifies which batch's reconciliation data to export. `-1` (default) means "use the most recent batch" via the global `modUtil.gCurrentBatchID`. Positive values target a specific import batch, allowing reports for historical batches to be generated independently. |

**External state read:**

| Source | Field | Business Description |
|--------|-------|---------------------|
| `modUtil` module | `gCurrentBatchID` | Global variable tracking the most recently imported batch number. Used as the fallback when `batchID` is `-1`. Set at the end of `modImport.RunImport()`. |

## 4. CRUD Operations / Called Services

### Database Tables (code↔DB map, from the code graph)

| Access Table | Foreign-key reference → |
|---|---|
| tblReconcileResult | tblTransactions (via `TransID`) |
| tblTransactions | tblImportBatch (via `ImportBatchID`) |

### Analysis of method calls and their effect on the data model

This method is a **pure read (R) operation** — it queries reconciled reconciliation data but does not modify, create, delete, or insert any records. All writes to `tblReconcileResult` are performed exclusively by `modReconcile.RunReconcile()`.

| CRUD | SC / CBS | SC Code | Entity / DB | Operation Description |
|------|----------|---------|-------------|----------------------|
| R | (inline SQL) | N/A | `tblReconcileResult`, `tblTransactions` | Reads reconciled result rows (RefNo, Branch, TxnDate, Amount, MatchStatus, Variance) joined on `TransID` for the specified `ImportBatchID`. Used to populate the export report. |

No service components (SC/CBS) are called. No create, update, or delete operations occur.

## 5. Dependency Trace

No callers were found within the VBA source files. This method is likely invoked from a UI form button (e.g., a "Export Report" control on a reconciliation form, such as `frmReconcile`) that is not represented in the `.bas` module source tree, or called programmatically from a form's `.frm` code-behind.

| # | Caller (Screen/Batch) | Call Chain (Full Path to this Method) | Terminal (SC / CRUD / Entity) |
|---|----------------------|--------------------------------------|-------------------------------|
| 1 | Unknown (likely form button) | `(Form Control) -> modExport.RunExport` | N/A |
| 2 | (Not found in source tree) | `RunExport` called with optional `batchID` parameter | `tblReconcileResult` [R], `tblTransactions` [R] |

**Reverse dependency — methods called by this method:**

| Method Called | Type | Called From |
|---------------|------|-------------|
| `CurrentDb()` | ADO/DAO | `modExport.RunExport` (L11) |
| `db.OpenRecordset(...)` | ADO/DAO | `modExport.RunExport` (L12-15) |
| `modUtil.gCurrentBatchID` | Module-level variable read | `modExport.RunExport` (L10) |

**Dependent modules:**

| Module | Relationship |
|--------|-------------|
| `modImport` | Produces the data (`tblTransactions`) that `RunExport` reads |
| `modReconcile` | Produces the data (`tblReconcileResult`) that `RunExport` reads |
| `modUtil` | Provides `gCurrentBatchID` global and utility helpers |

## 6. Per-Branch Detail Blocks

### Block 1 — [ASSIGN] `effectiveBatchID` resolution (L10)

> Determines the actual batch ID to use. If the caller passes the sentinel value `-1`, the method falls back to the most recently imported batch tracked in the global variable.

| # | Type | Code |
|---|------|------|
| 1 | SET | `effectiveBatchID = IIf(batchID = -1, modUtil.gCurrentBatchID, batchID)` |

- **Condition**: If `batchID = -1` → `effectiveBatchID = modUtil.gCurrentBatchID` (most recent import batch)
- **Condition**: If `batchID <> -1` → `effectiveBatchID = batchID` (explicitly requested batch)

### Block 2 — [ASSIGN] DB and Recordset setup (L11-15)

> Opens the current Access database and executes a JOIN query over `tblReconcileResult` and `tblTransactions` to retrieve all reconciled rows for the effective batch.

| # | Type | Code |
|---|------|------|
| 1 | SET | `Set db = CurrentDb()` |
| 2 | SET | `Set rs = db.OpenRecordset("SELECT t.RefNo, t.Branch, t.TxnDate, t.Amount, r.MatchStatus, r.Variance FROM tblReconcileResult r INNER JOIN tblTransactions t ON r.TransID = t.TransID WHERE t.ImportBatchID = " & effectiveBatchID)` |

### Block 3 — [ASSIGN] Excel application setup (L17-21)

> Creates an Excel application instance (hidden), adds a new workbook, and gets a reference to the first worksheet.

| # | Type | Code |
|---|------|------|
| 1 | SET | `Set xlApp = CreateObject("Excel.Application")` |
| 2 | EXEC | `xlApp.Visible = False` |
| 3 | SET | `Set xlWb = xlApp.Workbooks.Add` |
| 4 | SET | `Set xlSheet = xlWb.Sheets(1)` |

### Block 4 — [ASSIGN] Column headers (L23-28)

> Writes the 6 column header labels to row 1 of the Excel sheet, defining the report schema.

| # | Type | Code |
|---|------|------|
| 1 | SET | `xlSheet.Cells(1, 1).Value = "RefNo"` |
| 2 | SET | `xlSheet.Cells(1, 2).Value = "Branch"` |
| 3 | SET | `xlSheet.Cells(1, 3).Value = "TxnDate"` |
| 4 | SET | `xlSheet.Cells(1, 4).Value = "Amount"` |
| 5 | SET | `xlSheet.Cells(1, 5).Value = "MatchStatus"` |
| 6 | SET | `xlSheet.Cells(1, 6).Value = "Variance"` |

### Block 5 — [LOOP] Row-by-row recordset iteration (L30-43)

> Iterates every record in the reconciliation result set, writing each row's 6 fields to the Excel sheet. Non-zero variance cells are highlighted with a light red background.

| # | Type | Code |
|---|------|------|
| 1 | SET | `r = 2` (first data row, row 1 is headers) |
| 2 | WHILE | `Do While Not rs.EOF` (L30) |
| 3 | SET | `xlSheet.Cells(r, 1).Value = rs!RefNo` |
| 4 | SET | `xlSheet.Cells(r, 2).Value = rs!Branch` |
| 5 | SET | `xlSheet.Cells(r, 3).Value = rs!TxnDate` |
| 6 | SET | `xlSheet.Cells(r, 4).Value = rs!Amount` |
| 7 | SET | `xlSheet.Cells(r, 5).Value = rs!MatchStatus` |
| 8 | SET | `xlSheet.Cells(r, 6).Value = rs!Variance` |
| 9 | INC | `r = r + 1` |
| 10 | EXEC | `rs.MoveNext` |

#### Block 5.1 — [IF] Non-zero variance highlighting (L38-40)

> If the variance for this row is not zero, the Variance cell (column F) is highlighted with a light red fill color to draw the accountant's attention to the discrepancy.

| # | Type | Code |
|---|------|------|
| 1 | IF | `rs!Variance <> 0` |
| 2 | EXEC | `xlSheet.Range(xlSheet.Cells(r, 6), xlSheet.Cells(r, 6)).Interior.Color = RGB(255, 199, 199)` |

- **Condition**: If `Variance <> 0` → Apply red highlight (`RGB(255, 199, 199)`) to cell at row `r`, column 6 (Variance column)
- **Condition**: If `Variance = 0` → Skip (no highlighting needed; the transaction is balanced)

### Block 6 — [ASSIGN] Recordset close (L44)

| # | Type | Code |
|---|------|------|
| 1 | EXEC | `rs.Close` |

### Block 7 — [ASSIGN] Column auto-fit (L46)

| # | Type | Code |
|---|------|------|
| 1 | EXEC | `xlSheet.Columns("A:F").AutoFit` |

### Block 8 — [ASSIGN] File path construction and save (L48-49)

> Builds a timestamped output filename using the current year-month (`yyyymm`) and saves the workbook as an XLSX file.

| # | Type | Code |
|---|------|------|
| 1 | SET | `outPath = CurrentProject.Path & "\ReconcileReport_" & Format(Now(), "yyyymm") & ".xlsx"` |
| 2 | EXEC | `xlWb.SaveAs outPath, 51` |

- `51` = `xlOpenXMLWorkbook` (XLSX format, Excel 2007+)

### Block 9 — [ASSIGN] COM cleanup (L50-54)

> Releases all Excel and database COM object references to prevent orphaned Excel processes.

| # | Type | Code |
|---|------|------|
| 1 | EXEC | `xlWb.Close SaveChanges:=False` |
| 2 | EXEC | `xlApp.Quit` |
| 3 | SET | `Set xlWb = Nothing` |
| 4 | SET | `Set xlApp = Nothing` |
| 5 | SET | `Set db = Nothing` |

### Block 10 — [RETURN] Output path (L56)

| # | Type | Code |
|---|------|------|
| 1 | RETURN | `RunExport = outPath` |

## 7. Glossary

| Term | Type | Business Meaning |
|------|------|------------------|
| `batchID` | Parameter | Import batch identifier — a numeric token that groups all transactions imported from a single file upload. Used to isolate reconciliation and export to a specific batch. |
| `effectiveBatchID` | Field | Resolved batch ID — the actual batch number used for queries after applying the fallback logic (`-1` → `gCurrentBatchID`). |
| `gCurrentBatchID` | Global | The most recently imported batch number, set by `modImport.RunImport()` at the end of the import process. Acts as the default target for export. |
| `RefNo` | Field | Transaction reference number — a unique or semi-unique identifier on each transaction, used for matching against bank statements. |
| `Branch` | Field | The originating branch code or name for a transaction, populated from the imported file (or `"BANK"` for bank statement files). |
| `TxnDate` | Field | Transaction date — the date the transaction occurred, as recorded in the imported data. |
| `Amount` | Field | Transaction amount — the monetary value of the transaction, stored as Currency type. |
| `MatchStatus` | Field | Reconciliation match classification — one of `"Matched"`, `"Matched (Fuzzy)"`, `"Duplicate"`, or `"Unmatched"`, indicating how the transaction was matched (or not matched) against the bank statement. |
| `Variance` | Field | Difference between the transaction amount and the matched bank statement amount — zero means exact match, non-zero flags a discrepancy requiring review. |
| `tblTransactions` | Table | Import transactions — stores branch-level transactions imported from non-bank files, linked to `tblImportBatch` via `ImportBatchID`. |
| `tblBankStatement` | Table | Bank statement records — stores transactions imported from bank statement files, matched against `tblTransactions` during reconciliation. |
| `tblReconcileResult` | Table | Reconciliation output — stores the results of matching transactions against bank statements, including match status and variance amounts. |
| `tblImportBatch` | Table | Import batch metadata — tracks each file import with filename, import date, row count, and status. |
| `ImportBatchID` | Field/Column | Foreign key linking `tblTransactions` and `tblReconcileResult` to `tblImportBatch`. |
| `TransID` | Field/Column | Transaction record ID — primary key used to join `tblTransactions` with `tblReconcileResult`. |
| `xlOpenXMLWorkbook` | Constant | Excel file format code `51` — represents the XLSX format introduced in Excel 2007, without macros. |
| `RGB(255, 199, 199)` | Value | Light red color — used to highlight variance cells with non-zero discrepancies, visually alerting accounting staff to potential issues. |
| `AutoFit` | Excel method | Automatically adjusts column widths to fit the longest content in each column, improving readability of the exported report. |

---