Solved: "Save As" Date as part of file name

[canselmi]

I need help again. I have a button macro open a form with a predefined path and need it to use as part of the filename, cell H4 which is a date. No matter how I format the cell it still wants to save the date with / instead of -. H4 is formatted as MM-DD-YYYY and it still uses MM/DD/YYYY.

Also, how do I get the path to save to the "My Documents" of the user who is saving the document.

Here is the code:

Private Sub SaveWorkbookAs_Click()
'
' Macro recorded 03/10/2008


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
DestFile = InputBox _
("Enter the full path and file name to save." _
& Chr(10) & "Example- C:\Documents and Settings\User\My Documents\Technician Activity Log Week of 03-04-2008.xls (valid path)" _
& Chr(10) & "Make sure to tag the filename with the 'XLS' extention, so you continue working on it at a later date." _
& Chr(10) & "The Click on OK to save the workbook.", "Technician Activity Log - SAVE WORKBOOK AS", "C:\Documents and Settings\" & (User) & "\My Documents\Technician Activity Log Week of " & (Sheets("Monday").Range("H4")) & ".xls")


On Error Resume Next

ActiveWorkbook.SaveAs Filename:=DestFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
If Err <> 0 Then
MsgBox " DID NOT SAVE WORKBOOK. " _
& Chr(10) & "" _
& Chr(10) & " Possible causes," _
& Chr(10) & "" _
& Chr(10) & "- Action cancelled by you." _
& Chr(10) & "- No filename was specified." _
& Chr(10) & "- The destination directory does not exist. Create it and try again." _
Exit Sub
End If
On Error GoTo lineabort

MsgBox "Workbook succesfully saved as " & DestFile
Exit Sub
lineabort:
Exit Sub


End Sub

[Simon Lloyd]

[VBA]Private Sub SaveWorkbookAs_Click()
'
' Macro recorded 03/10/2008


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
DestFile = InputBox _
("Enter the full path and file name to save." _
& Chr(10) & "Example- C:\Documents and Settings\User\My Documents\Technician Activity Log Week of 03-04-2008.xls (valid path)" _
& Chr(10) & "Make sure to tag the filename with the 'XLS' extention, so you continue working on it at a later date." _
& Chr(10) & "The Click on OK to save the workbook.", "Technician Activity Log - SAVE WORKBOOK AS", "C:\Documents and Settings\" & (User) & "\My Documents\Technician Activity Log Week of " & (format(Sheets("Monday").Range("H4")).value,"mm-dd-yyyy) & ".xls"
On Error Resume Next

ActiveWorkbook.SaveAs Filename:=DestFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
If Err <> 0 Then
MsgBox " DID NOT SAVE WORKBOOK. " _
& Chr(10) & "" _
& Chr(10) & " Possible causes," _
& Chr(10) & "" _
& Chr(10) & "- Action cancelled by you." _
& Chr(10) & "- No filename was specified." _
& Chr(10) & "- The destination directory does not exist. Create it and try again." _
Exit Sub
End If
On Error GoTo lineabort

MsgBox "Workbook succesfully saved as " & DestFile
Exit Sub
lineabort:
Exit Sub

End Sub[/VBA]

[tstav]

Hi canselmi,
I've done various changes to your code.
[vba]Private Sub SaveWorkbookAs_Click()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

DestFile = InputBox _
("Enter the full path and file name to save." _
& Chr(10) & "Example- C:\Documents and Settings\User\My Documents\Technician Activity Log Week of 03-04-2008.xls (valid path)" _
& Chr(10) & "Make sure to tag the filename with the 'XLS' extention," & vbCrLf & _
"so you continue working on it at a later date." _
& Chr(10) & "Then Click on OK to save the workbook.", _
"Technician Activity Log - SAVE WORKBOOK AS", _
"C:\Documents and Settings\(User)\My Documents\Technician Activity Log Week of " & _
Format(Sheets("Monday").Range("H4").Value, "mm-dd-yyyy") & ".xls")

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=DestFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

If Err <> 0 Then
MsgBox " DID NOT SAVE WORKBOOK. " _
& Chr(10) & "" _
& Chr(10) & " Possible causes," _
& Chr(10) & "" _
& Chr(10) & "- Action cancelled by you." _
& Chr(10) & "- No filename was specified." _
& Chr(10) & "- The destination directory does not exist. Create it and try again."
Exit Sub
Else
MsgBox "Workbook succesfully saved as " & DestFile
End If
End Sub[/vba]

Oh, Simon had already answered before my post. Maybe I'm giving the same answer...

[Simon Lloyd]

tstav you did but you tidied the code up and i just noticed a typo in the addition to the code i made:
[VBA]
......My Documents\Technician Activity Log Week of " & (format(Sheets("Monday").Range("H4")).value,"mm-dd-yyyy) & ".xls"
[/VBA]should read:
[VBA]
......My Documents\Technician Activity Log Week of " & format(Sheets("Monday").Range("H4")).value,"mm-dd-yyyy") & ".xls"
[/VBA]i had a "(" where i didn't need one and was missing a ", its 'cos i haven't woke up yet!

[canselmi]

Can you tell me how to set the path to include the username because (User) will not work and I've tried %User% as well. Is it %Username%?

See below

[VBA]"C:\Documents and Settings\(User)\My Documents\Technician Activity Log Week of " & _
Format(Sheets("Monday").Range("H4").Value, "mm-dd-yyyy") & ".xls") [/VBA]

[tstav]

Can you tell me how to set the path to include the username

Here you are:

[vba]"C:\Documents and Settings\" & Environ.("Username") & "\My Documents\Technician Activity Log Week of " & _
Format(Sheets("Monday").Range("H4").Value, "mm-dd-yyyy") & ".xls") [/vba]

[Bob Phillips]

[vba]

CreateObject("WScript.Shell").Specialfolders(16) & "\Technician Activity Log Week of " & _
Format(Sheets("Monday").Range("H4").Value, "mm-dd-yyyy") & ".xls")
[/vba]

[canselmi]

XLD, thank you again and to everyone else that helped. The last code worked perfect. You are still "the bomb" in my books.