File Exists Killing Workbook_Open()
 

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:


fname = Application.InputBox( _
Prompt:="Please enter file name",
Title:="File name", Type:=2)
Do Until Dir(fname) = ""
If fname = "False" Then Exit Sub 'user clicked
Cancel
fname = Application.InputBox(Prompt:="FileName
Exists Please Try Again.", Title:="File Exists",
Type:=2)
Loop


ThisWorkbook.SaveAs Filename:=fname


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.

File Exists Killing Workbook_Open()
 

Hi Mike
what is your exact problem with the code below. It should work. did you
get an error message?

--
Regards
Frank Kabel
Frankfurt, Germany

Mike wrote:
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:


fname = Application.InputBox( _
Prompt:="Please enter file name",
Title:="File name", Type:=2)
Do Until Dir(fname) = ""
If fname = "False" Then Exit Sub 'user clicked
Cancel
fname = Application.InputBox(Prompt:="FileName
Exists Please Try Again.", Title:="File Exists",
Type:=2)
Loop


ThisWorkbook.SaveAs Filename:=fname


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.

message unavailable
 

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
-----Original Message-----
Message unavailable

File Exists Killing Workbook_Open()
 

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

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
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:


fname = Application.InputBox( _
Prompt:="Please enter file name",
Title:="File name", Type:=2)
Do Until Dir(fname) = ""
If fname = "False" Then Exit Sub 'user clicked
Cancel
fname = Application.InputBox(Prompt:="FileName
Exists Please Try Again.", Title:="File Exists",
Type:=2)
Loop


ThisWorkbook.SaveAs Filename:=fname


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.

File Exists Killing Workbook_Open()
 

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.


"Frank Kabel" wrote in message
...
Hi Mike
what is your exact problem with the code below. It should work. did you
get an error message?

--
Regards
Frank Kabel
Frankfurt, Germany

Mike wrote:
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:


fname = Application.InputBox( _
Prompt:="Please enter file name",
Title:="File name", Type:=2)
Do Until Dir(fname) = ""
If fname = "False" Then Exit Sub 'user clicked
Cancel
fname = Application.InputBox(Prompt:="FileName
Exists Please Try Again.", Title:="File Exists",
Type:=2)
Loop


ThisWorkbook.SaveAs Filename:=fname


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.

File Exists Killing Workbook_Open()
 

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"


In article ,
"Michael Hardy" wrote:

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.

File Exists Killing Workbook_Open()
 

Perfect.. Is there anyway to make it like vbokonly?
"JE McGimpsey" wrote in message
...
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"


In article ,
"Michael Hardy" wrote:

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.

File Exists Killing Workbook_Open()
 

Hmmm... perfect, but needs a tweak...

Can't do it using InputBox. Easily done with a Userform.


In article ,
"Michael" wrote:

Perfect.. Is there anyway to make it like vbokonly?

File Exists Killing Workbook_Open()
 

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.




"JE McGimpsey" wrote in message
...
Hmmm... perfect, but needs a tweak...

Can't do it using InputBox. Easily done with a Userform.


In article ,
"Michael" wrote:

Perfect.. Is there anyway to make it like vbokonly?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.588 / Virus Database: 372 - Release Date: 2/13/2004