[AccessD] Problem with Access VBA makes Excel Orphan

.HPageBreaks.Add Before:=Cells(lRow + 1, 6)

There's your unqualified reference. Change it to:
.HPageBreaks.Add Before:=ExcelwBook.sheets(sSheet).Cells(lRow + 1, 6).


-- 
Stuart


On 13 Nov 2009 at 15:38, Edward Zuris wrote:

> 
>  Thanks Heenan Lambert,
> 
>  I'll see if I can come up with something
>  a smaller that demonstrates the problem.
> 
>  I was hopeing there was a command that forces 
>  Excel to finish-up whatever and exit cleanly
>  regardless.
> 
>  Killing the Excel orphan isn't an option because
>  the Access 2000 VBA still thinks it is out there
>  causing other downstream Excel jobs to fail.
> 
>  The problem started when I started forcing
>  Page Control Breaks whenever the Department
>  column had a change.
> 
>  = = = = = = = = = = = = = = = = = = = = = = =
> 
>     If sNew <> sOld Then
>        sTmp5 = ""
>        sTmp5 = CStr(lRow + 1) & ":" & CStr(lRow + 1)
>        ExcelwBook.sheets(sSheet).Rows(sTmp5).Insert Shift:=-4121
>        For lColumn = 1 To 6
>            ExcelwBook.sheets(sSheet).Cells(lRow + 1,
> lColumn).Interior.ColorIndex = 10
>        Next lColumn
> 
>        sOld = ""
>        sOld = sNew
> 
>         If lRow > 1 Then
>            '                        * First Method *
>            'On Error Resume Next
>            'ExcelwBook.sheets(sSheet).Cells(lRow + 1, 6).Select
>            ' With ExcelwBook.Application.ActiveWindow
>            '    .ScrollColumn = 2
>            '    .SelectedSheets.HPageBreaks.Add Before:=ActiveCell
>            'End With
>            'On Error GoTo 0
> 
>             ' *****************************************************
>             ' Force Page Control Breaks
>             '
>             With ExcelwBook.sheets(sSheet)
>                 .HPageBreaks.Add Before:=Cells(lRow + 1, 6)
>             End With
> 
>         End If
> 
>     End If
> 
>  = = = = = = = = = = = = = = = = = = = = = = =
>  = = = = = = = = = = = = = = = = = = = = = = =
> 
> 
> -----Original Message-----
> From: Heenan, Lambert [mailto:Lambert.Heenan at chartisinsurance.com] 
> Sent: Friday, November 13, 2009 2:35 PM
> To: 'Access Developers discussion and problem solving'
> Cc: 'Edward Zuris'
> Subject: RE: [AccessD] Problem with Access VBA makes Excel Orphan
> 
> 
> Edward,
> 
> I just ran your example code in this form...
> 
> 
> Sub Excel_test(zPath As String, zName As String)
> Dim ExcelApp            As Excel.Application
>     Dim ExcelwBook          As Excel.Workbook
> 
>     Set ExcelApp = New Excel.Application
>     Set ExcelwBook = ExcelApp.Workbooks.Add
>     ExcelApp.DisplayAlerts = False
>     ExcelwBook.SaveAs zPath & zName
>     ExcelApp.DisplayAlerts = True
>  
>     ExcelwBook.Save
>     ExcelwBook.Close False
>     ExcelApp.Quit
>  
>     Set ExcelwBook = Nothing
>     Set ExcelApp = Nothing
>  
> 
> End Sub
> 
> ... And it does NOT leave an Excel orphan around. I can only conclude
> from that that your problem lies with the code between  the lines.
> Somewhere you are probably setting a reference to some Excel object that
> is not subsequently released.
> 
> Lambert
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris
> Sent: Friday, November 13, 2009 2:58 PM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Problem with Access VBA makes Excel Orphan
> 
> 
>  Using the below code from an Access 2000 VBA  application, once running
> these commands there  is still an Excel job running in task manager.
>  
>  The Excel orphan makes it impossible to run the  VBA application
> recessively.
>  
>  One clue is Excel will finally shut down if  the Access 2000 VBA
> application / database  is shutdown.
>  
>  Any way to tell Excel to just shut down ?
>  
>  Is there a work-a-round for this problem ?
>  
>  Thanks.
>  
>  Sincerely,
>  Edward Zuris.
> 
>  -
>  
>  =======================================================
>  ====== .Quit code not closing Excel Application ======
>  
>     Dim ExcelApp            As Excel.Application
>     Dim ExcelwBook          As Excel.Workbook
>     . . .
>     Set ExcelApp = New Excel.Application
>     Set ExcelwBook = ExcelApp.Workbooks.Add
>     . . .
>     ExcelApp.DisplayAlerts = False
>     ExcelwBook.SaveAs zPath & zName
>     ExcelApp.DisplayAlerts = True
>  
>     ExcelwBook.Save
>     ExcelwBook.Close False
>     ExcelApp.Quit
>  
>     Set ExcelwBook = Nothing
>     Set ExcelApp = Nothing
>  
>  =======================================================
>  =======================================================
>  -
>  
>  Yet a simpler VBA applications doesn't have  this issue using the
> following.
>  
>  This also fails using the complex VBA program.
>  
>  Any ideas ?
>  
>  =======================================================
>  = .Quit code Working closing simple Excel Application =
>  
>     ExcelwBook.Application.DisplayAlerts = False
>     ExcelwBook.SaveAs zPath & zName
>     ExcelwBook.Application.DisplayAlerts = True
>  
>     ExcelwBook.Application.Quit
>  
>     ExcelApp.Quit
>     
>     Set ExcelwBook = Nothing
>     Set ExcelApp = Nothing
>  
>  
>  =======================================================
>  =======================================================
>  
>  
> --
> 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