I have some code in "x.xls" which runs periodically to open a csv file
and copy the values there to x.xls. Occasionally another program will
have the csv file open writing new data before saving and closing it.

If my code attempts to open the csv file at this time, I get the error
message, "Read only, ....". Is it possible to write something into my
code which will cause it to wait until the csv file is available and
then open it and do its stuff, rather than stopping the code and giving
me an error message?

Thanks for any help

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

Bob Phillips posted this function under Programming on 5/27/05. Maybe you
could set up a Do/While Loop to call his function until IsFileOpen = false.

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err.Number
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function


Sub test()
If Not IsFileOpen("C:\MyTest\test-1.xls") Then
Workbooks.Open "C:\MyTest\test-1.xls"
End If
End Sub

--
HTH

Bob Phillips



"Matthew McManus" wrote:


I have some code in "x.xls" which runs periodically to open a csv file
and copy the values there to x.xls. Occasionally another program will
have the csv file open writing new data before saving and closing it.

If my code attempts to open the csv file at this time, I get the error
message, "Read only, ....". Is it possible to write something into my
code which will cause it to wait until the csv file is available and
then open it and do its stuff, rather than stopping the code and giving
me an error message?

Thanks for any help

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

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

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