macro to check if network file is being use if file is being used, then wait 10 seconds

hi, i need some help with a macro that can check for a file (external file "D:/formula/inventory.xlsm"). Sheet 'status' cell A1, contains a either 1 or 0, if A1 is 0, then proceed (and change this A1 to 1 (occupied), If A1 contains a '1' (available) , then wait 10 seconds and recheck.

PS.basically, i am trying to resolve network computers from fighting for the network file, i have bunch of other macros running in between this one.
If someone can help me or suggest better way to do this , thank you for reading my post and help me if you can please.

See if this works for your needs. Keep in mind you will need to edit the path ...

Please Login or Register  to view this content.

hi, thanks, it works, how do i put 10 seconds wait time and recheck if the value is 1 (1 being busy) ?

Option Explicit

'This is the macro you want to run first. It will automatically run your macro every 10 seconds and continue to do so until you run the sub manual_stop

Sub ScheduleclrCol()
TimeToRun = Now + TimeValue("00:00:10") '<----- adjust time to fire here HH:MM:SS
Application.OnTime TimeToRun, "CallingMainProcedure"
End Sub

Sub CallingMainProcedure()

'Calling GetValuesFromAClosedWorkbook procedure
'Specifying the file path, filename, sheet name and range of data
GetValuesFromAClosedWorkbook "C:\Users\jimga\Desktop", "LottoChecker(tora(v1)).xlsm", _
"Sheet1", "A1"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)

With ActiveSheet.Range(cellRange)
'Assigning the array formula to the specified range
.FormulaArray = "='" & fPath & "\[" & fName & "]" & sName & "'!" & cellRange
'Removing formula from the cell and pasting the values in the cell
.Value = .Value
If .Value = 0 Then
MsgBox "The value is ZERO"
'place your specific macro here
Else
MsgBox "The balue is ONE"
'place your specific macro here
End If
End With

ScheduleclrCol

End Sub

Sub manual_stop() 'button click for stop (Manual Stop)
On Error Resume Next
Application.OnTime TimeToRun, "CallingMainProcedure, , False"
End Sub