CODEWITHSUNDEEP
excelvba
Adam Jakiela
Adam Jakiela

Reputation: 2248

Visual Basic Excel Function to Name File off of Cell Content

I am trying to write a function in Visual Basic to create the filename of a spreadsheet based off of values in certain cells.

So far I have been using this code: 

Public Sub SaveAsA1()
ThisFile = "RWO_" + Format(Now(), "yyyymmdd") + "_" + Format(Now(), "hhmm") + "_" + Range("D4").Value + "_" + Range("F5").Value + "_" + Range("D8").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

This code works the first time I save the file, however, whenever I resave the file, it does not update the file names based on new values in the cells.

Does anyone know how I could go about writing a function to do this?

Upvotes: 0

Views: 1285

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

You could try to do it by adding code for BeforeSave event. It will save file normally but will change the name as you expect each time you press SaveAs button.

This code should be located in ThisWorkbook module (Workbook module).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If SaveAsUI = True Then

        Application.EnableEvents = False
        Dim ThisFile As String

        ThisFile = "RWO_" + Format(Now(), "yyyymmdd") + "_" + Format(Now(), "hhmmss") + "_" + Range("D4").Value + "_" + Range("F5").Value + "_" + Range("D8").Value & ".xlsm"
        ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
        Cancel = True
    End If

End Sub

Please note that I've change ThisFile by adding ss to time and .xlsm extension at the end. Also I've added FileFormat parameter to .SaveAs method.

Upvotes: 3

Related Questions