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?

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?

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?

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?

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?

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