Auto Name a workbook

[Scooter172]

I want to create a workbook and have the names auto name consistently each day. with the format "workbook Name" _ "mm/dd/yy". This is what I already have. It places the date but not the book name.


Private Sub Workbook_New()Dim MyPath As String
MyPath = "C:\Users\Scott\Documents"

ActiveWorkbook.SaveAs Filename:=MyPath & "\" & "NewTest" & _
Format(Date, "mm_dd_yyyy ddd"), FileFormat:=xlOpenXMLWorkbook
End Sub




:banghead:

[Zack Barresse]

Hi there,

So you're saying when you run the code it doesn't have the file name of 'NewTest07_28_2013 Sat.xlsx' ? What does it save the name as? I can't reproduce what you are saying. If you can reproduce it in the file, can you upload the file?

[Scooter172]

Where I used to work I had a book that would open and auto name and save to a location. I am trying to modify that code to work in my new position at another location. Here is theoriginal code from that old job.


Private Sub Workbook_Open() If IsEmpty(ThisWorkbook.Sheets("Markup").[OpsDate]) Then
markupDispatcher = InputBox("Insert your name...", "This markup proudly created by...", "Enter Name Here")
If markupDispatcher = 5614 Then Exit Sub 'This is the maintenance bypass key used to perform updates to template
[markupDisp] = markupDispatcher
If IsEmpty([markupDisp]) Then [markupDisp] = "[markup dispatcher name]"

ThisWorkbook.Sheets("Markup").[OpsDate] = Date + 1


If Month([OpsDate]) < 10 Then
fMonth = 0 & Month([OpsDate])
Else
fMonth = Month(ThisWorkbook.Sheets("Markup").[OpsDate])
End If
' Add a leading zero in front of single digit days
If Day([OpsDate]) < 10 Then
fDay = 0 & Day([OpsDate])
Else
fDay = Day([OpsDate])
End If


fDate = Year([OpsDate]) & "-" & fMonth & "-" & fDay & " " & WeekdayName(Weekday([OpsDate]), True)

fName = "F:\RCCDailyOpsLogs\Ops Table " & fDate & ".xlsm"
' If the file name already exists, SaveAs will ask to overwrite. If "No" is chosen an error will occur.
On Error Resume Next 'Skip the SaveAs if an error occurs
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52

End If
End Sub

[Zack Barresse]

Your old code, the last one posted, you see this line...

fName = "F:\RCCDailyOpsLogs\Ops Table " & fDate & ".xlsm"
You're saying in the F drive, the lowest sub-folder is RCCDailyOpsLogs, and 'OpsTable " is part of the filename. Is that correct?

I see you trimmed the original code significantly, as a lot of that wasn't needed in lieu of using a custom format. Well done. A quick suggestion regarding your old code and using On Error Resume Next. That is really one one type of error handling, and it is the most clunky and dangerous. Instead of ignoring all errors, we can use the Dir() method to actually see if a file is there or not, thus not even needing error handling, just robust coding.

So instead of this...

On Error Resume Next 'Skip the SaveAs if an error occurs
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
... you could use this...


If Dir(fName, vbNormal) = vbNullstring Then
'file exists there already, do stuff here
Else
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
End If
That is, if you don't want to overwrite the file, which I'm assuming you don't want to do. If you want to delete the file and manually overwrite it, use the Kill method to remove the file, then do a save as. Using the Kill method on a file which doesn't exist will also throw an error, but returning a string value with the Dir() method will not.

Another thing I should mention is you do not declare your variables. It is a good habit to start doing so. This would simply be this at the top of your (old) code...

Dim fName As String
Dim fDate As String

The code works great for me. I did look a little closer at the first code you posted, the new code you were trying to use. Why are you using this line...

Private Sub Workbook_New

Where are you calling it from? I'm not familiar with the Workbook_New event. Did you create this event? Or do you mean to use the Open event? I think this is the heart of your current problem.

[Scooter172]

Your old code, the last one posted, you see this line...

fName = "F:\RCCDailyOpsLogs\Ops Table " & fDate & ".xlsm"
You're saying in the F drive, the lowest sub-folder is RCCDailyOpsLogs, and 'OpsTable " is part of the filename. Is that correct?

I see you trimmed the original code significantly, as a lot of that wasn't needed in lieu of using a custom format. Well done. A quick suggestion regarding your old code and using On Error Resume Next. That is really one one type of error handling, and it is the most clunky and dangerous. Instead of ignoring all errors, we can use the Dir() method to actually see if a file is there or not, thus not even needing error handling, just robust coding.

So instead of this...

On Error Resume Next 'Skip the SaveAs if an error occurs
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
... you could use this...


If Dir(fName, vbNormal) = vbNullstring Then
'file exists there already, do stuff here
Else
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
End If
That is, if you don't want to overwrite the file, which I'm assuming you don't want to do. If you want to delete the file and manually overwrite it, use the Kill method to remove the file, then do a save as. Using the Kill method on a file which doesn't exist will also throw an error, but returning a string value with the Dir() method will not.

Another thing I should mention is you do not declare your variables. It is a good habit to start doing so. This would simply be this at the top of your (old) code...

Dim fName As String
Dim fDate As String

The code works great for me. I did look a little closer at the first code you posted, the new code you were trying to use. Why are you using this line...

Private Sub Workbook_New

Where are you calling it from? I'm not familiar with the Workbook_New event. Did you create this event? Or do you mean to use the Open event? I think this is the heart of your current problem.


Zach,

The file Path was the correct old path. I am testing this using "C:\User\Scott\Documents path. Correct in that I do not want to overright the older books. When I edited the other code it would only create an xlsx book. with the number 1 behind it. Essentially not doing what was aked. In reading your response This is what I have come up with but it does not work either. I am the first to admit my shortcomings on VBA but am trying to learn as I need to use/edit it.


Private Sub Workbook_Open()Dim MyPath As String
Dim fName As String
Dim fDate As String
fDate = Format(Date, "dd-mm-yy")
fName = "C:\User\Scott\Documents\Testworkbook " & fDate & ".xlsm"
MyPath = "C:\User\Scott\Documents\Testworkbook " & fDate & ".xlsm"
If Dir(fName, vbNormal) = vbNullString Then
'file exists there already, do stuff here
Else
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
End If
End Sub

[Scooter172]

This is the Code I use for word docs, to auto name and save, can this be manipulated to work with excel.
Option Explicit



Private Sub Document_New()
' This routine saves the document to a specific location with a name and time format.
Dim MyPath As String

MyPath = "C:\User\Scott\Documents\CallOffs\"
ActiveDocument.SaveAs FileName:=MyPath & "Daily Absenteeism Report" & _
Format(Date, " mm_dd_yyyy ddd ") & Format(Time, " _hh_mm ") & ".docx", _
FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False
End Sub

[Zack Barresse]

We can get the code to work, but there is no "Workbook_New" event, unless you have created it yourself. Just explain what you are trying to do. Let me see if I understand...

When you open the file, you want to always save the file as a new file for the day.
There should only be one file per day.
If you open the file and there is no file for that day, it should save the current file as the new days file.
The path will not change, only the date in the filename.

Is that about right? Also, what exactly do you want the filename to be?

[Scooter172]

We can get the code to work, but there is no "Workbook_New" event, unless you have created it yourself. Just explain what you are trying to do. Let me see if I understand...

When you open the file, you want to always save the file as a new file for the day.
There should only be one file per day.
If you open the file and there is no file for that day, it should save the current file as the new days file.
The path will not change, only the date in the filename.

Is that about right? Also, what exactly do you want the filename to be?

Zack,

That is all Correct, the exception to this compared to the word doc is in how the date would be formatted. In the word doc we are creating several a day, so it included the time of creation in the file name, (That doc was designed to be a form type doc that was filled out when employees called off work sick, doctor, personal holiday etc; then mailed to those who needed to know.) This excel workbook would be created once a day but every day, being stored in the designated folder, with a defined naming format. The reason Is that those opening/creating the workbook would only be able to create it from a shortcut and not be able to open from the folder Using Open on right click as when editing the template instead of New. Idealy this would be commanded from a Button on Excel Ribbon.

Our agency demands that templates be protected to some degree.

Scott

[Scooter172]

This does work in Word... The original code sent had User instead of Users so it hacked. It is Option Explicit as that was the only way it ran.

Private Sub Document_New()
' This routine saves the document to a specific location with a name and time format.
Dim MyPath As String

MyPath = "C:\Users\Scott\Documents\"
ActiveDocument.SaveAs FileName:=MyPath & "Daily Absenteeism Report" & _
Format(Date, " mm_dd_yyyy ddd ") & Format(Time, " _hh_mm ") & ".docx", _
FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False
End Sub

[Zack Barresse]

Ok, I understand how it can work in Word. It is a Document_New event for when you create a new document, which I'm assuming you're code is located in your Normal file. What I'm asking is what event you want to use in Excel? There is no built-in event for creating a new workbook.

I'm assuming the file you're trying to create with the SaveAs event is based on another file, right? I'm trying to discern exactly what event you want to use. It seems you want the Open event, but you keep referencing a Word event (New) which Excel does not have.

If the path and filename are correct in the Excel code (you need to confirm this), I'm not sure what's wrong as it works for me. Maybe you could step through your Excel code, and right before your SaveAs line do a print to the Immediate pane (Ctrl + G in the VBE will show it). To do a break-point, put your cursor on the line (no Dim statements) and press F9 (the line will turn red). When your code runs use F8 to step through it line-by-line. If you want you can use the command line "Stop" (on it's own line) where you want the code to break at (then use F8 from there).

Did you use the Dir() line I posted above? If so, what does it evaluate to? (Which portion of the If/Then clause gets evaluated?)

[Scooter172]

I can step through this using f8 and it stops and turns yellow at Line fDate,fName,then end if. I used Ctrl + G at each line that turned yellow but nothing displayed in immediate window. If I reset code and run with green arrow it does not stop anywhere but still it will not create of Save, name file to the commaded folder. Im going to walk away for today...


Private Sub Workbook_Open()Dim fName As String
Dim fDate As String
fDate = Format(Date, "dd-mm-yy")
fName = "C:\Users\Scott\Documents\Testworkbook " & fDate & ".xlsm"
If Dir(fName, vbNormal) = vbNullString Then
'file exists there already, do stuff here
Else
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
End If
End Sub

[Zack Barresse]

Sorry if I was confusing, CTRL + G will only show the Immediate pane. So if it's already showing it won't do anything.

What I'm wondering is, after the If Dir(... line evaluates (press F8 on that line), what gets executed next? Since there is nothing in the True clause, it will either go to the ThisWorkbook.SaveAs... or End If. Which one?

And I must apologize, I had the If statement backwards. Really sorry! If the Dir() function evaluates to a vbNullstring that means that the file doesn't exist. I sincerely hope this didn't cause too much trouble. :(

So you'd probably want the logic to be if the file doesn't exist, save it, otherwise don't do anything, which would be the following If/Then clause...

If Dir(fName, vbNormal) <> vbNullString Then
'file exists there already, do stuff here
Else
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
End If

[Scooter172]

Zack,

Had to come back and try one more time, and your last post solved the riddle (sleeping tonight!:) ) here is the final solved code. Its short and simple and something that can be used with any workbook. Thanks for the Help..



Private Sub Workbook_Open()Dim fName As String
Dim fDate As String
fDate = Format(Date, "mm-dd-yy")
fName = "C:\Users\Scott\Documents\Testworkbook " & fDate & ".xlsm"
If Dir(fName, vbNormal) <> vbNullString Then
'file exists there already, do stuff here
Else
ThisWorkbook.SaveAs Filename:=fName, AddToMru:=True, FileFormat:=52
End If
End Sub


Scott

[Zack Barresse]

You are very welcome. Apologies for the mix up again. :(