Save file queries

[ylchuah]

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

[Stefi]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[ylchuah]

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


[QUOTE=Stefi]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:
[color=blue]

[Stefi]

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