Saving files with names from ranges

[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]

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

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

=---

 

[Maria]

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.

 

[Tom Ogilvy]

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.