Solved: Excel reamins open after template finishes running

[Solrac3030]

I have this template that when run it connects to a Excel spreadsheet to retrieve some data that is from a AS400 system. The template runs fine and but when the AS400 ststem tries to delete the Excel file to create a new one an error message is received saying the the Excel file is being used by Excel. I have attached all the code in the Template that connects to the file and gets the data needed and then closes Excel. Maybe someone can help me in figuring out why Excel keeps the file in memory not allowing the file to be deleted.


Sub AutoNew()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim strUser As String

'specify the workbook to work on
strUser = Environ("UserName")
WorkbookToWorkOn = "C:\Documents and Settings\" & strUser & "\My Documents\OICWRCP_Correspondence.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = Excel.Application
End If

On Error GoTo Err_Handler

mRunLetterHeadForm
frmDate.Show
frmConfidential.Show

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

ActiveDocument.FormFields("Title").Result = oWB.Sheets("OICWRCP_Correspondence").Range("K2")
ActiveDocument.FormFields("CWFirstName").Result = oWB.Sheets("OICWRCP_Correspondence").Range("L2")
ActiveDocument.FormFields("CWLastName").Result = oWB.Sheets("OICWRCP_Correspondence").Range("M2")
ActiveDocument.FormFields("Address").Result = oWB.Sheets("OICWRCP_Correspondence").Range("N2")

If oWB.Sheets("OICWRCP_Correspondence").Range("O2") = "" Then
ActiveDocument.FormFields("Address1").Delete
Else
ActiveDocument.FormFields("Address1").Result = vbCr & oWB.Sheets("OICWRCP_Correspondence").Range("O2")
End If

ActiveDocument.FormFields("City").Result = oWB.Sheets("OICWRCP_Correspondence").Range("P2")
ActiveDocument.FormFields("State").Result = oWB.Sheets("OICWRCP_Correspondence").Range("Q2")
ActiveDocument.FormFields("ZipCode").Result = oWB.Sheets("OICWRCP_Correspondence").Range("R2")
ActiveDocument.FormFields("CaseNo").Result = oWB.Sheets("OICWRCP_Correspondence").Range("S2")
ActiveDocument.Bookmarks("Case").Range = oWB.Sheets("OICWRCP_Correspondence").Range("S2")

frmContact.Show
frmSources.Show
frmExplanation.Show
frmSummary.Show
frmFees.Show
ActiveDocument.Fields.Unlink

If ExcelWasNotRunning = True Then
oXL.Quit
End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If
End Sub

[Solrac3030]

Found the problem. Added oWB.Close True to the code after the last range is from Excel is inserted into the template. All is working fine now.