Code to close and reopen file
 

Looking to have a bit of code that will close the file it works on without
saving it and then reopen the same file.

I have a file (template.xls) which has a macro which runs on opening. Users
can then manipulate data and save the results to a given file. Without having
to blank all of the areas used for the data manipulation is there a way of
closing the template.xls file and then reopening it?

Code to close and reopen file
 

FName = "C:\temp\book1.xls""

''open workbook the 1st time
set oldbk = workbooks.open(filename:=FName)

'close file without making any changes
oldbk.close savechanges:=false

'open file a second time
set oldbk = workbooks.open(filename:=FName)


"Mr Bunj" wrote:

Looking to have a bit of code that will close the file it works on without
saving it and then reopen the same file.

I have a file (template.xls) which has a macro which runs on opening. Users
can then manipulate data and save the results to a given file. Without having
to blank all of the areas used for the data manipulation is there a way of
closing the template.xls file and then reopening it?

Code to close and reopen file
 

Does the macro stop running when the file is closed?

If so how would it know to open itself again?


"Joel" wrote:


FName = "C:\temp\book1.xls""

''open workbook the 1st time
set oldbk = workbooks.open(filename:=FName)

'close file without making any changes
oldbk.close savechanges:=false

'open file a second time
set oldbk = workbooks.open(filename:=FName)


"Mr Bunj" wrote:

Looking to have a bit of code that will close the file it works on without
saving it and then reopen the same file.

I have a file (template.xls) which has a macro which runs on opening. Users
can then manipulate data and save the results to a given file. Without having
to blank all of the areas used for the data manipulation is there a way of
closing the template.xls file and then reopening it?

Code to close and reopen file
 

A macro only stop running when Thisworkbook is closed. Not when other
workbooks are closed. Thisworkbook referes to the workbook where the macro
is located. A template is a second workbook that you open from Thisworkbook.


"Mr Bunj" wrote:

Does the macro stop running when the file is closed?

If so how would it know to open itself again?


"Joel" wrote:


FName = "C:\temp\book1.xls""

''open workbook the 1st time
set oldbk = workbooks.open(filename:=FName)

'close file without making any changes
oldbk.close savechanges:=false

'open file a second time
set oldbk = workbooks.open(filename:=FName)


"Mr Bunj" wrote:

Looking to have a bit of code that will close the file it works on without
saving it and then reopen the same file.

I have a file (template.xls) which has a macro which runs on opening. Users
can then manipulate data and save the results to a given file. Without having
to blank all of the areas used for the data manipulation is there a way of
closing the template.xls file and then reopening it?

Code to close and reopen file
 

Why don't you just save your file as template file (*.XLT) without any data
filled in at all. That would negate the need for an open/close/reopen macro.
--
Kevin Backmann


"Mr Bunj" wrote:

Looking to have a bit of code that will close the file it works on without
saving it and then reopen the same file.

I have a file (template.xls) which has a macro which runs on opening. Users
can then manipulate data and save the results to a given file. Without having
to blank all of the areas used for the data manipulation is there a way of
closing the template.xls file and then reopening it?

Code to close and reopen file
 

Have solved this one by writing a function which mimics the auto_open
function and then using the following as an option on their menu

Application.OnTime Now + TimeValue("00:00:02"), "on_opening"
ThisWorkbook.Close SaveChanges:=False