CODEWITHSUNDEEP
excelsave-asvba
stever
stever

Reputation: 13

Saveas function excel. with name from cell

I am trying to make the following code work for saving a file name in a certain format. I would like it to save in the folder the file was opened up in. the file would change it's name to a new month name. I have got most of it working, such as directory selection and filename and for it to save, however, if there is already a file with the same name or if someone selects no or cancel it gives an error. I have tried various ways of trying to get around it but now I'm at a loss. I have 2 codes they both are supposed to do the same thing, just variations.

Sub saving1()
' Saves the file under a new name based on the new month date.
    Dim NewFilename As String
    Dim tempnm
    Dim loc                                           ' variable for file location
    loc = Application.ThisWorkbook.Path               'loads the file location on the loc variable
    MsgBox loc
    ' creates the file name for saving includes the current path.
    NewFilename = loc + "\" + Range("NewFileName").Value & ".xlsm"
    'tempmm = Application.GetSaveAsFilename initialfilename

    ActiveWorkbook.SaveAs NewFilename, FileFormat:=52
    'Application.DisplayAlert = False
    'On Error Resume Next    'to omit error when cancel is pressed
    '  MsgBox "Not saved"
    'ActiveWorkbook.Save

    'If Err.Number <> 1004 Then  'optional, to confirmed that is not saved
    '  MsgBox "Not saved"
    'End If
    ' On Error GoTo 0         'to return standard error operation

End Sub

Sub saving()
' Saves the file under a new name based on the new month date.
    Dim NewFilename As String
    Dim loc                                           ' variable for file location
    loc = Application.ThisWorkbook.Path               'loads the file location on the loc variable
    ' creates the file name for saving includes the current path.
    NewFilename = loc + "\" + Range("NewFileName").Value & ".xlsm"
    ActiveWorkbook.SaveAs NewFilename, FileFormat:=52

End Sub

I also added message boxes to try see what it is doing during testing. I have also tried the Getsaveasfilename in order to give the user an option to choose his/her own filename and possibly folder. The file location will change once a year.

Upvotes: 0

Views: 56

Answers (1)

Siva
Siva

Reputation: 1149

If Your are looking at overwriting existing file, when there's already a file with same name try below.

NewFilename = loc + "\" + Range("NewFileName").Value & ".xlsm"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs NewFilename, FileFormat:=52
Application.DisplayAlerts = True

Upvotes: 1

Related Questions