DoCmd.TransferSpreadsheet only works once while db is open

Question

I'm working with some vba for a command to transfer the results of a query to a spreadsheet, then perform a lot of formatting for the excel worksheet.  I can get the transfer and formatting to work, except for a print to page, which I commented out for the meantime.  The issue I'd like to fix first is that the command only works one time while the db is open.  If I try to run the code a 2nd time without first closing then reopening the db, I get this error msg:

Run-time error '91':

Object variable or With block variable not set

The line where the error occurs is in bold.  I've only included the first two pieces of formatting.

Private Sub Command13_Click()

Dim myQuery As String

Dim FileName As String

Dim XlRpt As Excel.Application

Set XlRpt = CreateObject("Excel.Application")

myQuery = "qryComp_Excel"

FileName = "C:\XlCompsTest3.xlsx"

If Len(Dir(FileName)) Then

Kill FileName

End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, myQuery, FileName, True

XlRpt.Workbooks.Open FileName, True, False

XlRpt.Columns("D:D").Select

XlRpt.Selection.Delete Shift:=xlToLeft

XlRpt.Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select

XlRpt.Cells.FormatConditions.Delete

XlRpt.Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select

XlRpt.Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _

Formula1:="=0"

XlRpt.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With XlRpt.Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 16751052

.TintAndShade = 0

End With

XlRpt.Selection.FormatConditions(1).StopIfTrue = False

XlRpt.Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select

XlRpt.Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=AND(1,MOD(ROW(),3)=1)"

XlRpt.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With XlRpt.Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 6737151

.TintAndShade = 0

End With

XlRpt.Visible = True

XlRpt.Application.DisplayAlerts = False

Set XlRpt = Nothing

DoCmd.Close

End Sub

Any ideas on why this only works once unless I close the db, and what I need to do to fix it?

Thanks!

Answer 1

When you use Automation, all Excel objects should refer back to the Excel.Application object that you create, either directly or indirectly.

xlRpt.Selection is OK, but Selection without referring to xlRpt is not OK - it will cause the problem that you mention. Change all instances of Selection to xlRpt.Selection. The same goes for ActiveCell - change it to xlRpt.ActiveCell.

Answer 2

Ah yes.  I was missing the reference to xlRpt in several locations where it was needed.  I've debugged it and it's working.

Thanks!