might not be bad idea to check for error codes. if you only test err=0 or
err < 0 your code might never terminate (if path is wrong or if file is
moved/deleted - err number = 53 and the file will never be available)

I don't see how your code terminates its loop. Line1 calls either Line2 or
Line3, which both call Line1.


You could use something like this (which uses previous function posted). If
you just want to avoid specific error codes you could modify the Select Case
Statement to just use Case 0 and Case Else.

Sub test()
Const Name As String = "C:\temp\abccompany#1.xls"

Do While IsFileOpen(Name)
Application.Wait Now + TimeValue("00:00:30")
Loop

Workbooks.Open (Name)

End Sub


Or you could try a Do/until loop that just tries to open the file. You
could include a counter to terminate the loop after x number of tries. I've
not tested it, but something like the following:

count = 0
Do
err = 0
count = count + 1
Workbooks.Open("Y.CSV")
if err < 0 then application.wait now+timevalue("00:00:10")
until err = 0 or count = 10




"Matthew McManus" wrote:


Thanks for that JMB.

I have been thinking about a simple way of accomplishing what I want
without getting too far into error numbers and so on. I thought of the
following. Can anyone see any potential problems with the it?
(To re-state: File, x.xls periodically opens y.csv and copies a value
from there. If y.csv is being written to when x.xls tries to access it
(and so is read only), I would like it to try again later or when y.csv
is available. The timing is nor particularly important.)

Line1:
On Error GoTo Line2
Workbooks.Open Filename:= "C:\y.csv"
Range("A1").Select
Selection.Copy
Windows("x.xls").Activate
Range("B1").Select
Activesheet.Paste
Windows("y.csv").Activate
ActiveWorkbook.Close
GoTo Line3
Line2:
Application.Wait Now + TimeValue("00:00:10")
On Error GoTo 0
GoTo Line1
Line3:
Application.Wait Now + TimeValue("00:00:30")
GoTo Line1

Thanks
Mat


--
Matthew McManus
------------------------------------------------------------------------
Matthew McManus's Profile: http://www.excelforum.com/member.php...fo&userid=5833
View this thread: http://www.excelforum.com/showthread...hreadid=375127