Click to See Complete Forum and Search --> : MS Excel VB


February 22nd, 2000, 12:42 PM
I have two subs with one opening and operating on a workbook and the other saving and closing the workbook. The operation was ok and the workbook was saved properly. Unfortunately, my application starts an EXCEL.EXE process in sub 1 and this EXCEL.EXE process would not go away after Sub2 was executed unless I quit my application. I have included the frame of my two subs in this message. How can I remove this disconnected EXCEL.EXE process within VB?

Private Sub OpenWorkBookAndOperate()
Dim Wkb As Workbook
Workbooks.OpenText FileName:=MyASCIIFile, ...

Set Wkb = ActiveWorkbook
With Wkb.Application
'import ASCII file into Excell and parse it into worksheets.
'operation was done with EXCELL window minimized.
End With
Set Wkb = Nothing
End Sub 'Sub Start

Private Sub DoneAndQuit()
Dim Wkb As Workbook
Set Wkb = ActiveWorkbook
With Wkb
.SaveAs FileName:=MyXLFile
.Close True ' tried Wkb.Application.Quit - did not work
End With
Set Wkb = Nothing
End Sub 'DoneAndQuit

Thanks in advance.

Kyle Burns
February 22nd, 2000, 01:43 PM
You're releasing the variable Wkb in the first procedure without closing it. Try removing the Set Wkb = Nothing line in your first procedure or, better yet, passing the Wkb object to your second procedure.

private Sub OpenWorkBookAndOperate()
Dim Wkb as Workbook
Workbooks.OpenText FileName:=MyASCIIFile, ...

set Wkb = ActiveWorkbook
With Wkb.Application
'import ASCII file into Excell and parse it into worksheets.
'operation was done with EXCELL window minimized.
End With
DoneAndQuit Wkb
End Sub 'Sub Start

private Sub DoneAndQuit(Wkb as Workbook)
With Wkb
.SaveAs FileName:=MyXLFile
.Close true ' tried Wkb.Application.Quit - did not work
End With
set Wkb = nothing
End Sub 'DoneAndQuit