Attribute VB_Name = "modExport"
Option Compare Database
Option Explicit

Public Function RunExport(Optional batchID As Long = -1) As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlSheet As Object
    Dim effectiveBatchID As Long
    Dim outPath As String
    Dim r As Long

    effectiveBatchID = IIf(batchID = -1, modUtil.gCurrentBatchID, batchID)
    Set db = CurrentDb()
    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)

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    Set xlWb = xlApp.Workbooks.Add
    Set xlSheet = xlWb.Sheets(1)

    xlSheet.Cells(1, 1).Value = "RefNo"
    xlSheet.Cells(1, 2).Value = "Branch"
    xlSheet.Cells(1, 3).Value = "TxnDate"
    xlSheet.Cells(1, 4).Value = "Amount"
    xlSheet.Cells(1, 5).Value = "MatchStatus"
    xlSheet.Cells(1, 6).Value = "Variance"

    r = 2
    Do While Not rs.EOF
        xlSheet.Cells(r, 1).Value = rs!RefNo
        xlSheet.Cells(r, 2).Value = rs!Branch
        xlSheet.Cells(r, 3).Value = rs!TxnDate
        xlSheet.Cells(r, 4).Value = rs!Amount
        xlSheet.Cells(r, 5).Value = rs!MatchStatus
        xlSheet.Cells(r, 6).Value = rs!Variance
        If rs!Variance <> 0 Then
            xlSheet.Range(xlSheet.Cells(r, 6), xlSheet.Cells(r, 6)).Interior.Color = RGB(255, 199, 199)
        End If
        r = r + 1
        rs.MoveNext
    Loop
    rs.Close

    xlSheet.Columns("A:F").AutoFit

    outPath = CurrentProject.Path & "\ReconcileReport_" & Format(Now(), "yyyymm") & ".xlsx"
    xlWb.SaveAs outPath, 51 ' 51 = xlOpenXMLWorkbook
    xlWb.Close SaveChanges:=False
    xlApp.Quit
    Set xlWb = Nothing
    Set xlApp = Nothing
    Set db = Nothing

    RunExport = outPath
End Function