Updatiing Link Macro -- Better at handling errors?
Posted by Ben on September 21, 2000 2:51 PM
In master time sheet I'm responsible for maintaining, there are links to dummy time sheets for all 30 of our employees. When the pay period is over, our Human Resources clerk runs macros that change the master time sheet links from the dummies to the actual employee time sheets. The actual time sheet names are based on the six-digit date code for the first day of the pay period (DatePrefix) followed by the employee's initials. The problem is, if the employee didn't save his/her time sheet in the correct directory with the correct file name, the macro fails and ceases to be linked to even the dummy spreadsheet. Because of this, the macros won't work a second time if we want to try again.
Does anyone have any suggestions for error-handling. If the employee's time sheet isn't in the right place, I'd like the link to default back to the dummy time sheet, and perhaps a dialog box to come up stating what happened. Here's an example of one of the macros:
Sub ChangeLink()
Set DatePrefix = ActiveSheet.Range("DatePrefix")
ActiveWorkbook.Changelink Name:="W:\MASTERS\Timesheet\Placeholders\dummy1.xls", _
NewName:="\\BSG1\SYS\USERS\Name\Time Sheet\" & DatePrefix & "int.xls", Type:= _
xlExcelLinks
End Sub
Thanks in advance