Hi,

I have this line of code (Y is a string)

ActiveWorkbook.SaveAs Filename:=Y

when there are a same file name "Y" exist in the same location,
but i don't want to overwrite the file,
what code that need to add,
in order to ask the user to enter another file name instead o
overwrite the file?

Regards
yl chuah :

--
ylchua
-----------------------------------------------------------------------
ylchuah's Profile: http://www.excelforum.com/member.php...fo&userid=2534
View this thread: http://www.excelforum.com/showthread.php?threadid=40037

Do While FileExists(Y)
Y = InputBox(Y & " does exist!" & Chr(10) & "Enter new filename!")
Loop
ActiveWorkbook.SaveAs Filename:=Y

Public Function FileExists(fname As String) As Boolean
FileExists = IIf(Dir(fname) < "", True, False)
End Function

Regards,
Stefi

€žylchuah€� ezt Ã*rta:


Hi,

I have this line of code (Y is a string)

ActiveWorkbook.SaveAs Filename:=Y

when there are a same file name "Y" exist in the same location,
but i don't want to overwrite the file,
what code that need to add,
in order to ask the user to enter another file name instead of
overwrite the file?

Regards
yl chuah :(


--
ylchuah
------------------------------------------------------------------------
ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341
View this thread: http://www.excelforum.com/showthread...hreadid=400373

do
y = application.GetSaveAsFileName()
if dir(y) < "" then
msgbox "File exists, please provide another name rather than" _
& vbCrLf & vbCrLf & y
else
exit do
end if
Loop while True

ActiveWorkbook.SaveAs Filename:=Y

--
Regards,
Tom Ogilvy

"ylchuah" wrote in
message ...

Hi,

I have this line of code (Y is a string)

ActiveWorkbook.SaveAs Filename:=Y

when there are a same file name "Y" exist in the same location,
but i don't want to overwrite the file,
what code that need to add,
in order to ask the user to enter another file name instead of
overwrite the file?

Regards
yl chuah :(


--
ylchuah
------------------------------------------------------------------------
ylchuah's Profile:
http://www.excelforum.com/member.php...o&userid=25341
View this thread: http://www.excelforum.com/showthread...hreadid=400373

Hi, Stefi,

Thanks for your help.

But i have try this code,
error occurs. (Expected End Sub)

Actually i don't know how to use the public function.

in my exisitng programme, right after this line
'ActiveWorkbook.SaveAs Filename:=PCLname'
will have a window pop out say
"A file named "Y" already exists in this location. Do you want to
replace it?"
when i click "No" or "Cancel" run-rime error '1004' happen
"Method 'SaveAs' of object'_Workbook' failed

So, any idea?

Regards
yl chuah


Stefi Wrote: [color=blue]
Do While FileExists(Y)
Y = InputBox(Y & " does exist!" & Chr(10) & "Enter new filename!")
Loop
ActiveWorkbook.SaveAs Filename:=Y

Public Function FileExists(fname As String) As Boolean
FileExists = IIf(Dir(fname) < "", True, False)
End Function

Regards,
Stefi

€žylchuah€� ezt Ã*rta:



--
ylchuah
------------------------------------------------------------------------
ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341
View this thread: http://www.excelforum.com/showthread...hreadid=400373

Hi ylchuah,

I suppose the problem comes from wrongly inserting my piece of code into
your sub, but I have to see the whole sub to find the error.

But i have try this code,
error occurs. (Expected End Sub)
I suppose the problem comes from wrongly inserting my piece of code into
your sub, but I have to see the whole sub to find the error.

Actually i don't know how to use the public function.
Just copy it after your sub.
Or use Tom's piece of code, it does the same without UDF.

in my exisitng programme, right after this line
'ActiveWorkbook.SaveAs Filename:=PCLname'
will have a window pop out say
"A file named "Y" already exists in this location. Do you want to
replace it?"
when i click "No" or "Cancel" run-rime error '1004' happen
"Method 'SaveAs' of object'_Workbook' failed

PCLname is a variable that contains the string "Y".
If you already have this file, and if you reply No, control go back to the
same statement
'ActiveWorkbook.SaveAs Filename:=PCLname'
and gives an error, because VB cannot execute it without your permission.
To go around it use the above procedure, or - if you do not want to ask a
new filename, do this:
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=PCLname
On Error GoTo 0

Regards,
Stefi
[color=blue]
So, any idea?

Regards
yl chuah


Stefi Wrote:
Do While FileExists(Y)
Y = InputBox(Y & " does exist!" & Chr(10) & "Enter new filename!")
Loop
ActiveWorkbook.SaveAs Filename:=Y

Public Function FileExists(fname As String) As Boolean
FileExists = IIf(Dir(fname) < "", True, False)
End Function

Regards,
Stefi

€žylchuah€Â� ezt Ã*rta:



--
ylchuah
------------------------------------------------------------------------
ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341
View this thread: http://www.excelforum.com/showthread...hreadid=400373