Saving files with names from ranges
 

Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them easy to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving them
names on their own), I am using the following code in a macro button (called
Save) placed on the sheet to generate a file name based on contents of cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once the
file is saved with this name & the user clicks on the Macro button (Save)
next time, it displays the Excel message, which alerts the user that the
file already exists & asks whether you want to replace it with choices of ,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if the
user is using the blank template for the first time. For all subsequent
time, clicking on the macro should only save the file & not do a saveas
features.

I am a novice at this, & am sure that there is a better/more elegant way of
achieving what I am trying to do. Should this code be in a module or should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria

Saving files with names from ranges
 

Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel, they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save button,
then you need to test if the file already exists, if it doesn't then use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once the
file is saved with this name & the user clicks on the Macro button (Save)
next time, it displays the Excel message, which alerts the user that the
file already exists & asks whether you want to replace it with choices of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if the
user is using the blank template for the first time. For all subsequent
time, clicking on the macro should only save the file & not do a saveas
features.

I am a novice at this, & am sure that there is a better/more elegant way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Saving files with names from ranges
 

Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button on the
worksheet, then users may bypass it by selecting "File", "Save or Save As".
In that case, probably I should disable the 'File/Save/SaveAs' from the menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or ?
BeforeClose event. This way, it will always be activated, but when I tried
it out, I have a feeling that this saves the file twice (increasing time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user that
the
file already exists & asks whether you want to replace it with choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if
the
user is using the blank template for the first time. For all subsequent
time, clicking on the macro should only save the file & not do a saveas
features.

I am a novice at this, & am sure that there is a better/more elegant way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

Saving files with names from ranges
 

Maria

The FileSave and FileSaveAs options will always be a possibility wherever
you place the code. So to prevent this you will need to disable these menu
options but do you?

If the code is placed in the workbook before close event then the file will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign the
macro to your 'Save' control button, users can press the button to save the
file, resave it etc., if they don't then the workbook close event kicks in
and saves it anyway. The only problem is the user saves it as another name
buth this does not affect the primary operation, just clutters the system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or ?
BeforeClose event. This way, it will always be activated, but when I tried
it out, I have a feeling that this saves the file twice (increasing time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving
them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user that
the
file already exists & asks whether you want to replace it with choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more elegant
way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Saving files with names from ranges
 

Since you don't cancel the save that triggered the event, that will save the
file at least twice, maybe more as each save could trigger another
beforesave event (but fortunately doesn't - see below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.enableEvents = False
Cancel = True
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub

Would eliminate this type of recursive call and cancel the save initiated by
the user.

as a simple test - this simplified but equivalent version:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
ThisWorkbook.Save
End Sub

fires twice on each user initiated save.

--
Regards,
Tom Ogilvy


Nigel wrote in message
...
Maria

The FileSave and FileSaveAs options will always be a possibility
wherever
you place the code. So to prevent this you will need to disable these
menu
options but do you?

If the code is placed in the workbook before close event then the file
will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign the
macro to your 'Save' control button, users can press the button to save
the
file, resave it etc., if they don't then the workbook close event kicks in
and saves it anyway. The only problem is the user saves it as another
name
buth this does not affect the primary operation, just clutters the system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set
them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or ?
BeforeClose event. This way, it will always be activated, but when I
tried
it out, I have a feeling that this saves the file twice (increasing time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then
use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to
enter
information & then store the files in a directory after giving them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving
them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents
of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However
once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user
that
the
file already exists & asks whether you want to replace it with
choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an
Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only
if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more elegant
way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

Saving files with names from ranges
 

Thanks Tom, for pointing this out, I hope the original requestor benefits
from your insight
Cheers
Nigel

"Tom Ogilvy" wrote in message
...
Since you don't cancel the save that triggered the event, that will save
the
file at least twice, maybe more as each save could trigger another
beforesave event (but fortunately doesn't - see below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.enableEvents = False
Cancel = True
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub

Would eliminate this type of recursive call and cancel the save initiated
by
the user.

as a simple test - this simplified but equivalent version:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
ThisWorkbook.Save
End Sub

fires twice on each user initiated save.

--
Regards,
Tom Ogilvy


Nigel wrote in message
...
Maria

The FileSave and FileSaveAs options will always be a possibility
wherever
you place the code. So to prevent this you will need to disable these
menu
options but do you?

If the code is placed in the workbook before close event then the file
will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign
the
macro to your 'Save' control button, users can press the button to save
the
file, resave it etc., if they don't then the workbook close event kicks
in
and saves it anyway. The only problem is the user saves it as another
name
buth this does not affect the primary operation, just clutters the
system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set
them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button
on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from
the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or
?
BeforeClose event. This way, it will always be activated, but when I
tried
it out, I have a feeling that this saves the file twice (increasing
time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or
cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then
use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to
enter
information & then store the files in a directory after giving
them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users
giving
them
names on their own), I am using the following code in a macro
button
(called
Save) placed on the sheet to generate a file name based on
contents
of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as
"John
Doe -12345.xls" & saves it in the appropriate directory. However
once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user
that
the
file already exists & asks whether you want to replace it with
choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an
Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only
if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more
elegant
way
of
achieving what I am trying to do. Should this code be in a module
or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Saving files with names from ranges
 

Hello Tom & Nigel:
I really appreciate your time & efforts. I followed the suggestions by Nigel
& comment by Tom but kind of lost track of the recommendations (sorry, I am
still a novice finding my way around with VBA!).

Tom, could I ask what your suggestions would be to address the problem. Does
your reply imply that I can use the BeforeClose event & then it would save
the workbook twice & not go on to an indefinite recursive loop?
Is there a way to achieve what I want yet save the file only once? (It is a
big file 1.5 mb & takes some time to save, so I would like to avoid saving
it twice)

Thanks a lot for your valuable suggestions.

--
Maria

"Tom Ogilvy" wrote in message
...
Since you don't cancel the save that triggered the event, that will save
the
file at least twice, maybe more as each save could trigger another
beforesave event (but fortunately doesn't - see below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.enableEvents = False
Cancel = True
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub

Would eliminate this type of recursive call and cancel the save initiated
by
the user.

as a simple test - this simplified but equivalent version:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
ThisWorkbook.Save
End Sub

fires twice on each user initiated save.

--
Regards,
Tom Ogilvy


Nigel wrote in message
...
Maria

The FileSave and FileSaveAs options will always be a possibility
wherever
you place the code. So to prevent this you will need to disable these
menu
options but do you?

If the code is placed in the workbook before close event then the file
will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign
the
macro to your 'Save' control button, users can press the button to save
the
file, resave it etc., if they don't then the workbook close event kicks
in
and saves it anyway. The only problem is the user saves it as another
name
buth this does not affect the primary operation, just clutters the
system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set
them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button
on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from
the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or
?
BeforeClose event. This way, it will always be activated, but when I
tried
it out, I have a feeling that this saves the file twice (increasing
time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or
cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then
use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to
enter
information & then store the files in a directory after giving
them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users
giving
them
names on their own), I am using the following code in a macro
button
(called
Save) placed on the sheet to generate a file name based on
contents
of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as
"John
Doe -12345.xls" & saves it in the appropriate directory. However
once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user
that
the
file already exists & asks whether you want to replace it with
choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an
Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only
if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more
elegant
way
of
achieving what I am trying to do. Should this code be in a module
or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---

Saving files with names from ranges
 

Actually, Nigel was talking about Beforeclose and I was talking about
beforesave - so I didn't read it as closely as I should. However, what I
would suggest is using both.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo ErrHandler
Application.EnableEvents = False
With Worksheets(1)
If (Dir("c:\My documents\Special Folder\" & _
.Range("A1") & "- No " & .Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs FileName:= _
"c:\My documents\Special Folder\" & _
.Range("A1") & "- No " & .Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
On Error GoTo ErrHandler
Application.EnableEvents = False
With Worksheets(1)
If (Dir("c:\My documents\Special Folder\" & _
.Range("A1") & "- No " & .Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs FileName:= _
"c:\My documents\Special Folder\" & _
.Range("A1") & "- No " & .Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End With
ErrHandler:
Application.EnableEvents = True
Cancel = True
End Sub

This will not save twice.

--
Regards,
Tom Ogilvy


Maria wrote in message
...
Hello Tom & Nigel:
I really appreciate your time & efforts. I followed the suggestions by
Nigel
& comment by Tom but kind of lost track of the recommendations (sorry, I
am
still a novice finding my way around with VBA!).

Tom, could I ask what your suggestions would be to address the problem.
Does
your reply imply that I can use the BeforeClose event & then it would save
the workbook twice & not go on to an indefinite recursive loop?
Is there a way to achieve what I want yet save the file only once? (It is
a
big file 1.5 mb & takes some time to save, so I would like to avoid saving
it twice)

Thanks a lot for your valuable suggestions.

--
Maria

"Tom Ogilvy" wrote in message
...
Since you don't cancel the save that triggered the event, that will save
the
file at least twice, maybe more as each save could trigger another
beforesave event (but fortunately doesn't - see below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.enableEvents = False
Cancel = True
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub

Would eliminate this type of recursive call and cancel the save
initiated
by
the user.

as a simple test - this simplified but equivalent version:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
ThisWorkbook.Save
End Sub

fires twice on each user initiated save.

--
Regards,
Tom Ogilvy


Nigel wrote in message
...
Maria

The FileSave and FileSaveAs options will always be a possibility
wherever
you place the code. So to prevent this you will need to disable these
menu
options but do you?

If the code is placed in the workbook before close event then the file
will
either be created with the name you require or overwritten if it
already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign
the
macro to your 'Save' control button, users can press the button to
save
the
file, resave it etc., if they don't then the workbook close event
kicks
in
and saves it anyway. The only problem is the user saves it as another
name
buth this does not affect the primary operation, just clutters the
system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to
set
them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button
on
the
worksheet, then users may bypass it by selecting "File", "Save or
Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from
the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave
or
?
BeforeClose event. This way, it will always be activated, but when I
tried
it out, I have a feeling that this saves the file twice (increasing
time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the
optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or
cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the
save
button,
then you need to test if the file already exists, if it doesn't
then
use
SaveAs (what you have already), if it does exist then use just
save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No "
&
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\"
&
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to
enter
information & then store the files in a directory after giving
them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John
Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users
giving
them
names on their own), I am using the following code in a macro
button
(called
Save) placed on the sheet to generate a file name based on
contents
of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\"
&
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as
"John
Doe -12345.xls" & saves it in the appropriate directory. However
once
the
file is saved with this name & the user clicks on the Macro
button
(Save)
next time, it displays the Excel message, which alerts the user
that
the
file already exists & asks whether you want to replace it with
choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in
an
Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs
only
if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more
elegant
way
of
achieving what I am trying to do. Should this code be in a
module
or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure
Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---