First of all, I'm a total Excel interop noob.
I'm trying to get a date from a cell and then set the title of the document before the document gets saved, to be the month of the date. This is my code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Title = DateTime.Month(ThisWorkbook.Sheets("Sheet1").Cell("A10"))
End Sub
I'm not sure that anything is working. I set a breakpoint on the code, but I can't "run" it because it's not a macro, but an event handler, so I don't think the breakpoint is going to work. I don't get any errors. I don't even know that ThisWorkbook.Title
is what I want and I'm not even sure about getting the month from the cell.
The title of the document is a "Built In" property - this is the info that appears when you right click on the file and look at the properties.
The name of the spreadsheet is set on save, so you will want to save the file with a new name if you want to see the date on the file itself
A code something like this should give you the result you desire:
(note that this code is VBA, so it may need some tweaking to work in interop.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FilePath As String
Dim varName As String
On Error GoTo ErrorHandler
' This disables all Excel events.
Application.EnableEvents = False
' disable the default behaviour of the save like so:
Cancel = True
'you can leave this blank if you want it to save in the default directory
FilePath = "C:\The path\To\The File"
varName = Format(ThisWorkbook.Sheets("Sheet1").Cell("A10"),"mmmm")
ActiveWorkbook.SaveAs Filename:=FilePath & varName & ".xlsx"
ErrorExit:
' This makes sure events get turned back on again no matter what.
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox "No value submitted - File Not Saved"
Resume ErrorExit
End Sub