I have an opened workbook which was saved by a user.
Now I need to treat this workbook as a new, so after clicking File/Save
(not Save as) Excel will open dialog window to provide filename.

Is there any way to do this from VBA?

Look in Excel help at the beforeSave event.

http://www.cpearson.com/excel/events.htm

you would cancel the save by setting

Cancel = True
then disable events
Application.EnableEvents = False
then put up the saveas dialog

fname = Application.GetSaveAsFileName

then save the file if the name is acceptable

thisworkbook.SaveAs fname

reenable events
Application.EnableEvents = True

--
Regards,
Tom Ogilvy



"witek" wrote:


I have an opened workbook which was saved by a user.
Now I need to treat this workbook as a new, so after clicking File/Save
(not Save as) Excel will open dialog window to provide filename.

Is there any way to do this from VBA?

It is more complicated.

Code is somewhere else that workbook which is open, so I can't use any
events which apply to that workbook.


the idea is like below


wbk = workbooks.Add
wbk.saveas "temp"


now wbk remians open with "temp" as a name, but I need it as "noname".
There is no VBA code in "temp" file.








Tom Ogilvy wrote:
Look in Excel help at the beforeSave event.

http://www.cpearson.com/excel/events.htm

you would cancel the save by setting

Cancel = True
then disable events
Application.EnableEvents = False
then put up the saveas dialog

fname = Application.GetSaveAsFileName

then save the file if the name is acceptable

thisworkbook.SaveAs fname

reenable events
Application.EnableEvents = True

I have an opened workbook which was saved by a user.
was your original problem statement. You have now contradicted that.

If you determine what your actual situation is, then certainly, post back.

If a workbook has been saved, then it will have a name. Possibly you are
looking for a template.

--
Regards,
Tom Ogilvy


"witek" wrote:

It is more complicated.

Code is somewhere else that workbook which is open, so I can't use any
events which apply to that workbook.


the idea is like below


wbk = workbooks.Add
wbk.saveas "temp"


now wbk remians open with "temp" as a name, but I need it as "noname".
There is no VBA code in "temp" file.








Tom Ogilvy wrote:
Look in Excel help at the beforeSave event.

http://www.cpearson.com/excel/events.htm

you would cancel the save by setting

Cancel = True
then disable events
Application.EnableEvents = False
then put up the saveas dialog

fname = Application.GetSaveAsFileName

then save the file if the name is acceptable

thisworkbook.SaveAs fname

reenable events
Application.EnableEvents = True

opened workbook, doesn't mean that I can write any code to it.

1.
Workbook is created from VBA code from scratch, but VBA code is in add-in.
For some reasons this newly created workbook must be saved.
After that if user saves it, it is saved under the temporary name chosen
by me.
I don't want to and I can't force Save As option.
I would like to have it as a noname even if I saved it before.

2. if user saved using his own name, he needs option to leave it open as
a noname, so next time choosing File/Save Excel will ask for a name.

What I need is option like ActiveWorkbook.FullName = ""

Is it clear now?




Tom Ogilvy wrote:
I have an opened workbook which was saved by a user.

was your original problem statement. You have now contradicted that.

If you determine what your actual situation is, then certainly, post back.

If a workbook has been saved, then it will have a name. Possibly you are
looking for a template.

witek,
..SaveCopyAs ?

NickHK

"witek" wrote in message
...
opened workbook, doesn't mean that I can write any code to it.

1.
Workbook is created from VBA code from scratch, but VBA code is in
add-in.
For some reasons this newly created workbook must be saved.
After that if user saves it, it is saved under the temporary name chosen
by me.
I don't want to and I can't force Save As option.
I would like to have it as a noname even if I saved it before.

2. if user saved using his own name, he needs option to leave it open as
a noname, so next time choosing File/Save Excel will ask for a name.

What I need is option like ActiveWorkbook.FullName = ""

Is it clear now?




Tom Ogilvy wrote:
I have an opened workbook which was saved by a user.

was your original problem statement. You have now contradicted that.

If you determine what your actual situation is, then certainly, post
back.

If a workbook has been saved, then it will have a name. Possibly you
are
looking for a template.

SaveCopyAs creates a copy and doesn't save current workbook.
Problem is that current workbook must be saved, not simply its copy
created. Why? Because of bug in Excel.




NickHK wrote:
witek,
.SaveCopyAs ?

NickHK

"witek" wrote in message
...

opened workbook, doesn't mean that I can write any code to it.

1.
Workbook is created from VBA code from scratch, but VBA code is in

add-in.

For some reasons this newly created workbook must be saved.
After that if user saves it, it is saved under the temporary name chosen
by me.
I don't want to and I can't force Save As option.
I would like to have it as a noname even if I saved it before.

2. if user saved using his own name, he needs option to leave it open as
a noname, so next time choosing File/Save Excel will ask for a name.

What I need is option like ActiveWorkbook.FullName = ""

Is it clear now?




Tom Ogilvy wrote:

I have an opened workbook which was saved by a user.

was your original problem statement. You have now contradicted that.

If you determine what your actual situation is, then certainly, post

back.

If a workbook has been saved, then it will have a name. Possibly you

are

looking for a template.

SaveCopyAs does not require that the workbook be saved.


"witek" wrote in message
...
SaveCopyAs creates a copy and doesn't save current workbook.
Problem is that current workbook must be saved, not simply its
copy created. Why? Because of bug in Excel.




NickHK wrote:
witek,
.SaveCopyAs ?

NickHK

"witek" wrote in message
...

opened workbook, doesn't mean that I can write any code to it.

1.
Workbook is created from VBA code from scratch, but VBA code
is in

add-in.

For some reasons this newly created workbook must be saved.
After that if user saves it, it is saved under the temporary
name chosen
by me.
I don't want to and I can't force Save As option.
I would like to have it as a noname even if I saved it before.

2. if user saved using his own name, he needs option to leave
it open as
a noname, so next time choosing File/Save Excel will ask for
a name.

What I need is option like ActiveWorkbook.FullName = ""

Is it clear now?




Tom Ogilvy wrote:

I have an opened workbook which was saved by a user.

was your original problem statement. You have now
contradicted that.

If you determine what your actual situation is, then
certainly, post

back.

If a workbook has been saved, then it will have a name.
Possibly you

are

looking for a template.

But it is not that case.
Once again.
There is a workbook which has a name assigned to it. So File/Save will
not ask for name.
I need to remove filename, so next time when user will try to save it by
choosing File/Save Excel should display SaveAs dialog box.

Making copies, forcing Save as doesn't solves the problem.



Chip Pearson wrote:
SaveCopyAs does not require that the workbook be saved.


"witek" wrote in message
...

SaveCopyAs creates a copy and doesn't save current workbook.
Problem is that current workbook must be saved, not simply its
copy created. Why? Because of bug in Excel.




NickHK wrote:

witek,
.SaveCopyAs ?

NickHK

"witek" wrote in message
...


opened workbook, doesn't mean that I can write any code to it.

1.
Workbook is created from VBA code from scratch, but VBA code
is in

add-in.


For some reasons this newly created workbook must be saved.
After that if user saves it, it is saved under the temporary
name chosen
by me.
I don't want to and I can't force Save As option.
I would like to have it as a noname even if I saved it before.

2. if user saved using his own name, he needs option to leave
it open as
a noname, so next time choosing File/Save Excel will ask for
a name.

What I need is option like ActiveWorkbook.FullName = ""

Is it clear now?




Tom Ogilvy wrote:


I have an opened workbook which was saved by a user.

was your original problem statement. You have now
contradicted that.

If you determine what your actual situation is, then
certainly, post

back.


If a workbook has been saved, then it will have a name.
Possibly you

are


looking for a template.

If you save as a template then the user would be asked to rename it or it
would be given a name + 1,2, (next sequence number)

--
Wendell A. Clark, BS
-------------------------------------

CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.


"witek" wrote in message
...

I have an opened workbook which was saved by a user.
Now I need to treat this workbook as a new, so after clicking File/Save
(not Save as) Excel will open dialog window to provide filename.

Is there any way to do this from VBA?

witek,
If it has been saved, you cannot *UnSave* it.
What you are asking makes no sense.

NickHK

"witek" wrote in message
...
But it is not that case.
Once again.
There is a workbook which has a name assigned to it. So File/Save will
not ask for name.
I need to remove filename, so next time when user will try to save it by
choosing File/Save Excel should display SaveAs dialog box.

Making copies, forcing Save as doesn't solves the problem.



Chip Pearson wrote:
SaveCopyAs does not require that the workbook be saved.


"witek" wrote in message
...

SaveCopyAs creates a copy and doesn't save current workbook.
Problem is that current workbook must be saved, not simply its
copy created. Why? Because of bug in Excel.




NickHK wrote:

witek,
.SaveCopyAs ?

NickHK

"witek" wrote in message
...


opened workbook, doesn't mean that I can write any code to it.

1.
Workbook is created from VBA code from scratch, but VBA code
is in

add-in.


For some reasons this newly created workbook must be saved.
After that if user saves it, it is saved under the temporary
name chosen
by me.
I don't want to and I can't force Save As option.
I would like to have it as a noname even if I saved it before.

2. if user saved using his own name, he needs option to leave
it open as
a noname, so next time choosing File/Save Excel will ask for
a name.

What I need is option like ActiveWorkbook.FullName = ""

Is it clear now?




Tom Ogilvy wrote:


I have an opened workbook which was saved by a user.

was your original problem statement. You have now
contradicted that.

If you determine what your actual situation is, then
certainly, post

back.


If a workbook has been saved, then it will have a name.
Possibly you

are


looking for a template.

NickHK wrote:
witek,
If it has been saved, you cannot *UnSave* it.
What you are asking makes no sense.

I thought that maybe I can.
It makes sense, simply there is no function which allows to do that.

Wendell A. Clark wrote:
If you save as a template then the user would be asked to rename it or it
would be given a name + 1,2, (next sequence number)


ActiveWorkbook.SaveAs "aaa", xlTemplate
ActiveWorkbook.Close
Workbooks.Open "aaa"


It makes sense. It is similar to what I wanted to achive. Thanks.

If a workbook has been saved, then it will have a name. Possibly you are
looking for a template.


--
Regards,
Tom Ogilvy

"witek" wrote in message
...
Wendell A. Clark wrote:
If you save as a template then the user would be asked to rename it or
it
would be given a name + 1,2, (next sequence number)


ActiveWorkbook.SaveAs "aaa", xlTemplate
ActiveWorkbook.Close
Workbooks.Open "aaa"


It makes sense. It is similar to what I wanted to achive. Thanks.