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
>