---

# Business Logic — modImport.RunImport() [97 LOC]

| Field | Value |
|-------|-------|
| Fully Qualified Name | `modImport.RunImport()` |
| Layer | Utility (Package: `vba-source/modImport.bas`) |
| Module | `modImport` (Package: `vba-source/modImport.bas`) |

## 1. Role

### modImport.RunImport()

This method performs the core business operation of importing financial transaction data from Excel spreadsheet files into Microsoft Access database tables. It accepts an Excel file path provided through the `frmImport` form's `txtFilePath` control and determines the file type by inspecting the filename — if the filename contains "BankStatement" (case-insensitive), the rows are loaded into the `tblBankStatement` table; otherwise, they are loaded into the `tblTransactions` table.

The method implements a **retry pattern** for Excel file opening, attempting up to 3 times with 2-second waits between attempts to handle file-lock contention (Excel COM error 70: "Permission Denied"). It wraps every incoming row through `clsTransaction.IsValid()` validation — rejecting rows with non-positive amounts, blank references, missing branches, or dates before 2000 — and logs each invalid row for review.

After processing all rows, the method records a summary batch record in `tblImportBatch` containing the filename, import timestamp, successful row count, and status, then updates the `frmImport!lblStatus` control with a human-readable summary and returns the batch ID to the caller.

Its role in the larger system is as the primary entry point for the import workflow, invoked by users clicking an "Import" button on the `frmImport` form. It acts as a utility-level orchestrator that coordinates COM automation (Excel), data validation, conditional routing, and audit-trail batch recording.

## 2. Processing Pattern (Detailed Business Logic)

```mermaid
flowchart TD
    START(["RunImport starts"])
    START --> GET_PATH["Get filePath from frmImport"]
    GET_PATH --> GET_FILE["Get fileName from filePath"]
    GET_FILE --> CHECK_BANK["isBankFile = fileName contains BankStatement?"]
    CHECK_BANK --> YES_BANK["Yes: isBankFile = True"]
    CHECK_BANK --> NO_BANK["No: isBankFile = False"]
    YES_BANK --> OPEN_DB["Open DAO database (CurrentDb)"]
    NO_BANK --> OPEN_DB
    OPEN_DB --> BATCH["Get next batch number (modUtil.GetNextBatchNumber)"]
    BATCH --> ATTEMPT_LOOP["Initialize attempt = 0"]
    ATTEMPT_LOOP --> INC_ATTEMPT["Increment attempt"]
    INC_ATTEMPT --> CREATE_XL["Create Excel.Application COM object"]
    CREATE_XL --> OPEN_WB["Open workbook (ReadOnly)"]
    OPEN_WB --> CHECK_ERR{Err.Number = 70<br/>File Locked?}
    CHECK_ERR --> YES_LOCK["Yes AND attempt < MAX_ATTEMPTS"]
    CHECK_ERR --> DONE["No: Exit loop"]
    YES_LOCK --> CLEANUP["Release resources, Sleep 2000ms"]
    CLEANUP --> INC_ATTEMPT
    DONE --> CHECK_XL{xlWb Is Nothing?}
    CHECK_XL --> YES_NULL["Yes"]
    CHECK_XL --> NO_NULL["No: Continue import"]
    YES_NULL --> LOG_FAIL["Log error: Could not open file"]
    LOG_FAIL --> UI_FAIL["Set lblStatus to 'Import failed: file locked.'"]
    UI_FAIL --> RET_BATCH["Return batchID (early exit)"]
    NO_NULL --> GET_SHEET["Get first worksheet (Sheets(1))"]
    GET_SHEET --> LAST_ROW["Find lastRow via TxnDate column xlUp"]
    LAST_ROW --> INIT_COUNTS["rowCount = 0, errCount = 0"]
    INIT_COUNTS --> LOOP_START["For r = 2 To lastRow"]
    LOOP_START --> NEW_TXN["New clsTransaction instance"]
    NEW_TXN --> MAP_FIELDS["Map cells to t.TransID, TxnDate, Amount, RefNo, Branch"]
    MAP_FIELDS --> BRANCH_LOGIC["Branch = IIf(isBankFile, 'BANK', cell(1))"]
    BRANCH_LOGIC --> VALID_CHECK{"t.IsValid()?"}
    VALID_CHECK --> FALSE_INVALID["No: Invalid row"]
    VALID_CHECK --> TRUE_VALID["Yes"]
    FALSE_INVALID --> INC_ERR["errCount + 1"]
    INC_ERR --> LOG_ERROR["modUtil.LogError batchID, r, 'Invalid row'"]
    LOG_ERROR --> NEXT_ROW["Next r"]
    TRUE_VALID --> IS_BANK_CHECK{isBankFile?}
    IS_BANK_CHECK --> YES_BANK_INS["Yes"]
    IS_BANK_CHECK --> NO_BANK_INS["No"]
    YES_BANK_INS --> INS_BANK["INSERT INTO tblBankStatement<br/>TxnDate, Amount, BankRefNo, ImportBatchID"]
    NO_BANK_INS --> INS_TXN["INSERT INTO tblTransactions<br/>Branch, TxnDate, Amount, RefNo, ImportBatchID"]
    INS_BANK --> INC_ROW["rowCount + 1"]
    INS_TXN --> INC_ROW
    INC_ROW --> NEXT_ROW
    NEXT_ROW --> LOOP_END{r <= lastRow?}
    LOOP_END --> YES_ROW["Yes"]
    LOOP_END --> CLOSE_XL["No: Close workbook, Quit Excel"]
    YES_ROW --> NEW_TXN
    CLOSE_XL --> INS_BATCH["INSERT INTO tblImportBatch<br/>FileName, ImportDate, RowCount, Status"]
    INS_BATCH --> SET_GLOBAL["modUtil.gCurrentBatchID = batchID"]
    SET_GLOBAL --> UI_SUCCESS["Set lblStatus to 'Imported N rows, M errors'"]
    UI_SUCCESS --> RET_BATCH_END["Return batchID"]
    RET_BATCH --> FINAL_END(["End"])
    RET_BATCH_END --> FINAL_END
```

**CRITICAL — Constant Resolution:**
- `MAX_ATTEMPTS = 3` — Local constant (declared at line 22). Maximum number of attempts to open the Excel file before giving up and returning an error.

## 3. Parameter Analysis

| No | Parameter Name | Type | Business Description |
|----|---------------|------|---------------------|
| - | `filePath` (derived from form) | String | File system path to the Excel file to import. Read from `Forms!frmImport!txtFilePath.Value` — this field is populated by the `BrowseForFile()` method which uses the OS file picker dialog. |
| - | `batchID` | Long | Internally generated import batch number obtained from `modUtil.GetNextBatchNumber()`. Serves as a foreign-key reference linking imported rows to the batch summary record in `tblImportBatch`. |
| - | `isBankFile` | Boolean | Derived flag — `True` if the filename contains "BankStatement" (case-insensitive), `False` otherwise. Controls which target table receives the imported rows. |
| - | `attempt` | Integer | Retry counter for the Excel file-opening loop. Starts at 0, incremented before each attempt. Maximum value is `MAX_ATTEMPTS = 3`. |

**External state read by this method:**
| Field | Type | Business Description |
|-------|------|---------------------|
| `Forms!frmImport!txtFilePath.Value` | String | User-selected file path displayed in the import form. |
| `Forms!frmImport!lblStatus.Caption` | String | Status label on the import form — updated at the end of processing to show results. |
| `modUtil.gCurrentBatchID` | Long | Global variable holding the current batch ID, written back after import completes. |

## 4. CRUD Operations / Called Services

### Pre-computed evidence from code analysis graph:

| CRUD | SC / CBS | SC Code | Entity / DB | Operation Description |
|------|----------|---------|-------------|----------------------|
| R | `modUtil.GetNextBatchNumber` | mod | - | Calls `GetNextBatchNumber` in `modUtil` — retrieves the next available batch number |
| - | `modUtil.LogError` | mod | - | Calls `LogError` in `modUtil` — logs an error record with batch ID, row number, and message |
| - | `clsTransaction.IsValid` | clsTransaction | - | Calls `IsValid` in `clsTransaction` — validates transaction data fields |
| - | `modUtil.FormatDateForSql` | mod | - | Calls `FormatDateForSql` in `modUtil` — formats a date value for safe SQL embedding |
| - | `modUtil.FormatNumberForSql` | mod | - | Calls `FormatNumberForSql` in `modUtil` — formats a currency/number value for safe SQL embedding |
| C | (inline SQL) | modImport | `tblBankStatement` | Inserts a new bank statement record — TxnDate, Amount, BankRefNo, ImportBatchID |
| C | (inline SQL) | modImport | `tblTransactions` | Inserts a new transaction record — Branch, TxnDate, Amount, RefNo, ImportBatchID |
| C | (inline SQL) | modImport | `tblImportBatch` | Inserts the batch summary record — FileName, ImportDate, RowCount, Status |

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

| Access Table | Foreign-key reference → |
|---|---|
| tblBankStatement | tblImportBatch (via ImportBatchID) |
| tblImportBatch | — |
| tblTransactions | tblImportBatch (via ImportBatchID) |

**Classification summary:**
- **Create (C):** Three `INSERT INTO` statements execute inline SQL. Two route to `tblBankStatement` or `tblTransactions` based on `isBankFile`, and one creates the batch summary record in `tblImportBatch`.
- **Read (R):** `modUtil.GetNextBatchNumber()` returns a batch number. No explicit `SELECT` queries are executed — data is read from Excel via COM automation.
- **No Update or Delete operations** — this method only creates new records.

## 5. Dependency Trace

No external callers were found in the codebase outside of `modImport.bas` itself. This method is invoked from the `frmImport` user interface — users supply the file path via a file picker dialog (the `BrowseForFile()` method in the same module populates `txtFilePath`) and then trigger `RunImport()` (likely via a button click event handler on the form).

| # | Caller (Screen/Batch) | Call Chain (Full Path to this Method) | Terminal (SC / CRUD / Entity) |
|---|----------------------|--------------------------------------|-------------------------------|
| 1 | Form: frmImport | `frmImport` button click event -> `modImport.RunImport` | `INSERT tblBankStatement [C] tblBankStatement`; `INSERT tblTransactions [C] tblTransactions`; `INSERT tblImportBatch [C] tblImportBatch` |

## 6. Per-Branch Detail Blocks

**Block 1** — VARIABLE DECLUSIONS (constant and local variables) (L8–22)

| # | Type | Code |
|---|------|------|
| 1 | SET | `filePath As String` — variable to hold the Excel file path |
| 2 | SET | `batchID As Long` — variable to hold the batch identifier |
| 3 | SET | `isBankFile As Boolean` — flag for file type detection |
| 4 | SET | `xlApp As Object` — late-bound Excel.Application COM object |
| 5 | SET | `xlWb As Object` — late-bound Excel.Workbook COM object |
| 6 | SET | `xlSheet As Object` — late-bound Excel.Worksheet COM object |
| 7 | SET | `r As Long` — row counter for Excel iteration |
| 8 | SET | `lastRow As Long` — last used row index in the worksheet |
| 9 | SET | `rowCount As Long` — count of successfully imported rows |
| 10 | SET | `errCount As Long` — count of rows rejected by validation |
| 11 | SET | `db As DAO.Database` — database handle from CurrentDb() |
| 12 | SET | `fileName As String` — derived file name from the path |
| 13 | SET | `attempt As Integer` — retry attempt counter |
| 14 | CONST | `MAX_ATTEMPTS As Integer = 3` — maximum Excel open attempts [MAX_ATTEMPTS=3] |

**Block 2** — FILE PATH EXTRACTION AND TYPE DETECTION (L24–26)

| # | Type | Code |
|---|------|------|
| 1 | SET | `filePath = Forms!frmImport!txtFilePath.Value` — read user-selected file path from the import form |
| 2 | SET | `fileName = Dir(filePath)` — extract file name (with extension) from the full path |
| 3 | SET | `isBankFile = (InStr(1, fileName, "BankStatement", vbTextCompare) > 0)` — case-insensitive check: True if "BankStatement" appears in the filename |

**Block 3** — DATABASE AND BATCH SETUP (L28–29)

| # | Type | Code |
|---|------|------|
| 1 | SET | `db = CurrentDb()` — open a new DAO database connection |
| 2 | SET | `batchID = modUtil.GetNextBatchNumber()` — fetch next available batch number |

**Block 4** — EXCEL OPENING RETRY LOOP (L31–47)

> Attempts to launch Excel and open the workbook, retrying up to 3 times on file-lock errors (COM error 70).

**Block 4** — DO LOOP (retry loop, attempt < MAX_ATTEMPTS) (L31–47) [MAX_ATTEMPTS=3]

| # | Type | Code |
|---|------|------|
| 1 | SET | `attempt = attempt + 1` — increment retry counter |
| 2 | EXEC | `On Error Resume Next` — suppress errors for COM operations |
| 3 | EXEC | `Err.Clear` — clear any pending error state |
| 4 | SET | `xlApp = CreateObject("Excel.Application")` — launch Excel COM server (late-bound) |
| 5 | EXEC | `xlApp.Visible = False` — run Excel invisibly |
| 6 | SET | `xlWb = xlApp.Workbooks.Open(filePath, ReadOnly:=True)` — open the workbook read-only |
| 7 | EXEC | `On Error GoTo 0` — re-enable normal error handling |

**Block 4.1** — IF (error handling: lock contention) (L40–46)

| # | Type | Code |
|---|------|------|
| 1 | IF | `Err.Number = 70 And attempt < MAX_ATTEMPTS` [Err 70 = Permission Denied / file locked; MAX_ATTEMPTS=3] |
| 2 | SET | `xlWb = Nothing` — discard failed workbook reference |
| 3 | EXEC | `xlApp.Quit` — close the Excel instance |
| 4 | SET | `xlApp = Nothing` — release Excel COM object |
| 5 | EXEC | `Sleep 2000` — wait 2 seconds before retrying |
| 6 | ELSE-IF | `Else` (no error, or attempt exhausted) — exit the retry loop |

**Block 5** — ERROR EXIT IF EXCEL COULD NOT BE OPENED (L49–53)

> If all retry attempts were exhausted and xlWb remains Nothing, the method logs the failure and returns early.

**Block 5** — IF (xlWb Is Nothing?) (L49)

| # | Type | Code |
|---|------|------|
| 1 | IF | `xlWb Is Nothing` — workbook failed to open after all retries |
| 2 | CALL | `modUtil.LogError(batchID, 0, "Could not open file after " & MAX_ATTEMPTS & " attempts: " & filePath)` — log the failure with row 0 (no specific row) |
| 3 | SET | `Forms!frmImport!lblStatus.Caption = "Import failed: file locked."` — update UI status |
| 4 | RETURN | `RunImport = batchID` — return the batch ID so the caller knows which batch was attempted |
| 5 | EXEC | `Exit Function` — early exit |

**Block 6** — EXCEL WORKSHEET PREPARATION (L55–62)

| # | Type | Code |
|---|------|------|
| 1 | SET | `xlSheet = xlWb.Sheets(1)` — reference the first worksheet |
| 2 | SET | `lastRow = xlSheet.Cells(xlSheet.Rows.Count, 2).End(-4162).Row` — find last row by scanning column 2 (TxnDate) upward; uses -4162 = xlUp (late-bound constant); column 1 is intentionally skipped because it is blank for bank files |
| 3 | SET | `rowCount = 0` — reset successful import counter |
| 4 | SET | `errCount = 0` — reset error counter |

**Block 7** — ROW ITERATION LOOP (L64–90)

> Iterates Excel rows from 2 (header row skipped) to the last populated row. Each row is mapped to a `clsTransaction` object, validated, and either inserted or rejected.

**Block 7** — FOR LOOP (row iteration) (L64–90)

| # | Type | Code |
|---|------|------|
| 1 | FOR | `For r = 2 To lastRow` — iterate from row 2 to last used row (row 1 is header) |

**Block 7.1** — TRANSACTION OBJECT CREATION AND FIELD MAPPING (L65–70)

| # | Type | Code |
|---|------|------|
| 1 | SET | `t As clsTransaction` — declare local transaction variable |
| 2 | SET | `Set t = New clsTransaction` — instantiate a new transaction object |
| 3 | SET | `t.TransID = 0` — set transaction ID to 0 (not used in import) |
| 4 | SET | `t.TxnDate = xlSheet.Cells(r, 2).Value` — map Excel column B (TxnDate) |
| 5 | SET | `t.Amount = xlSheet.Cells(r, 3).Value` — map Excel column C (Amount) |
| 6 | SET | `t.RefNo = CStr(xlSheet.Cells(r, 4).Value)` — map Excel column D (RefNo), cast to string |
| 7 | SET | `t.Branch = IIf(isBankFile, "BANK", xlSheet.Cells(r, 1).Value)` [isBankFile flag] — use "BANK" literal for bank files; otherwise read from Excel column A (Branch) |

**Block 7.2** — IF (validation check) (L72)

| # | Type | Code |
|---|------|------|
| 1 | IF | `Not t.IsValid()` — transaction failed validation |

**Block 7.2.1** — ELSE (valid transaction — inner branch) — implicit else of `If Not t.IsValid()` (L72)

| # | Type | Code |
|---|------|------|
| 1 | SET | `errCount = errCount + 1` — increment error counter |
| 2 | CALL | `modUtil.LogError(batchID, r, "Invalid row: Amount/RefNo/TxnDate failed validation")` — log the invalid row |

> **Block 7.2.2** — ELSE (valid transaction) — implicit else branch

| # | Type | Code |
|---|------|------|
| 1 | IF | `isBankFile` — true if file type is bank statement |

**Block 7.2.2.1** — INSERT (bank statement table) (L76–78)

| # | Type | Code |
|---|------|------|
| 1 | CALL | `db.Execute "INSERT INTO tblBankStatement (TxnDate, Amount, BankRefNo, ImportBatchID) VALUES (...)"` — insert bank transaction record using SQL concatenation. Fields: TxnDate (formatted), Amount (formatted), BankRefNo (single-quotes escaped), batchID |
| 2 | CALL | `modUtil.FormatDateForSql(t.TxnDate)` — format the date for SQL safety |
| 3 | CALL | `modUtil.FormatNumberForSql(t.Amount)` — format the currency for SQL safety |
| 4 | EXEC | `Replace(t.RefNo, "'", "''")` — escape single quotes in the reference number |

**Block 7.2.2.2** — INSERT (transactions table) — else of isBankFile (L79–80)

| # | Type | Code |
|---|------|------|
| 1 | CALL | `db.Execute "INSERT INTO tblTransactions (Branch, TxnDate, Amount, RefNo, ImportBatchID) VALUES (...)"` — insert general transaction record. Fields: Branch (from column A), TxnDate (formatted), Amount (formatted), RefNo (escaped), batchID |
| 2 | CALL | `modUtil.FormatDateForSql(t.TxnDate)` — format the date for SQL safety |
| 3 | CALL | `modUtil.FormatNumberForSql(t.Amount)` — format the currency for SQL safety |
| 4 | EXEC | `Replace(t.RefNo, "'", "''")` — escape single quotes in the reference number |

**Block 7.2.3** — ROW COUNT INCREMENT (L82)

| # | Type | Code |
|---|------|------|
| 1 | SET | `rowCount = rowCount + 1` — increment successful import counter |

**Block 8** — EXCEL CLEANUP (L86–89)

| # | Type | Code |
|---|------|------|
| 1 | EXEC | `xlWb.Close SaveChanges:=False` — close workbook without saving |
| 2 | EXEC | `xlApp.Quit` — quit Excel application |
| 3 | SET | `xlWb = Nothing` — release workbook reference |
| 4 | SET | `xlApp = Nothing` — release Excel COM object |

**Block 9** — BATCH SUMMARY RECORD CREATION (L91–92)

| # | Type | Code |
|---|------|------|
| 1 | CALL | `db.Execute "INSERT INTO tblImportBatch (FileName, ImportDate, [RowCount], [Status]) VALUES ('" & fileName & "', Now(), " & rowCount & ", 'Imported')"` — create batch summary. FileName (escaped), current timestamp, row count, status = 'Imported' |
| 2 | EXEC | `Replace(fileName, "'", "''")` — escape single quotes in the file name |
| 3 | EXEC | `Now()` — get current timestamp for ImportDate |

**Block 10** — STATUS UPDATE AND RETURN (L94–97)

| # | Type | Code |
|---|------|------|
| 1 | SET | `modUtil.gCurrentBatchID = batchID` — persist batch ID to global variable |
| 2 | SET | `Forms!frmImport!lblStatus.Caption = "Imported " & rowCount & " rows, " & errCount & " errors. Batch #" & batchID` — update UI with human-readable summary |
| 3 | RETURN | `RunImport = batchID` — return the batch ID to the caller |
| 4 | SET | `Set db = Nothing` — release database connection |

## 7. Glossary

| Term | Type | Business Meaning |
|------|------|------------------|
| `tblBankStatement` | Database Table | Bank statement transaction records — holds imported bank-specific transactions (no Branch field; Branch is fixed to "BANK"). |
| `tblTransactions` | Database Table | General transaction records — holds imported non-bank transactions (includes Branch, TxnDate, Amount, RefNo, ImportBatchID). |
| `tblImportBatch` | Database Table | Import batch summary table — tracks each import job with filename, timestamp, row count, and status. |
| `ImportBatchID` | Field | Foreign key in `tblBankStatement` and `tblTransactions` linking imported rows to their batch summary in `tblImportBatch`. |
| `txndate` | Field | Transaction date — the date of the financial transaction, imported from Excel column B. |
| `amount` | Field | Transaction amount — the monetary value of the transaction, imported from Excel column C (Currency type). |
| `refno` | Field | Reference number — a text identifier for the transaction, imported from Excel column D. |
| `branch` | Field | Branch identifier — for bank files, always "BANK"; for general files, read from Excel column A. |
| `isbankfile` | Field/Flag | Derived boolean — True if the filename contains "BankStatement" (case-insensitive). Controls which target table receives the data. |
| `clsTransaction` | Class | Transaction entity class — holds transaction data fields and provides the `IsValid()` method for data validation. |
| `modUtil` | Module | Utility module — provides helper functions (`GetNextBatchNumber`, `LogError`, `FormatDateForSql`, `FormatNumberForSql`) used across the application. |
| `gCurrentBatchID` | Global Variable | Global batch ID variable — set after import to track the current active batch session. |
| `frmImport` | Form | Import form — the user interface where users select files and view import status. |
| `txtFilePath` | Form Control | Text box on `frmImport` — holds the selected file path, populated by `BrowseForFile()`. |
| `lblStatus` | Form Control | Label on `frmImport` — displays import progress and result summary to the user. |
| `BrowseForFile` | Method | Helper method in `modImport` — opens the OS file picker dialog (msoFileDialogFilePicker) filtered to Excel files (*.xlsx). |
| `DAO.Database` | Technology | Data Access Objects — the legacy Microsoft Access database API used for executing SQL statements. |
| `COM` | Technology | Component Object Model — the Windows automation technology used to control Excel (CreateObject("Excel.Application")). |
| `Err.Number = 70` | Error Code | VBA error 70: "Permission Denied" — occurs when the Excel file is open in another process, or the file is locked. |
| `xlUp (-4162)` | Excel Constant | Excel's built-in enumeration for "move up from end of range to find last cell" — used late-bound (numeric value) since VBA cannot access the named constant. |
| `CurrentDb()` | DAO Method | DAO function that returns a Database object connected to the current Access front-end database. |
| `MAX_ATTEMPTS = 3` | Constant | Local constant limiting the Excel file-open retry loop to 3 attempts. |

---