Preventing error messages

[Matthew McManus]

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

[JMB]

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
>
>

[Matthew McManus]

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

[JMB]

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
>
>