Solved: auto save in excel

[Scooter172]

This is code I use to auto save and name a document in Word.. what do I need to do differant in excel to make this work?



Private Sub Document_New()
Dim MyPath As String


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

[mancubus]

hi.
try...



Private Sub Document_New()
Dim MyPath As String
MyPath = "H:\LRT\RCCDailyOpsLogs\CallOffs"


ActiveWorkbook.SaveAs FileName:=MyPath & "\" & "Daily Absenteeism Report" & _
Format(Date, "mm_dd_yyyy ddd"), FileFormat:=xlOpenXMLWorkbook
End Sub

[Scooter172]

hi.
try...



Private Sub Document_New()
Dim MyPath As String
MyPath = "H:\LRT\RCCDailyOpsLogs\CallOffs"


ActiveWorkbook.SaveAs FileName:=MyPath & "\" & "Daily Absenteeism Report" & _
Format(Date, "mm_dd_yyyy ddd"), FileFormat:=xlOpenXMLWorkbook
End Sub

Oh, I am sorry, this needs to open as an xlsm and the time should be included behind the date.

[Scooter172]

This would not rename the file, Nor would it save it to the location in the file path. It simply saved it to the same folder location and added 1 behind it.

[mancubus]

if you want to save the active workbook as macro enabled workbook in the same folder then try this:


Private Sub Workbook_New()

Dim MyPath As String, fName As String

MyPath = ThisWorkbook.Path

fName = MyPath & "\" & "Daily Absenteeism Report" & " " & Format(Now, "mm_dd_yyyy ddd hh:mm:ss")
'for 12_23_2011 Fri 20:00:00

'0r
'fName = MyPath & "\" & "Daily Absenteeism Report" & " " & FormatDateTime(Now, vbGeneralDate)
'for 12.23.2011 18:00:00

ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub

[Scooter172]

This would be a template that is kept in a folder and saved to the Directory "H:\Temp\" I am not sure in your code where this would go.

[mancubus]

post1 requirement: excel adoption of word macro which saves the active document as a new file with specified name in specified folder.
post2: done

post3-4 (changing) requirement: save as macro enabled workbook-current time at the end of file name
post5: done (saves the workbook that runs the macro as new file in the same folder. )

post6 (changing) requirement:
not clear to me.

if you want to save current file as macro enabled template then replace blue text with red text in the code in the post#5.

MyPath = ThisWorkbook.Path
MyPath = "H:\Temp\"

xlOpenXMLWorkbookMacroEnabled
xlOpenXMLTemplateMacroEnabled

[Scooter172]

The code below allows this to be saved to the location needed, But any attempt to add a dateand time of creation to the end of the file name of this code gives me a Run Time error 1004 "application-defined or Object defined error
Private Sub Workbook_Open()
Dim MyPath As String, fName As String
MyPath = "H:\LRT\RCCDailyOpsLogs\LRV Trouble "
fName = MyPath
'for 12_23_2011 Fri 20:00:00

'0r
'fName = MyPath & "\" & "Daily Absenteeism Report" & " " & FormatDateTime(Now, vbGeneralDate)
'for 12.23.2011 18:00:00

ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub

[Scooter172]

This is the working solution... this now works and is solved!
Patience Is a Vertue!
Private Sub Workbook_Open()
Dim MyPath As String, fName As String
MyPath = "H:\LRT\RCCDailyOpsLogs\LRV Trouble "
fName = MyPath

ActiveWorkbook.SaveAs Filename:=fName & Format(Date, " mm_dd_yyyy ddd ") & Format(Time, " _hh_mm "), FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub

[Scooter172]

I may have marked solved to soon... once the file is named and saved it updates the time once it re-opens the file in the same directory. This should only be named and time stamped once. Any Ideas...