Cross-country data compare and update

Hello all.

I have a time lag issue due to the distance between the user and the server where the workbook is located. I would like to code a single workbook to be self-aware of its location and access itself at other locations for updates.

Basically, I need to have at least 2 copies of the workbook saved on servers thousands of miles apart. When Workbook1 on server A opens, it should check to see where it is located, then access Workbook1 on server B, which may be open or closed. It should copy the contents of the remote Sheet1 to a temporary sheet, compare the contents of the temporary sheet to the local Sheet1, and make changes/additions as necessary to local Sheet1. I don't want to delete anything from the local Sheet1, just change/add.

Since I occasionally update the code of the workbook, I only want to update one file which I can save to 2 servers, thus the reason to be self-aware of its location.

I hope this is possible. Waiting 10 minutes or more to open and later save this file seems excessive for a 1.7 MB file.

Thanks,
Andrew

Thanks mrice. Not sure exactly what to do with this but I am trying

    If ThisWorkbook.Path = "\\WestcoastServer\Folder1\Folder2" Then
        Application.Workbooks.Open ("\\EastcoastServer\Folder1\Folder2\Folder3\Folder4\FileName1.xls")
        file_name = ActiveWorkbook.Name
       
MsgBox ThisWorkbook.Path

End If

The MsgBox is just to let me know when the code reaches that point, but I am having trouble opening 2 files with the same name. I only need to copy Sheet1 on the remote workbook. Is there a way to copy remote Sheet1 to the Local workbook to replace Local Sheet1? If not, how would I change the name of the remote workbook as it is being opened so there isn't a name conflict with the local workbook name? Would also need to check the time stamp to ensure only the latest saved workbook is used to copy Sheet1 from. I don't want to copy an older Sheet1 to a workbook with a newer Sheet1.

Thanks,
Andrew