Please help with Restaurant Paperwork

[rpmonarch]

I have recently taken over a small restaurant and am in desperate need of updation procedures. We currently have all of our paperwork in single files for the date and in a folder for the month. I have about 5 separate reports or files that we use. 3 of them are weekly while the other two are for each day. I have created an excel file that has a copy of all five reports on them. i then created a button that copies the 3 weekly reports to a separate workbook. It also take the first tab copies it into a new workbook and makes a new sheet for each day monday thru sunday and copies sheet 1 to all of them. And the same for the next report. My problem is that I need very little user interaction because my staff is bad with computers and replace my files all the time instead of saving them as new files. If anybody could help me I would appreciate it. What I am looking to do is Open a file every monday morning that asks for the date. It then makes my three new files with the right date and then saves them to the current directory. Trick there is that I would also like it to create a folder for the month, and another for the day, and then save all three files in that folder. That way I have all my reports for that day together in a folder for that day.

PLEEEEESSSEEEE HELP!!!!! My restaurant needs this.

 

[PA HS Teacher]

Just thinking out loud here, but you could have an openworkbook event that

1) Checks the current date
2) Checks the current day of the week
3) Checks to see if you have the file you need for this week set up yet.
4) If those files do not exist yet, create them.


5) You might have also make the workbook resave itself with a different name every time the file is saved. This way you have a backup copy of the file for every time it was saved.

The file names could be sequential, or have the date and time in the file name etc. Don't rely on the users to name the files at all. Don't have the users.

If you are really paranoid, you could have code that backs up all of your data files periodically.


I'm sure others will follow with other suggestions.

 

[al_b_cnu]

Hi,

Carrying on from PA HS Teacher,

This link shows how to create a directory:
http://www.mrexcel.com/board2/viewtopic.php?t=181306&highlight=create+directory

This code will prompt for the date (defaults to current date)

Sub xxx()
Dim sPrompt As String
Dim vDate As Variant

sPrompt = "Please enter Run Date & press 'OK'"
Do
    vDate = Application.InputBox(prompt:=sPrompt, _
                                Title:="Please confirm run Date", _
                                Default:=Format(Date, "dd-mmm-yy"))
    If vDate = False Then
        MsgBox "Abandoned"
        Exit Sub
    End If
    sPrompt = "Invalid date entered"
Loop While IsDate(vDate) = False

If Weekday(vDate) = vbMonday Then
    MsgBox "Its Friday!"
Else
    MsgBox vDate & " isnt a Monday"
End If

End Sub

You might want to use this to actually save the workbook:
ThisWorkbook.SaveCopyAs filename

 

[rpmonarch]

Thanks al_b_cnu,

I copied the code directly in. I created a button and put the code under the button seeing as how I do not know how to write code for a workbook without putting it in a button. I don't understand how to make it run otherwise. When I copied the code directly in, I got some errors. It may be because of my version. I am using Excel: Mac. I don't know if this is the problem. Especially because when I finish this, I am putting it onto my restaurant PC. I get an error and it Highlights the header Sub xxx(). Then I get another error saying expection end of statement with the Do line. I don't know what to do. I would like to try and run this without a button, but that comes later.

 

[al_b_cnu]

Hi,

Never used MAC, but presumably your command button code looks like this:

Private Sub CommandButton1_Click()
Dim sPrompt As String
Dim vDate As Variant

sPrompt = "Please enter Run Date & press 'OK'"
Do
    vDate = Application.InputBox(prompt:=sPrompt, _
                                Title:="Please confirm run Date", _
                                Default:=Format(Date, "dd-mmm-yy"))
    If vDate = False Then
        MsgBox "Abandoned"
        Exit Sub
    End If
    sPrompt = "Invalid date entered"
Loop While IsDate(vDate) = False

If Weekday(vDate) = vbMonday Then
    MsgBox "Its Friday!"
Else
    MsgBox vDate & " isnt a Monday"
End If

End Sub

NMote that the 'Sub xxx()' has been replaced with 'Private Sub CommandButton1_Click()'

 

[al_b_cnu]

Thinking about your reqiurement a little more, whilst a command button is ok as a starter & for testing, perhaps you might want to head towards a user form, opened at Workbook open.

I envisage this userform requesting a username / password. The code will then present a menu, based on the username, showing / hiding each of the required options.

You might want to consider having your data in a separate workbook - this makes the supporting code far easier to maintain, also you're not limited to just the current data.

 

[rpmonarch]

Thanks,

Sorry it has been so long.

I have been working on this. I have my master file creating the directory for the month...now I need a directory for the week...(any thoughts on creating a directory for a week?) (Week1...and so forth for the month..correctly)

I also have a sheet within my file being saved as a separate file when closed which is great...except the macro goes with it. That causes lots of problems. Any help would be appreciated. You guys have been great so far and have helped a lot.

Thanks

 

[al_b_cnu]

Hi rpmonarch,

without further details, can only give generalities.

The WEEKNUM returns the weeknumber (between 1 and 53) and the DAY function returns the dayof the month, so that divided by 7 gives the week number of the month, dont know if you can use that.

Re saving a file & the macro goes with it, I've solved this in the past by using a 'master' workbook which holds NO data in itself (except admin typeinfo) and which opens other workbooks as appropriate, however with this approach,you have to use 1 or more userforms, effectively taking control out of the hands of the user & under the control of the VBA coder - that's you!