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.
