close workbook and reopen it

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
Hi all

Is there a way of making something that closes the workbook down and re-opens it again without saving the previous work.
I have some code that runs, saves various things and creates reports etc.
But when it has finished and i want to paste new info into it, i have to close it and then re-open it, would be great to make it happen by itself ready for the next lot of data.
Thanks

dave
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
ok all

answered my own question.
this may help someone else?

Code:
Sub CloseMe() 
    Application.OnTime Now + TimeValue("00:00:10"), "OpenMe" 
    ThisWorkbook.Close False 
End Sub 

Sub OpenMe() 
    MsgBox "I'm Back!" 
End Sub

Dave
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hi Dave
To answer your question directly....No.
However, you would only be able to close and re-open a workbook from another workbook or app that remains open.
I guess the question is why do you need to close and re-open to paste new data into it ?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Sorry Dave, unless I'm not understanding you correctly....the code you posted won't close the workbook.....and then re-open it !
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

It actually will, Michael. That's why you need to deliberately unschedule any scheduled macros before you close a workbook.
 
Last edited:

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
It actually will, Michael. That's why you need to deliberately unschedule any scheduled macros before you close a workbook.

Well ain't that something?! I would have expected the "I can't find xxx macro" msg.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Bear in mind that the workbook will only reopen if the application is still running. I have come accross a similar scenario where I needed to reopen a workbook via code and I used a VBScript created upon closing on the fly to re-launch the workbook and the application if required.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Thanks GTO....I thought exactly that !!!
****, my tag line still rings true .....LOL !
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
Hi guys

all I can say is it works just fine, i am using it now.
all my macros finish running, creates my reports, closes and then re-opens?
Ready for the new data

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

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