canselmi
03-10-2008, 10:09 PM
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
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