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.

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?

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.