re: 'Save As' routine

I have used the command workbook.SaveAs = intReturn1, where intReturn1 is
the value returned from an input box allowing a user to save an Excel
workbook under the name they wish.

Does anyone know of the instructions necessary to inform a user that a file
already exists in that location, and that they need to save it under a
different name?

--
Charles Dean

John
Thu Sep 06 06:23:30 PDT 2007


"CCDEAN100" <CCDEAN100@discussions.microsoft.com> wrote in message news:76037B92-9A6D-4E95-BC85-2B72C97838AC@microsoft.com...
>
> I have used the command workbook.SaveAs = intReturn1, where intReturn1 is
> the value returned from an input box allowing a user to save an Excel
> workbook under the name they wish.
>
> Does anyone know of the instructions necessary to inform a user that a file
> already exists in that location, and that they need to save it under a
> different name?
>
> --
> Charles Dean

One way is using FileSystemObject.FileExists, as shown in the following code. You'll have to refine it slightly if the user
enters the file extension, but this gives you the general idea.

Dim objFso
Dim sPath, sFile, sFilename, fileExists, sPrompt

Set objFso = CreateObject("Scripting.FileSystemObject")

sPath = "C:\temp\"
sPrompt = "Enter filename to save as"
Do
sFilename = InputBox (sPrompt)
sFile = sPath & sFilename & ".xls"
fileExists = False
If objFso.FileExists(sFile) Then
sPrompt = sFilename & " already exists, please enter a different filename"
fileExists = True
End If
Loop While fileExists

objExcelWorkbook.SaveAs sFile

CCDEAN100
Thu Sep 06 12:26:01 PDT 2007


Thanks, that code works great.

Bishop
Fri Jul 18 03:23:01 CDT 2008

and for a better handling you can give the user a "Save as" Dialog

Here is a Sample

http://www.scriptbox.at.tt/index.php?search=Dialog%20SaveFile.vbs


"CCDEAN100" wrote:

>
> I have used the command workbook.SaveAs = intReturn1, where intReturn1 is
> the value returned from an input box allowing a user to save an Excel
> workbook under the name they wish.
>
> Does anyone know of the instructions necessary to inform a user that a file
> already exists in that location, and that they need to save it under a
> different name?
>
> --
> Charles Dean