A file named....already exists in this location. Do you want to replace it? Yes, No, Cancel"

[raphagcwill]

Hello there.

Here I am again hoping to get some help with something you may consider a piece of cake

The code below opens a windows with the suggested file name and format.

If there is no file with the same name in the folder and the user clicks on "Save", then everything works great.

If a file with the same name already exists in the folder, when the user clicks on "Save" a new windows pops up:
"A file named....already exists in this location. Do you want to replace it? Yes, No, Cancel"

When "Yes" is chosen, then the new file overwrites the old one.

When either "No" or "Cancel" is chosen, then the user gets the 1004 error. "Run time error 1004, Method of "SaveAs" of object '_Workbook failed. "
The debug brings me to this line:
ActiveWorkbook.SaveAs Filename:=DataFileName

What I am trying to do is to bypass this error.

I would like the macro to reopen the "Saveas" window when user chooses "No" and to exit sub when the user chooses "Cancel", without any Debug.

The code was originally copied from
http://www.mrexcel.com/forum/excel-questions/672970-application-getsaveasfilename-help.html

I did my reasearch and found a similiar issue and a possible solution, but I am not sure how to apply it to my case
GetSaveAs problem when filename already exists | Windows Secrets Lounge

    'Rename first sheet 


    ActiveSheet.name = "Raphael_" & Range("J2").Value & "_" & Range("L2").Value
    
    
    fName = Application.GetSaveAsFilename(InitialFileName:="Raphael_" & Range("J2").Value & "_" & Range("L2").Value, FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save As")
    If fName = False Then Exit Sub


    ActiveWorkbook.SaveAs Filename:=fName

   
            
        'File saved
  
         MsgBox "File successfully created under Raphael folder"

Thanks in advance,

Kind Regards,
Raphael

 

[excelginge]

Hi raphagcwilll,

What I usually do in situations like this is to create a loop to see if file already exists. This way you never get Dups.

If not save file as

Filename.xlsm


if found

try save as

Filenamea.xlsm

if also found save as

Filenameb.xlsm

and so on

If you want the code for this just let me know.

OR

You could check to see if file already exists, before you save, if so ask user for new name. If not just save file

If you need the code for this let me know.


OR

You could check to see if file already exists, before you save, if so delete the existing file then write the new file with the same name.

If you need the code for this let me know.



The choice is yours?

Excelgringe

 

[raphagcwill]

OR

You could check to see if file already exists, before you save, if so ask user for new name. If not just save file

If you need the code for this let me


Excelgringe[/QUOTE]

Hi and thanks for taking the time to look at this case. Would appreciate if you could send me the code for the option above, if it is not too much to asks. I am no do familiar with programming.

Regards,
Raphael

 

[excelginge]

Hi raphagcwilll,

Have included some useful functions that can be used over & over again to see if either file or folder or both exists!

Hope this helps


Sub SaveFile()

Dim strFolderName As String 'Folder Name
Dim strFileName As String 'File Name
Dim strExt As String 'File extention
Dim strFullName As String

strFolderName = "C:\test1"
strFileName = "Raphael_INJ2_INL2" 'this should be as per line below
'strFileName = "Raphael_" & Range("J2").Value & "_" & Range("L2").Value
strExt = ".xlsm"
'make sure no slash '\' at end of string

If Right(strFolderName, 1) = "\" Then strFolderName = Left(strFolderName, Len(strFolderName) - 1)

'make sure folder exists if not get one

If FolderExists(strFolderName) = False Then
'if no folder exists ask user for either
'1. InputBox to get new folder
'or '2. use getFolder Fuction(See below) to get an existing folder
End If

'does folder & file exist?

strFullName = strFolderName & "\" & strFileName & strExt


If FileExists(strFullName) = True Then
'if file already exists ask user for new name
'1. InputBox to get new file name
End If

ActiveWorkbook.SaveAs strFullName, xlOpenXMLWorkbookMacroEnabled ' fileformat:=xlOpenXMLWorkbookMacroEnabled (52) allows as as a macro workbook


MsgBox "In folder " & strFolderName & vbNewLine & vbNewLine _
& "with a file name of " & strFileName & strExt, vbInformation + vbOKOnly, "File successfully created"
End Sub


Function FolderExists(strPath As String) As Boolean
On Error Resume Next
FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
'Purpose: Return True if the file exists, even if it is hidden.
'Arguments: strFile: File name to look for. Current directory searched if no path included.
' bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
'Note: Does not look inside subdirectories for the file.
Dim lngAttributes As Long


'Include read-only files, hidden files, system files.
lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)


If bFindFolders Then
lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
Else
'Strip any trailing slash, so Dir does not look inside the folder.
Do While Right$(strFile, 1) = "\"
strFile = Left$(strFile, Len(strFile) - 1)
Loop
End If


'If Dir() returns something, the file exists.
On Error Resume Next
FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function


Excelginge