VBA not closing excel (visible in the task manager)

Hi All,

the following code does some simple stuff in outlook. When i run the code once, everything goes as planned.
Upon its second run, it comes up with various errors. I've looked what the difference is between the first and the next runs and it seems that excel isn't closed (its still visible in the task manager). as soon as i close it manually (task manager), i can run the code once again - but only once since excel will still end up in the task manager.

Thanks for any guidance!

Sub test3()

Dim objInsp As Inspector
Dim objOl As Outlook.Application
Dim objSel As Outlook.Folder
Dim objItem As Object
Dim intMaxItems As Integer
Dim x As Inspector
Dim sText As String
Dim obMail As Outlook.MailItem
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim wk As Excel.Worksheet

Set objOl = Application
Set objSel = objOl.ActiveExplorer.CurrentFolder

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Users\xxx\Desktop\Schichtwunsche.xlsx")

For Each objItem In objSel.Items
    If objItem.Class = olMail And objItem.Subject = "Schichtwunsch" Then
        Set x = objItem.GetInspector
                'Debug.Print x.ModifiedFormPages(1).item("lblName").Caption
                'Debug.Print x.ModifiedFormPages(1).item("lblTMName").Caption
                'Debug.Print x.ModifiedFormPages(1).item("OlkDateControl1").Value
                wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = objItem.SenderEmailAddress
                wb.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = objItem.SentOn
                wb.Sheets(1).Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = x.ModifiedFormPages(1).item("OlkDateControl1").Value
                wb.Sheets(1).Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = x.ModifiedFormPages(1).item("txtTMName").Value
        objItem.UnRead = False
    End If
Next objItem


wb.Close savechanges:=True
Set wb = Nothing
xlApp.Workbooks.Close
xlApp.UserControl = False
xlApp.Quit
Set xlApp = Nothing
Set objOl = Nothing
Set objSel = Nothing
Set objItem = Nothing

End Sub

Hi Bishonen,

I noticed two things.

1. there is no error handling which may prevent the xlApp.Quit statement from being executed
2. I am not familiar with xlApp.UserControl (frequently use the Excel app instance in memory trick - never used this method)

I would suggest to add error trapping

Sub test3()
On Error Goto ErrorTrapping

...your code

RoutineExit:
  On Error Resume Next
  wb.Close savechanges:=True
  Set wb = Nothing
  xlApp.Workbooks.Close
  xlApp.UserControl = False
  xlApp.Quit
  Set xlApp = Nothing
  Set objOl = Nothing
  Set objSel = Nothing
  Set objItem = Nothing
  Exit Sub

ErrorTrapping:
  Resume RoutineExit

End Sub

Thanks a lot for your reply. Unfortunately that still does not get rid of the excel instance ;/ I'm fighting with this problem since ~1.5h googling my way through dozens of forums and I still didn't find a solution that would work...

Originally Posted by Bishonen

Thanks a lot for your reply. Unfortunately that still does not get rid of the excel instance ;/ I'm fighting with this problem since ~1.5h googling my way through dozens of forums and I still didn't find a solution that would work...

Have you tried removing statement xlApp.UserControl=False?

Else make the xlApp visible and step through your code using debug to see where it goes wrong.