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 Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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,118,140
Messages
5,570,404
Members
412,322
Latest member
mohi021
Top