Close & Re-Open a Workbook

Bovey44

New Member
Joined
Jul 4, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Is there a way to close an active workbook and then to immediately re-open the same workbook via VBA? I tried having two lines of code in a macro to do this, but the macro stops executing after the close command and never gets to the open.

Here's what I have:

ActiveWorkbook.Close savechanges:=False
Workbooks.Open ("C:\Users\perfi\OneDrive\Documents\Bob\VBA PRACTICE\Consolidated Data - Recovery 7.24 - XX.xlsx")


Is there a way to get around this?

Thank you.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I am curious to see how others have solved this problem.
Basically, as soon as you .Close the current Workbook it is gone, so the .Open never fires.
My solution might be a bit clunky but I have solved this problem before; maybe not as slick as other solutions.
Here is my approach:
1. Write a routine to open the file you want to open and close the ActiveWorkBook. You can leave this code in your current WorkBook.
2. Save the Workbook you want to open again..
3. Save As this Workbook.
4. Call the routine to open the Workbook you want and close this one.

It has been a while but as I recall it is faster that you might first imagine.
 
Upvote 0
You are welcome, thanks for the feedback. Covid has me stuck in my little computer room with zero "real" work, so I am glad I can help out here now and then.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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
Back
Top