Workaround for updating Files in One drive if open on another PC

Hi All

I have 2 files in a one drive folder...Main & Sub

One person is continuously working on Main on PC1.

On opening Sub by another person on PC2, Sub Opens Main takes info from Main and opens a listbox with that info...
When clicking on the row in listbox, info appears in textbox.
Textbox gets edited and processed...Code updates Main and Sub closes and saves both.
Problem now is Main if it was closed would be updated but because it was open...when it finally closes, another version of Main is created.

Is there any way of overcoming this...I tried setting Main as a shared file but somehow does not allow such a setting as the file contains Tables.

How can I get the main to be updated as my code runs from Sub???
Is there a way for real time updates??
I am in a predicament and do not know how to overcome this...
Is there a way of checking if the file is open in one drive on this other pc???

3 hours in and I still can't find a solution...Anyone out there perhaps solved this prior...

CODE TO TEST IF FILE IS OPEN AND THEN LOOP UNTIL AVAILABLE OR A MESSAGE PERHAPS...

How bout if I put these files on network drive and add code to first test if main is open...Will that solve my problem???

This is what I would normally make use of if the files were on one pc...Is there a way to check if file is open in one drive by changing path???

Option Explicit
Public wb1 As Workbook, wb2 As Workbook, wb3 As Workbook

Sub Auto_Open()
Set wb1 = ThisWorkbook
If IsFileOpen(ThisWorkbook.Path & "\" & "Overberg Solution.xlsm") Then
    MsgBox "FILE IS IN USE", vbInformation, ""
    Call Close_Me
    Exit sub
Else
    Application.Workbooks.Open (ThisWorkbook.Path & "\" & "Overberg Solution.xlsm")
End If
End Sub

Sub Close_Me()
ThisWorkbook.Save
Application.Quit
Application.ActiveWindow.Close savechanges:=False
ActiveWorkbook.Close savechanges:=True
End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
    Case 0
    IsFileOpen = False
    Case 70
    IsFileOpen = True
    Case Else
    Error errnum
End Select
End Function

Ok chalking this one up to as unsolved...
Excel has limitations lol...

As it is unsolved, I will remove the solved tag. You may get a response at some point.

Presumably, you have investigated the share options in OneDrive? If it's corporate, then your OneDrive may be Sharepoint, in which case collaborative working should be enabled. Not sure how this affects VBA, though.

Alrighty then AliGW...

Here's to hoping lol...

Edit...

If it's corporate, then your OneDrive may be Sharepoint,

Have not looked into this option...Tx will do some research.

Guys...I think ultimately I just need to have code to check if the specific file is is open directly in OneDrive
Is there a way to check if file is open in one drive by changing path???
What could the path possibly be...