Outlook VBA does not open Excel Workbook in Run Sub mode

[Sreekanth]

I am trying to open excel file from outlook am using office 2016
I have referenced Microsoft Excel 16.0 Object Library and Microsoft Outlook 16.0 Object Library
My code runs fine in debug mode using F8 without any issues however, it does not run in Run Sub.
My code is as below and I am not getting any error message either.
When I view task manager, I can see Microsoft Excel app has been created, and when I kill it and get into debug option, I see that it is stuck at Workbooks.Open method. Can someone please help?

Sub OpenFile()

Dim xlApp As Excel.Application
Dim xlWs As Excel.Worksheet
Dim xlRange As Excel.Range

Set xlApp = CreateObject("Excel.Application")

Workbooks.Open ("C:\Users\MyUserID\Documents\MyFile.xlsx") '<<<Code is getting stuck here

Set xlWs = ActiveWorkbook.Sheets("Emails")
xlWs.Activate
Set xlRange = xlWs.Range("A1")
xlRange.Activate

ActiveWorkbook.Save
xlApp.Quit

Set xlRange = Nothing
Set xlWs = Nothing
Set xlApp = Nothing

End Sub

 

[RoryA]

It should be:

xlApp.Workbooks.Open "C:\Users\MyUserID\Documents\MyFile.xlsx"

and you should use xlApp.Activeworkbook rather than just Activeworkbook in two places in the code.

 

[Sreekanth]

Still the same. Does not run in Run Sub mode.
I noticed something strange as described below.

When I use xlApp.Workbooks.Open "C:\Users\MyUserID\Documents\MyFile.xlsx" and xlApp.Activeworkbook in both places, it runs in F8 Debug mode but doesn't run in Run Sub however, The Microsoft Excel app in Task Manager is getting closed (when run in F8 Debug mode)

When I use Workbooks.Open ("C:\Users\MyUserID\Documents\MyFile.xlsx") and ActiveWorkbook in both places, this time it runs in both F8 Debug mode and in Run Sub mode however, the Microsoft Excel app in the Task Manager is still there after the execution is completed (when run in F8 Debug mode) and any subsequent runs is creating one additional Microsoft Excel app in Task Manager and it gets closed once the execution is completed but leaves the first (original) Microsoft Excel app still listed.

If I kill the original Microsoft Excel App from Task Manager then it runs only in F8 debug mode and doesn't run in Run Sub mode.

 

[Sreekanth]

It should be:

xlApp.Workbooks.Open "C:\Users\MyUserID\Documents\MyFile.xlsx"

and you should use xlApp.Activeworkbook rather than just Activeworkbook in two places in the code.

Still the same. Does not run in Run Sub mode.
I noticed something strange as described below.

When I use xlApp.Workbooks.Open "C:\Users\MyUserID\Documents\MyFile.xlsx" and xlApp.Activeworkbook in both places, it runs in F8 Debug mode but doesn't run in Run Sub however, The Microsoft Excel app in Task Manager is getting closed (when run in F8 Debug mode)

When I use Workbooks.Open ("C:\Users\MyUserID\Documents\MyFile.xlsx") and ActiveWorkbook in both places, this time it runs in both F8 Debug mode and in Run Sub mode however, the Microsoft Excel app in the Task Manager is still there after the execution is completed (when run in F8 Debug mode) and any subsequent runs is creating one additional Microsoft Excel app in Task Manager and it gets closed once the execution is completed but leaves the first (original) Microsoft Excel app still listed.

If I kill the original Microsoft Excel App from Task Manager then it runs only in F8 debug mode and doesn't run in Run Sub mode.

 

[RoryA]

You should not use the code as you had it. The unqualified calls to Excel objects are why the process is lingering in Task Manager. I don't really see the point of the code, but I'd suggest:

Sub OpenFile()

Dim xlApp As Excel.Application
Dim xlWb as Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlRange As Excel.Range

Set xlApp = CreateObject("Excel.Application")

set xlwb = xlApp.Workbooks.Open("C:\Users\MyUserID\Documents\MyFile.xlsx")

Set xlWs = xlwb.Sheets("Emails")
xlWs.Activate
Set xlRange = xlWs.Range("A1")
xlRange.Activate

xlwb.Save
xlwb.close False
xlApp.Quit

Set xlRange = Nothing
Set xlWs = Nothing
Set xlApp = Nothing

End Sub

 

[Sreekanth]

You should not use the code as you had it. The unqualified calls to Excel objects are why the process is lingering in Task Manager. I don't really see the point of the code, but I'd suggest:

Sub OpenFile()

Dim xlApp As Excel.Application
Dim xlWb as Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlRange As Excel.Range

Set xlApp = CreateObject("Excel.Application")

set xlwb = xlApp.Workbooks.Open("C:\Users\MyUserID\Documents\MyFile.xlsx")

Set xlWs = xlwb.Sheets("Emails")
xlWs.Activate
Set xlRange = xlWs.Range("A1")
xlRange.Activate

xlwb.Save
xlwb.close False
xlApp.Quit

Set xlRange = Nothing
Set xlWs = Nothing
Set xlApp = Nothing

End Sub

You should not use the code as you had it. The unqualified calls to Excel objects are why the process is lingering in Task Manager. I don't really see the point of the code, but I'd suggest:

Sub OpenFile()

Dim xlApp As Excel.Application
Dim xlWb as Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlRange As Excel.Range

Set xlApp = CreateObject("Excel.Application")

set xlwb = xlApp.Workbooks.Open("C:\Users\MyUserID\Documents\MyFile.xlsx") ' <<< Code execution stuck here when I terminate Excel app from Task Manager

Set xlWs = xlwb.Sheets("Emails")
xlWs.Activate
Set xlRange = xlWs.Range("A1")
xlRange.Activate

xlwb.Save
xlwb.close False
xlApp.Quit

Set xlRange = Nothing
Set xlWs = Nothing
Set xlApp = Nothing

End Sub

I really appreciate you RoryA for your kind response. Unfortunately I used the exact code you suggested but it still doesn't want to run in Run Mode and getting stuck. When I use debug Step into mode it runs. I am not sure why.
Regarding the purpose of this file, I wanted to get details of some specific emails in my mailbox tracked in Excel file which I omitted code.

 

[RoryA]

Since the code as it is here doesn't actually do anything noticeable, what do you mean exactly when you say it "doesn't want to run in run mode"?

 

[Sreekanth]

Since the code as it is here doesn't actually do anything noticeable, what do you mean exactly when you say it "doesn't want to run in run mode"?

Sub OpenFile()

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlRange As Excel.Range

Set xlApp = CreateObject("Excel.Application")
MsgBox "Excel App created"

Set xlWb = xlApp.Workbooks.Open("C:\Users\MyUserID\Documents\MyFile.xlsx")  ' <<< Code execution stuck here when I terminate Excel app from Task Manager
MsgBox "Excel Workbook opened"

Set xlWs = xlWb.Sheets("Emails")
xlWs.Activate
MsgBox "Excel Worksheet Activated"

Set xlRange = xlWs.Range("A1")
MsgBox "Excel Range Set"

xlRange.Activate
MsgBox "Excel Range Activated"

xlWb.Save
MsgBox "Excel Workbook Saved"

xlWb.Close False
MsgBox "Excel Workbook Closed"

xlApp.Quit
MsgBox "Excel App Quit"

Set xlRange = Nothing
Set xlWs = Nothing
Set xlApp = Nothing

MsgBox "Program Executed Successfully"

End Sub

OK, for the test sake I had put message box in the above code to check the execution stages, The code got executed once with message box without leaving any residue of Excel app in Task manager before beginning execution. Then I found one place deep in my original processing code line and you were spot on, like what you said "The unqualified calls to Excel objects are why the process is lingering in Task Manager." after eliminating that, the code now runs flawlessly without leaving any residue or getting stuck anywhere.

Thank you so much for your help and guidance RoryA, you are awesome!