Close & Re-Open a Workbook

Bovey44

New Member
Joined
Jul 4, 2020
Messages
11
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019
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.
 

Bovey44

New Member
Joined
Jul 4, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Thank you, Ray - this worked perfectly!
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,680
Members
414,008
Latest member
SNesbyCarr

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