Application object variables

G

Guest

I have some procedures that open up Excel and do things with it. Some of them
close, but others keep it open in the background. How do I prevent this?

I do set ExAp = nothing
ExAp.quit

even a procedure that does this. IS there a failsafe way of making sure a
variable set to an application closes?
[email protected]
 
D

Douglas J. Steele

Not sure whether it'll solve your problem, but you need to reverse the order
of your statements. Once you set ExAp to Nothing, the object no longer
exists, so you can't use the Quit method on it.

How do you declare ExAp, by the way?

If you're using

Dim ExAp As New Excel.Application

try using

Dim ExAp As Excel.Application

Set ExAp = New Excel.Application

instead.
 
G

Guest

I already did it the way you described originally.

Dim ExAp As excel.Application
Set ExAp = New excel.Application

Then at the bottom of the procedure and in error mode:

ExAp.Quit
Set ExAp = Nothing

But Excel is still running in the background.

The first procedure does open and close Excel okay. It calls a function to
close the object called CloseX and is used just as a browse button. After It
runs Task Manager does show Excel not running at all.:

Private Sub Command0_Click()
'BROWSE BUTTON
On Error GoTo er1
'ChDir ("C:\My Docs2\Documentation\Returned Questionaires")
Dim ExAp As excel.Application, FileToOpen As String
Set ExAp = New excel.Application
ExAp.Visible = True
'With ExAp.
FileToOpen = ExAp.Application.GetOpenFilename("Excel Files (*.xls),
*.*")
Me.Text1.Value = FileToOpen 'Populate textbox to use later
ExAp.Visible = False
ExAp.Quit
Set ExAp = Nothing
er1:
If Len(Err.Description) > 0 Then
MsgBox Err.Description
End If

Call CloseX(ExAp)
End Sub

Sub CloseX(ExAp)
On Error GoTo er2
ExAp.Quit
Set ExAp = Nothing
Exit Sub
er2:
End Sub

But the other procedure that exports data to Excel, while does usually run
fine, always seems to leave ExAp running no matter what I do:

Private Sub Command4_Click()
'EXPORT BUTTON
Dim ExAp As excel.Application, WB As excel.Workbook
Dim FileToOpen As String, RowCount As String, Docname As String
Dim Purpose1 As String, Source1 As String, Output1 As String, FileType1 As
String
Dim Users1 As String, Frequency1 As String, Size1 As String, Age1 As String
Dim Critical1 As String, Support1 As String, Retired1 As String, Version1 As
String
Dim BackedUp1 As String, Alternate1 As String, Macros1 As String, ID1 As
String, Response1 As String

Call CloseX(ExAp)

Dim DB As Database, rs As Recordset, QRY1 As String
Set DB = CurrentDb

On Error GoTo er2
FileToOpen = Me.Text1.Value
Set ExAp = New excel.Application
ExAp.Application.Workbooks.Open filename:=FileToOpen, UpdateLinks:=False
'Docname = Left(ExAp.ActiveWorkbook.Name, Len(ExAp.ActiveWorkbook.Name) -
4)
Docname = ExAp.ActiveWorkbook.Name

Dim i As Integer

RowCount = ExAp.Workbooks(Docname).Sheets(1).Range("A65000").End(xlUp).Row
For i = 3 To RowCount
With ExAp.Workbooks(Docname).Sheets(1)

Purpose1 = Cells(i, 4).Text
Source1 = Cells(i, 5).Text
Output1 = Cells(i, 6).Text
FileType1 = Cells(i, 7).Text
Users1 = Cells(i, 8).Text
Frequency1 = Cells(i, 9).Text
Size1 = Cells(i, 10).Text
Age1 = Cells(i, 11).Text
Critical1 = Cells(i, 12).Text
Support1 = Cells(i, 13).Text
Retired1 = Cells(i, 14).Text
Version1 = Cells(i, 15).Text
BackedUp1 = Cells(i, 16).Text
Alternate1 = Cells(i, 17).Text
Macros1 = Cells(i, 18).Text
ID1 = Cells(i, 19).Text
Response1 = Cells(i, 20).Text

QRY1 = "UPDATE [Document_Details] SET Purpose = " & Chr(34) & Purpose1 &
Chr(34) & _
", Source = " & Chr(34) & Source1 & Chr(34) & ", Output = " & Chr(34) & Output1
& Chr(34) & _
", [File Type] = " & Chr(34) & FileType1 & Chr(34) & ", Number_Of_Users = " &
Chr(34) & Users1 & Chr(34) & _
", Frequency_Of_Use = " & Chr(34) & Frequency1 & Chr(34) & ", Size = " &
Chr(34) & Size1 & Chr(34) & _
", Age = " & Chr(34) & Age1 & Chr(34) & ", Critical = " & Chr(34) & Critical1 &
Chr(34) & _
", [IT_Support Contact] = " & Chr(34) & Support1 & Chr(34) & ", To_Be_Retired =
" & Chr(34) & Retired1 & Chr(34) & _
", version = " & Chr(34) & Version1 & Chr(34) & ", [Backed-Up] = " & Chr(34) &
BackedUp1 & Chr(34) & _
", Alternate_Contact = " & Chr(34) & Alternate1 & Chr(34) & ", Macros = " &
Chr(34) & Macros1 & Chr(34) & ", ResponseDate = #" & Date & "# WHERE ID = " &
ID1 & ";"

DB.Execute (QRY1)

End With
Next i

Call CloseX(ExAp)

MsgBox "Updated " & (i - 3) & " Records, chief."

Exit Sub

er2:

Call CloseX(ExAp)

If Len(Err.Description) > 0 Then
MsgBox Err.Description
End If
Exit Sub
End Sub





Subject: Re: Application object variables
From: "Douglas J. Steele" NOSPAM_djsteele@NOSPAM_canada.com

Not sure whether it'll solve your problem, but you need to reverse the order
of your statements. Once you set ExAp to Nothing, the object no longer
exists, so you can't use the Quit method on it.

How do you declare ExAp, by the way?

If you're using

Dim ExAp As New Excel.Application

try using

Dim ExAp As Excel.Application

Set ExAp = New Excel.Application

instead.
 
G

Guest

I do all of that. Here are three procedures. The browse button oepns and closes
excel okay. The import procedure always leaves it running in the background,
though:

Private Sub Command0_Click()
'BROWSE BUTTON
On Error GoTo er1

Dim ExAp As excel.Application, FileToOpen As String
Set ExAp = New excel.Application
ExAp.Visible = True
FileToOpen = ExAp.Application.GetOpenFilename("Excel Files (*.xls),
*.*")
Me.Text1.Value = FileToOpen
ExAp.Visible = False
ExAp.Quit
Set ExAp = Nothing
er1:
If Len(Err.Description) > 0 Then
MsgBox Err.Description
End If

Call CloseX(ExAp)
End Sub

Sub CloseX(ExAp) 'Close Excel Obj Variable
On Error GoTo er2
ExAp.Quit
Set ExAp = Nothing
Exit Sub
er2:
End Sub

Private Sub Command4_Click()
'Import From Excel
Dim ExAp As excel.Application, WB As excel.Workbook
Dim FileToOpen As String, RowCount As String, Docname As String
Dim Purpose1 As String, Source1 As String, Output1 As String, FileType1 As
String
Dim Users1 As String, Frequency1 As String, Size1 As String, Age1 As String
Dim Critical1 As String, Support1 As String, Retired1 As String, Version1 As
String
Dim BackedUp1 As String, Alternate1 As String, Macros1 As String, ID1 As
String, Response1 As String

Call CloseX(ExAp)

Dim DB As Database, rs As Recordset, QRY1 As String
Set DB = CurrentDb

On Error GoTo er2
FileToOpen = Me.Text1.Value
Set ExAp = New excel.Application
ExAp.Application.Workbooks.Open filename:=FileToOpen, UpdateLinks:=False
'Docname = Left(ExAp.ActiveWorkbook.Name, Len(ExAp.ActiveWorkbook.Name) -
4)
Docname = ExAp.ActiveWorkbook.Name
'End With

Dim i As Integer


RowCount = ExAp.Workbooks(Docname).Sheets(1).Range("A65000").End(xlUp).Row
For i = 3 To RowCount
With ExAp.Workbooks(Docname).Sheets(1)

Purpose1 = Cells(i, 4).Text
Source1 = Cells(i, 5).Text
Output1 = Cells(i, 6).Text
FileType1 = Cells(i, 7).Text
Users1 = Cells(i, 8).Text
Frequency1 = Cells(i, 9).Text
Size1 = Cells(i, 10).Text
Age1 = Cells(i, 11).Text
Critical1 = Cells(i, 12).Text
Support1 = Cells(i, 13).Text
Retired1 = Cells(i, 14).Text
Version1 = Cells(i, 15).Text
BackedUp1 = Cells(i, 16).Text
Alternate1 = Cells(i, 17).Text
Macros1 = Cells(i, 18).Text
ID1 = Cells(i, 19).Text
Response1 = Cells(i, 20).Text

QRY1 = "UPDATE [Document_Details] SET Purpose = " & Chr(34) & Purpose1 &
Chr(34) & _
", Source = " & Chr(34) & Source1 & Chr(34) & ", Output = " & Chr(34) & Output1
& Chr(34) & _
", [File Type] = " & Chr(34) & FileType1 & Chr(34) & ", Number_Of_Users = " &
Chr(34) & Users1 & Chr(34) & _
", Frequency_Of_Use = " & Chr(34) & Frequency1 & Chr(34) & ", Size = " &
Chr(34) & Size1 & Chr(34) & _
", Age = " & Chr(34) & Age1 & Chr(34) & ", Critical = " & Chr(34) & Critical1 &
Chr(34) & _
", [IT_Support Contact] = " & Chr(34) & Support1 & Chr(34) & ", To_Be_Retired =
" & Chr(34) & Retired1 & Chr(34) & _
", version = " & Chr(34) & Version1 & Chr(34) & ", [Backed-Up] = " & Chr(34) &
BackedUp1 & Chr(34) & _
", Alternate_Contact = " & Chr(34) & Alternate1 & Chr(34) & ", Macros = " &
Chr(34) & Macros1 & Chr(34) & ", ResponseDate = #" & Date & "# WHERE ID = " &
ID1 & ";"

DB.Execute (QRY1)

End With
Next i

Call CloseX(ExAp)

MsgBox "Updated " & (i - 3) & " Records, chief."

Exit Sub

er2:
Call CloseX(ExAp)
If Len(Err.Description) > 0 Then
MsgBox Err.Description
End If
Exit Sub
End Sub
[email protected]
 
Top