[AccessD] Problem with Access VBA makes Excel Orphan

 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