I have a workbook that I use as a template for users. I open the worksheet
in read-only mode (from an Access form), and allow the users to enter their
data. When they are finished, they click a command button to save the data
to an Access database. They can also save the worksheet to a new file name,
but can't overwrite the template file.

My problem is, in certain circumstances I would like to "clear" the
workbook, resetting all cells to their original contents. This would take
place in VBA code, when the user clicks a command button. Is there an easy
way to do this? I have looked at the Application and Workbook objects, but I
can't see any way to tell the workbook to "reset" itself. I thought about
just iterating through the cells in the workbook, doing a ClearContents on
each, but it is possible that the user has added some rows to the workbook
and I want to be sure to remove those rows as well.

Is there a good way to do this? Thanks for any information,

Carl Rapson

Carl,

Could you just close it and reopen it? It seems like since it's read-only,
it will be in it's original state.

Doug

"Carl Rapson" wrote in message
...
I have a workbook that I use as a template for users. I open the worksheet
in read-only mode (from an Access form), and allow the users to enter
their
data. When they are finished, they click a command button to save the data
to an Access database. They can also save the worksheet to a new file
name,
but can't overwrite the template file.

My problem is, in certain circumstances I would like to "clear" the
workbook, resetting all cells to their original contents. This would take
place in VBA code, when the user clicks a command button. Is there an easy
way to do this? I have looked at the Application and Workbook objects, but
I
can't see any way to tell the workbook to "reset" itself. I thought about
just iterating through the cells in the workbook, doing a ClearContents on
each, but it is possible that the user has added some rows to the workbook
and I want to be sure to remove those rows as well.

Is there a good way to do this? Thanks for any information,

Carl Rapson

Doug,

I could, but I think there's a slight problem in that I am controlling all
of this from an Access application. I open Excel using automation and
maintain a pointer to the Workbook object so that I can call some Excel VBA
functions from Access when I open the workbook (I do some configuration of
the workbook based on some options on an Access form). I'm afraid that if I
close and re-open the workbook, that pointer will be messed up. Am I wrong
about that?

Carl

"Doug Glancy" wrote in message
...
Carl,

Could you just close it and reopen it? It seems like since it's
read-only,
it will be in it's original state.

Doug

"Carl Rapson" wrote in message
...
I have a workbook that I use as a template for users. I open the
worksheet
in read-only mode (from an Access form), and allow the users to enter
their
data. When they are finished, they click a command button to save the
data
to an Access database. They can also save the worksheet to a new file
name,
but can't overwrite the template file.

My problem is, in certain circumstances I would like to "clear" the
workbook, resetting all cells to their original contents. This would
take
place in VBA code, when the user clicks a command button. Is there an
easy
way to do this? I have looked at the Application and Workbook objects,
but
I
can't see any way to tell the workbook to "reset" itself. I thought
about
just iterating through the cells in the workbook, doing a ClearContents
on
each, but it is possible that the user has added some rows to the
workbook
and I want to be sure to remove those rows as well.

Is there a good way to do this? Thanks for any information,

Carl Rapson

Carl,

It probably will be, but why not just reset the pointer when you re-open? It
will be default be the active workbook at that point.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Carl Rapson" wrote in message
...
Doug,

I could, but I think there's a slight problem in that I am controlling all
of this from an Access application. I open Excel using automation and
maintain a pointer to the Workbook object so that I can call some Excel
VBA
functions from Access when I open the workbook (I do some configuration of
the workbook based on some options on an Access form). I'm afraid that if
I
close and re-open the workbook, that pointer will be messed up. Am I wrong
about that?

Carl

"Doug Glancy" wrote in message
...
Carl,

Could you just close it and reopen it? It seems like since it's
read-only,
it will be in it's original state.

Doug

"Carl Rapson" wrote in message
...
I have a workbook that I use as a template for users. I open the
worksheet
in read-only mode (from an Access form), and allow the users to enter
their
data. When they are finished, they click a command button to save the
data
to an Access database. They can also save the worksheet to a new file
name,
but can't overwrite the template file.

My problem is, in certain circumstances I would like to "clear" the
workbook, resetting all cells to their original contents. This would
take
place in VBA code, when the user clicks a command button. Is there an
easy
way to do this? I have looked at the Application and Workbook objects,
but
I
can't see any way to tell the workbook to "reset" itself. I thought
about
just iterating through the cells in the workbook, doing a
ClearContents
on
each, but it is possible that the user has added some rows to the
workbook
and I want to be sure to remove those rows as well.

Is there a good way to do this? Thanks for any information,

Carl Rapson

I'll look into that. However, I don't think I was descriptive enough of my
problem in my original post. Here's what I'm doing:

I have an Access application that open an Excel spreadsheet. I save the
Excel application pointer and the Workbook pointer in Access, for two
reasons: one, to call some workbook methods after opening the workbook (to
do some configuring on the workbook); and two, to close Excel cleanly when
the user is finished (by closing the workbook and setting the Excel
application pointer to Nothing; I found that if I didn't do this, then if
the user exits Excel from within Excel, the Excel process itself keeps
running in Task Manager).

Within my workbook, the user has several options. One is to load some data
from an Access database, and another is to "auto-fill" the workbook cells
with certain values. In both cases, if the user has already made some
changes to the workbook, I want to "reset" the workbook to clear everything
out and start fresh. Bus as I mentioned before, one of the things a user can
do results in adding new rows to some of the worksheets. As a result, I need
to remove those rows as well as clearing all cell contents. I am trying to
think of a simple way of doing that.

The idea of closing and reloading the workbook might work, but I envision
two problems with it. First, the workbook pointer in my original Access
application will probably be messed up; if it is, I can't close the workbook
from Access. Second, I will be closing and reloading the workbook from
within a subroutine within the workbook itself. How will that work? It seems
like only the Close method would be executed, and then my workbook
(including the module containing the subroutine) is gone. Plus, I will
somehow need to return to the same point in VBA that I started from, to
continue with my work.

I hope this is making sense. I appreciate all of the suggestions so far, and
I will certainly appreciate any more anyone might have.

Thanks again,

Carl Rapson


"Bob Phillips" wrote in message
...
Carl,

It probably will be, but why not just reset the pointer when you re-open?
It
will be default be the active workbook at that point.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Carl Rapson" wrote in message
...
Doug,

I could, but I think there's a slight problem in that I am controlling
all
of this from an Access application. I open Excel using automation and
maintain a pointer to the Workbook object so that I can call some Excel
VBA
functions from Access when I open the workbook (I do some configuration
of
the workbook based on some options on an Access form). I'm afraid that
if
I
close and re-open the workbook, that pointer will be messed up. Am I
wrong
about that?

Carl

"Doug Glancy" wrote in message
...
Carl,

Could you just close it and reopen it? It seems like since it's
read-only,
it will be in it's original state.

Doug

"Carl Rapson" wrote in message
...
I have a workbook that I use as a template for users. I open the
worksheet
in read-only mode (from an Access form), and allow the users to
enter
their
data. When they are finished, they click a command button to save
the
data
to an Access database. They can also save the worksheet to a new
file
name,
but can't overwrite the template file.

My problem is, in certain circumstances I would like to "clear" the
workbook, resetting all cells to their original contents. This would
take
place in VBA code, when the user clicks a command button. Is there
an
easy
way to do this? I have looked at the Application and Workbook
objects,
but
I
can't see any way to tell the workbook to "reset" itself. I thought
about
just iterating through the cells in the workbook, doing a
ClearContents
on
each, but it is possible that the user has added some rows to the
workbook
and I want to be sure to remove those rows as well.

Is there a good way to do this? Thanks for any information,

Carl Rapson

try this:
Sub Macro1()
Cells.Select
Selection.ClearContents
Range("A1").Select
End Sub
-----Original Message-----
I have a workbook that I use as a template for users. I
open the worksheet
in read-only mode (from an Access form), and allow the
users to enter their
data. When they are finished, they click a command
button to save the data
to an Access database. They can also save the worksheet
to a new file name,
but can't overwrite the template file.

My problem is, in certain circumstances I would like
to "clear" the
workbook, resetting all cells to their original
contents. This would take
place in VBA code, when the user clicks a command
button. Is there an easy
way to do this? I have looked at the Application and
Workbook objects, but I
can't see any way to tell the workbook to "reset"
itself. I thought about
just iterating through the cells in the workbook, doing
a ClearContents on
each, but it is possible that the user has added some
rows to the workbook
and I want to be sure to remove those rows as well.

Is there a good way to do this? Thanks for any
information,

Carl Rapson


.

David,

Thanks for the suggestion. That solves the problem of clearing the cells,
but I have another problem -- the user may have added some rows to a
worksheet, and I need to remove those added rows also. I'm having trouble
figuring out how to do that as well.

Thanks again,

Carl

"David" wrote in message
...
try this:
Sub Macro1()
Cells.Select
Selection.ClearContents
Range("A1").Select
End Sub
-----Original Message-----
I have a workbook that I use as a template for users. I
open the worksheet
in read-only mode (from an Access form), and allow the
users to enter their
data. When they are finished, they click a command
button to save the data
to an Access database. They can also save the worksheet
to a new file name,
but can't overwrite the template file.

My problem is, in certain circumstances I would like
to "clear" the
workbook, resetting all cells to their original
contents. This would take
place in VBA code, when the user clicks a command
button. Is there an easy
way to do this? I have looked at the Application and
Workbook objects, but I
can't see any way to tell the workbook to "reset"
itself. I thought about
just iterating through the cells in the workbook, doing
a ClearContents on
each, but it is possible that the user has added some
rows to the workbook
and I want to be sure to remove those rows as well.

Is there a good way to do this? Thanks for any
information,

Carl Rapson


.