mcrackin
06-19-2013, 11:31 AM
Hi everyone.
I'm trying to create a SaveAs button in an excel file so that employee's can easily Save workorder documents from a template. The macro needs to have the following attributes:
1. Save the file as "cellvalue_date.xls" (ie. 123456_20130620.xls)
2. Save the file into organized directories by year and month name and create those dirs if they aren't already created (ie. /2013/Jun/123456_20130620.xls)
3. I want the user to be prompted with a customizable message to confirm the SaveAs, and when they click "Yes" (instead of "Cancel"), the document executes the whole SaveAs and restores the document to a blank sheet to start over. This is so people can easily create more than one work order.
Update: Here is my code so far, which I have attempted the first 2 steps and it is working perfectly. I've also coded a little prompt and tied the two together and everything works fine. Now I'm just looking to return to the original template so an employee can begin editing the next work order from the original state.
The code so far:
Sub Button1_Click()
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String
strPrompt = "Are you sure you want to save the file and clear the document?" 'message prompt question
strTitle = "Confirmation" 'title of prompt box
iRet = MsgBox(strPrompt, vbYesNo, strTitle)
If iRet = vbNo Then
MsgBox "Please make necessary changes." 'if user clicks no on prompt
Else
Dim newFile As String, fName As String
On Error Resume Next
fName = Sheets("Production Report").Range("G5").Value 'Change A1 to whatever cell you want to appear in the saved file name
newFile = fName & "_" & Format$(Date, "yyyymmdd") & ".xls" 'Change the date to whatever you want ie ddmmyyyy but leave the quotes
MkDir "C:\Users\McRackin\Desktop\Dave\" & Format(Date, "YYYY")
MkDir "C:\Users\McRackin\Desktop\Dave\" & Format(Date, "YYYY") & "\" & Format(Date, "MMM")
ChDir "C:\Users\McRackin\Desktop\Dave\" & Format(Date, "YYYY") & "\" & Format(Date, "MMM") 'edit the 3 directory names to whatever you use, original string must pre-exist
ActiveWorkbook.SaveAs Filename:=newFile
MsgBox "Your document has been saved. Continue with a new work order or exit to finish." 'if user clicks yes on prompt
End If
End Sub
I'm trying to create a SaveAs button in an excel file so that employee's can easily Save workorder documents from a template. The macro needs to have the following attributes:
1. Save the file as "cellvalue_date.xls" (ie. 123456_20130620.xls)
2. Save the file into organized directories by year and month name and create those dirs if they aren't already created (ie. /2013/Jun/123456_20130620.xls)
3. I want the user to be prompted with a customizable message to confirm the SaveAs, and when they click "Yes" (instead of "Cancel"), the document executes the whole SaveAs and restores the document to a blank sheet to start over. This is so people can easily create more than one work order.
Update: Here is my code so far, which I have attempted the first 2 steps and it is working perfectly. I've also coded a little prompt and tied the two together and everything works fine. Now I'm just looking to return to the original template so an employee can begin editing the next work order from the original state.
The code so far:
Sub Button1_Click()
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String
strPrompt = "Are you sure you want to save the file and clear the document?" 'message prompt question
strTitle = "Confirmation" 'title of prompt box
iRet = MsgBox(strPrompt, vbYesNo, strTitle)
If iRet = vbNo Then
MsgBox "Please make necessary changes." 'if user clicks no on prompt
Else
Dim newFile As String, fName As String
On Error Resume Next
fName = Sheets("Production Report").Range("G5").Value 'Change A1 to whatever cell you want to appear in the saved file name
newFile = fName & "_" & Format$(Date, "yyyymmdd") & ".xls" 'Change the date to whatever you want ie ddmmyyyy but leave the quotes
MkDir "C:\Users\McRackin\Desktop\Dave\" & Format(Date, "YYYY")
MkDir "C:\Users\McRackin\Desktop\Dave\" & Format(Date, "YYYY") & "\" & Format(Date, "MMM")
ChDir "C:\Users\McRackin\Desktop\Dave\" & Format(Date, "YYYY") & "\" & Format(Date, "MMM") 'edit the 3 directory names to whatever you use, original string must pre-exist
ActiveWorkbook.SaveAs Filename:=newFile
MsgBox "Your document has been saved. Continue with a new work order or exit to finish." 'if user clicks yes on prompt
End If
End Sub