Comments:
You are using VBA’s Ontime method (timer) which can run a macro (Macro X) at a certain time. In your case the macro causes a run time error because it is trying to access a workbook which is not open. It runs without a problem if the workbook is open.
To resolve this problem you will need to add some macro code which will determine if the workbook is open. If it is not open, you will need open it. You would put this code in a new macro which will be called by your timer. If it is open, then you can run Macro X. If it is not open then you will need to open the workbook and then run Macro X.
Below is a VBA function which goes through all the open workbooks to see if a specific workbook is open.
Function bCheckIfOpen(sName As String) As Boolean
Dim wkb As Workbook
For Each wkb In Application.Workbooks
If wkb.Name = sName Then
bCheckIfOpen = True
Exit Function
Else
bCheckIfOpen = False
End If
Next wkb
End Function
You would then use this function in an IF statement in the macro being called by OnTime.
If bCheckIfOpen(WkBookName) Then
Run Macro X.
Else
Workbooks.Open Filename:= WorkbookName
Run Macro X.
End If
I hope this information helps.
|
