[AccessD] Automation Error 91 for Excel Checkbox

David Emerson newsgrps at dalyn.co.nz
Wed Jun 25 01:54:20 CDT 2014


Correction to the correction :( 
Once I close the error message box the second instance of Excel disappears
from Task manager.

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

Correction - closing the second spreadsheet doesn't close the instance of
Excel.  Two instances now appear in Task manager.

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

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

--
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