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

Posted by Ivan Moala on September 22, 2000 1:33 PM

Try this;

NB: not tested

Sub ChangeLink()

Set DatePrefix = ActiveSheet.Range("DatePrefix")

if Dir("\\BSG1\SYS\USERS\Name\Time Sheet\" & DatePrefix & "int.xls")= "" then goto ErrH

ActiveWorkbook.Changelink Name:="W:\MASTERS\Timesheet\Placeholders\dummy1.xls", _
NewName:="\\BSG1\SYS\USERS\Name\Time Sheet\" & DatePrefix & "int.xls", Type:= _
xlExcelLinks

ErrH:
Exit Sub
Msgbox "Link not set...file doesn't exist"

End Sub

HTH

Ivan



Posted by Ben on September 26, 2000 6:37 AM

Thanks Ivan, I'll try that (n/t)