Saving files with names from ranges

  • Thread starter Thread starter Maria
  • Start date Start date
M

Maria

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
 
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
 
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 said:
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

of




----== 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
Click to expand...
=---
 
Maria

The File>Save and File>SaveAs 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
 
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 said:
Maria

The File>Save and File>SaveAs 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







----== 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
Click to expand...
=---
 
Thanks Tom, for pointing this out, I hope the original requestor benefits
from your insight
Cheers
Nigel
 
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.
 
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.
 
Back
Top