RUNTIME 1004

[JB]

Worksheet A is saving every 5 seconds. Worksheet B is refreshing links from
A every "x" seconds. When these events occur concurrently, I get a pop-up
asking that I select the source file (A) that was not found during the macro.
If I cancel out, it yields a 1004 error.

Any ideas of how to get around the "error"? (the error occurs in the top of
the code (filename))


ActiveWorkbook.UpdateLink Name:= _
"\\DTCNAS-ILSP002\Mandatory\Analysts - Working Files\Josh\Live Market
Securities.xls" _
, Type:=xlExcelLinks
Range("A1").Select

Call UpdateTime
End Sub


Sub UpdateTime()

Application.OnTime Now + TimeValue("00:00:01"), "UpdateLinks"
'On Error Resume Next


End Sub

Thanks in advance....

 

[Gary''s Student]

You need to dis-synchronize the events. It is easier to do this than test
for conflicts.

Run the save and refresh both at 10 second intervals, but offset them by 5
seconds.

 

[JB]

Thanks for the reply.

How do I go about the offset when the files are opened independently and the
save/refresh occur begin at different time intervals?

 

[George Nicholson]

Clunky, but...

On Error Resume Next ' Set Inline Error handling

Do
' Guaranteed to go though loop at least once.
' Will repeat loop only if error is encountered on previous pass.
Err.Clear 'Resets Err.Number to 0 (if it wasn't already)
ActiveWorkbook.UpdateLink Name:= _
"\\DTCNAS-ILSP002\Mandatory\Analysts - Working Files\Josh\Live Market
Securities.xls", Type:=xlExcelLinks
Do Events
Loop While Err.Number <> 0

On Error GoTo [??]

If you go this route, consider adding a "retry" counter and then displaying
an error message & exiting if it gets too high (ex: if the file gets
renamed, you'll be in an endless loop otherwise).

Also consider replacing "Do Events" or extending the idea to include a
specific duration with a Sleep/Wait routine like
http://www.mvps.org/access/api/api0021.htm

Observation: saving a file can take a while. Certainly longer than 5
seconds, especially on my network