Automation to Excel: Warnings

[tbellomo]

Hello all,

I have a couple of functions that do a formatted export to Excel using an xlt template.

Option Explicit

Dim XLApp As Object
Dim XLTemplate As Object
Dim XLSheet As Object


Public Function fncEE_SetupFile(locTemplate As String, locOutput As String)

Set XLApp = New Excel.Application
   ' XLApp.DisplayAlerts = False
Set XLTemplate = XLApp.Workbooks.Open(locTemplate)
    XLTemplate.SaveAs locOutput
    XLTemplate.Close
Set XLTemplate = Nothing
Set XLTemplate = XLApp.Workbooks.Open(locOutput)

End Function


Public Function fncEE_ExecuteDump(tabname, tablename As String, rowStart As Integer, Optional tblHeadings As Boolean)

Set XLSheet = XLTemplate.Worksheets(CStr(tabname))

Dim db As Database
Dim rs As Recordset
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(tablename)
Dim numCol As Integer
Dim colCnt As Integer
Dim numRow As Integer
Dim rowCnt As Integer
Dim rowOffset As Integer


numCol = rs.Fields.Count
rs.MoveLast
rs.MoveFirst
numRow = rs.RecordCount

    If tblHeadings = True Then
        rowOffset = rowStart
        For colCnt = 1 To numCol
            XLSheet.Range(XLSheet.Cells(rowOffset, colCnt).Address) = rs(colCnt - 1).Name
        Next colCnt
        rowOffset = rowOffset + 1
    Else
        rowOffset = rowStart
    End If

For rowCnt = 0 To numRow - 1
 For colCnt = 1 To numCol
    XLSheet.Range(XLSheet.Cells((rowCnt + rowOffset), colCnt).Address) = rs(colCnt - 1)
 Next colCnt
rs.MoveNext
Next rowCnt

    

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

'Resize all columns & rows with autofit
'XLSheet.Cells.Select
XLSheet.Cells.EntireColumn.AutoFit

End Function

Public Function fncEE_CloseExport()

Set XLSheet = Nothing
XLTemplate.Close
'XLApp.DisplayAlerts = True
XLApp.Quit
Set XLTemplate = Nothing
Set XLApp = Nothing
End Function

I split up the sections cause it makes it easier... anyway -- notice the "XLApp.DisplayAlerts" lines. When I have them uncommented (and thus no "are you sure you want to save?" alerts), the file that is generated is just a blank version of the template. But when the lines are commented out (and thus "are you...") everything's fine.

Any insight? Am I putting that line in the wrong spot?

Thanks,
Timo

 

[hymn]

Have you turned them off!
you need to before you turn them on?

Hope this helps
Hymn