Do I link or share
Posted by Melissa on November 21, 2000 12:16 PM
Timesheets - I made a master file with project codes and employees. I fill in their time. I want them to acess their own sheet on the network, fill in their time and then I can upload changes and hours on the master file once a week. How do I do this? Linking the sheets or sharing a database? I dont get it. HELP.
Posted by Ben O. on November 21, 2000 12:53 PM
I recommend having the employee time sheets linked to the master time sheet. If employees will be using the same file week after week, updating the links shouldn't be a problem. At my company, however, employees use a different workbook each pay period. Because of this the links constantly have to be changed. This would take our human resources person a long time, but I created macros that make it quick and easy for her to change the links.
By default, the master time sheet is linked to placeholder worksheets, one for each employee, that are blank. The macros change the links from the placeholders to the time sheets for any given pay period. Employees here have to save their time sheets in a specific directory and give it a specific file name, so that it'll be there when the macro looks for it. Here's what a typical macro looks like:
Sub Changelink()
Dim OldFile as String
Dim NewFile as String
Dim TimeSheetDate as String
TimeSheetDate = Activesheet.Range("Date")
OldFile = "W:\Placeholders\Ben_Osborne.xls"
NewFile = "W"\Users\Beno\TimeSheet\" & TimeSheetDate & "bro.xls"
ActiveWorkbook.Changelink Name:=PHLoc, NewName:=NewFile, Type:=xlExcelLinks
End Sub
I have one of these macros for each of our 30 employees. Microsoft's Word's mail merge feature made it easy. There's a summary page on the master time sheet with a list of everyone's name and a button next to it. Each button activates the macro that changes the link of the person it corresponds to.
So, that's how I did it at my company. I'd be happy to tell you more if you're interested.
-Ben
