Excel process not closing?

[jmthompson]

Good morning,
Technically this is an Outlook macro I've created to run each morning when I launch Outlook. I had it working perfectly, but my company moved me to Office 2010 and lost my Outlook macros. I am not stymied by an issue that I know I had and solved previously, but I am not as smart today, as I was then.

This macro identifies today's day, opens an Excel workbook, performs vlookup to find the date and return a value. If the date is found. it generates a message box with that value. Then it closes Excel. This is where my problem is. It closes excel, but it's leaving a process open. When I later try to use an Excel-based add-in, the open process is interfering. I've spent the morning with Professor Google and cannot figure this out. Ideas?

Private Sub Application_Startup()
Dim myDate As Date
Dim myPrompt As String
myDate = Format(Now(), "mm/dd/yyyy")
Dim xlApp As Excel.Application
Dim myWB As Excel.Workbook



Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = False
Set myWB = xlApp.Workbooks.Open("C:\Users\JThomps2\Desktop\ClosingDays.xls")

On Error GoTo Errorhandler
myPrompt = Excel.Application.VLookup(CDbl(myDate), xlApp.Worksheets("Sheet1").Range("A:B"), 2, False)

MsgBox ("Closing Day " & myPrompt)
Errorhandler:

Excel.Application.Quit
Set xlApp = Nothing


End Sub

 

[patel45]

Private Sub Application_Startup()
Dim myDate As Date
Dim myPrompt As String
myDate = Format(Now(), "mm/dd/yyyy")
Dim myWB As Excel.Workbook
Set myWB = xlApp.Workbooks.Open("C:\Users\JThomps2\Desktop\ClosingDays.xls")

On Error GoTo Errorhandler
myPrompt = Application.VLookup(CDbl(myDate), myWB.Worksheets("Sheet1").Range("A:B"), 2, False)

MsgBox ("Closing Day " & myPrompt)
Errorhandler:
myWB.close true
Application.Quit

End Sub

 

[jmthompson]

Thank you for the response. I tried your solution, but it did not work.

 

[Andrew Poulsom]

Shouldn't it be?

xlApp.Quit

 

[jim may]

Try also adding:

Set myWB = Nothing

just before or after your existing line: Set xlApp = Nothing

 

[Norie]

Try this.

Option Explicit

Private Sub Application_Startup()
Dim xlApp As Excel.Application
Dim myWB As Excel.Workbook
Dim myDate As Date
Dim myPrompt As Variant

    myDate = Date

    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = False

    Set myWB = xlApp.Workbooks.Open("C:\Users\JThomps2\Desktop\ClosingDays.xls")

    myPrompt = xlApp.VLookup(CDbl(myDate), myWB.Worksheets("Sheet1").Range("A:B"), 2, False)
   
    If Not IsError(myPrompt) Then
        MsgBox ("Closing Day " & myPrompt)
    End If
    
    myWB.Close False
  
    Set myWB = Nothing

    xlApp.Quit

    Set xlApp = Nothing

End Sub

 

[jmthompson]

Thanks, all!
I tried Norie's code, since it included the other suggestions and it works!