Edward Zuris
edzedz at comcast.net
Fri Nov 13 17:40:02 CST 2009
Success! This worked. . . . And MSFT we would call this a feature. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Friday, November 13, 2009 4:23 PM To: Access Developers discussion and problem solving Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com