Outlook VBA does not open Excel Workbook in Run Sub mode

Sreekanth

New Member
Joined
Apr 5, 2013
Messages
9
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?
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,728
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It should be:

Code:
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

New Member
Joined
Apr 5, 2013
Messages
9
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

New Member
Joined
Apr 5, 2013
Messages
9
It should be:

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,728
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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:

VBA Code:
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
 
Solution

Sreekanth

New Member
Joined
Apr 5, 2013
Messages
9
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:

VBA Code:
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:

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,728
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Apr 5, 2013
Messages
9
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"?
VBA Code:
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!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,014
Messages
5,639,555
Members
417,098
Latest member
steverob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top