[AccessD] Automation Error 91 for Excel Checkbox

Hi Listers,

 

I am exporting data to an excel spreadsheet, then opening the spreadsheet to
add some checkboxes to make it easier for data entry.

 

My code runs fine the first time, but when I run it a second time (whether
for the same spreadsheet or to create a new one) I get error 91.

It doesn't seem to want to loop through the cell range.

If I close the database and reopen it then the code runs fine for the first
time again but when I run it a second time I get the same error.

 

Below is my code.  The line with the error is shown:

 

strFullName = Me!txtFileFolderPAMaint & "\" & strSCBranchFileName & " Post
Address Services " & Format(Date, "yyyy mm") & ".xlsx"

Kill strFullName 'In case already created today

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,
"dbo_ttmpBranchPAMaintenance", strFullName, True

 

    Dim xlObj As Excel.Application

    Dim intRow As Integer, cel As Range

 

    Set xlObj = CreateObject("Excel.Application")

    xlObj.Visible = True

    xlObj.Workbooks.Open strFullName

    For Each cel In xlObj.ActiveSheet.Range("K2:O2") 'Add checkboxes to each
of these cells

        xlObj.ActiveSheet.CheckBoxes.Add(Left:=cel.Left, Top:=cel.Top,
Width:=cel.Width, Height:=cel.Height).Select

        With Selection

           .Caption = "" ' ****** Error 91 Object variable or with block
variable not set ***************

           .Value = cel.Offset(0, -5)

           .LinkedCell = cel.Offset(0, -5).Address

           .Locked = False

        End With

    Next

    xlObj.ActiveWorkbook.Save

    xlObj.Quit

    Set xlObj = Nothing

 

Any help would be appreciated.

 

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand