close workbook and reopen it

[SQUIDD]

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

 

[SQUIDD]

ok all

answered my own question.
this may help someone else?

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]

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]

Sorry Dave, unless I'm not understanding you correctly....the code you posted won't close the workbook.....and then re-open it !

 

[shg]

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

 

[GTO]

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]

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]

Thanks GTO....I thought exactly that !!!
****, my tag line still rings true .....LOL !

 

[SQUIDD]

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

 

[ZVI]

Hi Dave,
You can Save a workbook or worksheet as a template
Take a look in the above link on chapter "Use a template to create a new workbook"
Regards