[AccessD] Automation Error 91 for Excel Checkbox

Bill Benson bensonforums at gmail.com
Wed Jun 25 01:53:57 CDT 2014


With Excel 2013 ms moved to a SDI (single document interface) and you get
one instance per workbook.

Can you paste the full code in a message to me off list please?

I did not see a

Sub

End Sub

Pair, so I would like to see the complete procedure.

Tia.
On Jun 25, 2014 2:49 AM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:

> setting xlWB to Nothing also, before Quit made no difference.
>
> When I run the code for the first time the spreadsheet and Excel closes
> (but
> Excel still appears in the task manager).
> When I run it the second time the spreadsheet stays open in Excel (because
> it didn't reach the close line) and there are two instances of Excel in
> task
> manager.  Closing it only closes one of them.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> Sent: Wednesday, 25 June 2014 6:08 p.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Automation Error 91 for Excel Checkbox
>
> Hmm
>
> Try setting xlWB to Nothing also, before Quit.
>
> Since you made it visible can you see it in the task pane even after proc
> ends?
>
>
> On Jun 25, 2014 1:54 AM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:
> >
> > 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
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list