Auto update of time on a excel file on network

[nigamalok]

Hello All,


I have a excel file on the network which acts as parent to multiple child files. I want the current time to be pushed by the parent file to all child files.


I am using NOW() function in the parent file in one of the cells (say "A1"). Now A1 is being referenced in all child files so that all files get the same value and there are no inconsistencies.


The problem is NOW() function gets evaluated only when the file is opened and not everytime A1 is referenced by the child files. I am thus not getting the actual time but the time stamp when the master file was opened last.


Any help is welcome!


Regards,

Alok

 

[Luke M]

Let's rethink about what the actual goal is. IF you want to know the last time your main data was updated, then your current situation is correct. If you want to know the last time the child file was updated, you'll need to have the NOW function in the child workbook.

If your goal is not one of the two above, can you explain?

 

[nigamalok]

Hello Luke,


Thanks for your response!


My purpose is not one of the above. I want to know if the child file was able to connect to the network or not. Two possibilities arise:

a. Offline: In this case child file will not be able to connect and I want to show the user last date when data was fetched. So file will have date when it last connected.

b. Online: In this case child file will connect to parent and would like to show current time as pushed by parent file (I am expecting NOW() to do this for me)


Hope this clarifies!

 

[Luke M]

Yes, that helps. In that case, you could put something like this in the ThisWorkbook module of the child workbooks.

[pre]

Private Sub Workbook_Open()
Const MyFile = "N:Cool StuffAwesome File.xlsx"
Dim x As String

x = Dir(MyFile)
If x <> "" Then Worksheets("Sheet1").Range("A1") = Now
End Sub

[/pre]

Checks if it can find a workbook. If it can, it would return workbook name. If it can't, x will be blank.