Save File as, avoid overwriting

[Lisarella117]

Hey to all!

I have a problem with my "Saving Macro".

I want a file to be saved as Value in Cell C6 and at the same time overwriting of existing files must be suppressed.

When it is not possible to save the file (two reasons: 1. file name is false because it contains s.th. like \, 2. File already exists and overwriting was refused) I want a Message Box telling the user "Saving has been blocked, please rename Inquiry-No. by adding rev.-No. " and OK Buttom (special upgrade would be an Input Box where in this case the rev. No. can directly be inserted and added to the file name .. )

When Saving was successful, another Messagebox to inform the user about it should appear and tell: "Your Inquiry Process file has been successfully saved at: **Path**

I really don't know where it stucks, when the file name is correct, second message box shows up and everything is allright, but when the overwrite-display alert shows up and I press "No" to it, it does not show the other message box and it does not simply exit the sub, but Runtime Error 1004 occurs.

Here is my macro, which I puzzled by googling, so sorry for any garbage you might find in it!

_____________________________________________________
Sub CommandButtonSave_Click()

Application.DisplayAlerts = True
' Save file name and path into a variable
template_file = ActiveWorkbook.FullName
' Default directory would be G:\75 I&S\Procurement\Inquiry Process\. Users however will have the ability to change where to save the file if need be.
' only allowing the save as option to be of .xls format.
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="G:\75 I&S\Procurement\Inquiry Process\" + Rang("C6").Value + ".xls", _
fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName = False Then
MsgBox "Saving has been blocked, please rename Inquiry-No. by adding rev.-No. "
Else
End If

' Save file as .xls TAB delimited
ActiveWorkbook.SaveAs Filename:= _
fileSaveName, FileFormat:=xlNormal, _
CreateBackup:=False
file_name_saved = ActiveWorkbook.FullName
MsgBox "Your Inquiry Process file has been successfully saved at: " & vbCr & vbCr & file_name_saved

End Sub
___________________________________________________


I never worked with VBA before, I only have to do this for an Internship, and as I am in Taiwan I have no Idea where to get a library with english VBA 6.0 Books ..

I hope you can help me!!

Thanks a lot!
Lisa

 

[AlphaFrog]

Sub CommandButtonSave_Click()

    Application.DisplayAlerts = True
    ' Save file name and path into a variable
    template_file = ActiveWorkbook.FullName
    
Retry:
    ' Default directory would be G:\75 I&S\Procurement\Inquiry Process\. Users however will have the ability to change where to save the file if need be.
    ' only allowing the save as option to be of .xls format.
    
    ChDrive = "G"                                     'Default drive
    ChDir = "G:\75 I&S\Procurement\Inquiry Process\"  'Default path
    fileSaveName = Application.GetSaveAsFilename( _
                   InitialFileName:=Range("C6").Value & ".xls", _
                   fileFilter:="Excel Files (*.xls), *.xls")
    
    If fileSaveName = False Then
        MsgBox "Saving has been blocked, please rename Inquiry-No. by adding rev.-No. "
        Exit Sub
    End If
    
    If Dir(fileSaveName) <> "" Then  'Test if file exists
        ' Prompt user to retry
        If MsgBox("File: " & fileSaveName & vbLf & vbLf & "Try again?", vbExclamation + vbYesNo, "File Already Exists") = vbYes Then
            GoTo Retry
        Else
            Exit Sub
        End If
    End If

    ' File doesn't exist
    ' Save file as .xls TAB delimited
    ActiveWorkbook.SaveAs Filename:= _
                          fileSaveName, FileFormat:=xlNormal, _
                          CreateBackup:=False
    file_name_saved = ActiveWorkbook.FullName
    MsgBox "Your Inquiry Process file has been successfully saved at: " & vbCr & vbCr & file_name_saved

End Sub

 

[Lisarella117]

Wow thanks for the quick reply!

It says "Compile Error - Argument not optional" and marks the expression:

ChDrive = "G"
ChDir = "G:\75 I&S\Procurement\Inquiry Process\"

 

[AlphaFrog]

Sorry. Should be this with no equal sign.

    ChDrive "G"                                     ' Default drive
    ChDir "G:\75 I&S\Procurement\Inquiry Process\"  ' Default path

 

[Lisarella117]

Perfect!!

Thank you so much for your help with this!