[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