File Exists Killing Workbook_Open()

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a little code that saves the current file as a
user input name as soon as the file is opened. I am
trying to figure out how to keep the code from breaking
when the user chooses a filename that exists.

The following code was suggested:



I haven't been able to get this to work. I am not sure
if I am doing something wrong or not.

Any help with a solution is greatly appreciated.
 
Hi Mike
what is your exact problem with the code below. It should work. did you
get an error message?
 
This what I see.

If anyone can explain this to me so I can make it quit
Please do. you can email me recs_4u@bellsouth. net

Thanks
 
Here is one I use that will ask if you want to overwrite. It uses a typed
name in a cell named clientname.

Sub SaveClientName()
On Error Resume Next
ActiveWorkbook.SaveAs Filename:= _
"C:\yourfolder\" & [clientname] & ".xls"
End Sub
 
Frank

Thanks for the tips....

Ok what it does it seems to skip by the Do condition.. Like it is always
true.

So even if the filename chosen exists it still goes the saveas. Then I get
the standard do you want to overwrite message from excel. Of courseif I
choose no the macro dies.
 
Does this happen when you *don't* enter an extension?

This is a bit more robust:


Const sPROMPT1 As String = "Please enter file name."
Const sPROMPT2 As String = "Filename exists. Please try again."
Dim bValidName As Boolean
Dim sFName As String

Do
sFName = Application.InputBox( _
Prompt:=IIf(Len(sFName) = 0, sPROMPT1, sPROMPT2), _
Title:="File Name", _
Type:=2)
If sFName = "False" Then Exit Sub 'user clicked cancel
If Right(sFName, 4) = ".xls" Then _
sFName = Left(sFName, Len(sFName) - 4)
If Len(sFName) > 0 Then bValidName = (Dir(sFName & ".xls") = "")
Loop Until bValidName
ThisWorkbook.SaveAs Filename:=sFName & ".xls"
 
Hmmm... perfect, but needs a tweak...

Can't do it using InputBox. Easily done with a Userform.
 
Well in relation to the question asked the answer was absolutely perfect.
It was the question that needed the tweak actually.


Thanks a ton for all of the help I have gotten.
 
Back
Top