[AccessD] Automation Error 91 for Excel Checkbox

Bill,

Thanks for your response.  I made the changes you suggested but with still
the same problem.

I did notice in task manager that after I created the first spreadsheet that
an instance of Excel was still open.  This disappears when the database is
closed.

Further information - the database is an accdb running in Access 2010.

Regards

David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Wednesday, 25 June 2014 4:21 p.m.
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automation Error 91 for Excel Checkbox

I would change the code as follows, not sure if it will help. Normally it is
not necessary and is counterproductive to Select ranges in Excel VBA. Also,
I have seen problems looping through parts of a range that is not
represented as the Cells collection. Also, be careful Killing anything that
may not exist prior (use error handling). And I like to have a handle to the
workbook object not deal with ActiveWorkbook, so this code gives you one.

Note, I may not have solved your problem at all but I suspect it is because
you are dealing with Selection when you do not need to be.

Dim xlObj As Excel.Application
Dim xlWB as Excel.Workbook
Dim intRow As Integer, cel As Excel.Range strFullName =
Me!txtFileFolderPAMaint & "\" & strSCBranchFileName & _
        " Post Address Services " & Format(Date, "yyyy mm") & ".xlsx"

On Error Resume Next
Kill strFullName 'In case already created today On Error Goto 0

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

Set xlObj = CreateObject("Excel.Application") xlObj.Visible = True

On Error Resume Next
Set xlWB =    xlObj.Workbooks.Open strFullName
On error Goto 0
If Not xlWB is Nothing then
    For Each cel In xlWB.ActiveSheet.Range("K2:O2").Cells  'Add checkboxes
to each of these cells
        xlWB.ActiveSheet.CheckBoxes.Add(Left:=cel.Left, Top:=cel.Top,
Width:=cel.Width, Height:=cel.Height).Select
        With Cel
           .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
    xlWB.Close SaveChanges:=TRUE
    xlObj.Quit
    Set xlObj = Nothing
End If


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Tuesday, June 24, 2014 7:18 PM
To: AccessD
Subject: [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