Opening an Excel worksheet from OneDrive on a Network (SharePoint)

Hi Aligw

OneDrive (lets call it Master One) will be a central depository for the Folder with worksheets in. These would be set up by someone (probably me) with a Master Excel workbook that sorts all the information for each worksheet and saves those sheets to the specific folder. Both Folder and worksheets are named from cells in the Master Workbook. (Parh1 & Rightfilename)

A second workbook (lest call that WB2) will then be available for certain association members to down load from anywhere in the country (World?). This workbook will give them the option of opening any of the worksheets from a specific folder in OneDrive, (on the click of a button), using the same Cell values / references used in the Master workbook.

There may or may not be OneDrive on the other computers although if there is it's likely to be linked to their own OneDrive rather than Master One. Instructions could be issued to get them connected to Master One first, but the association members will have varying "technical" ability so the best way for this to work would be to be able to link their computer to Master One (if it isn't already linked to it) and open the selected sheet (in desktop Excel) at the click of the button.

With potentially 5 active Folders containing up to 50 worksheet each in each, the WB2 issued to a member would only allow them to open the folder relevant to them (as dictated by the and Path1 ThisWorkbook.Sheets("Running Order").Range("F1").Value) and access the worksheets in that folder (as selected via the file reference at Range("BN1") & (".xlsx")).

Hope this isn't too longwinded and clarifies what I'm trying to achieve.

Look forward to any assistance out there!!

Cheers

Frankie

But if someone was connected to Master One on their computer they should be able to access the worksheets as they would sync to their OneDrive folder on their computer?
So if I sent an invite to link along with the link to WB2, WB2 would just need to hunt down their C:User\OneDrive folder and use that address. Is there any way excel VBA can hunt through a computer to find a folder? (Oops! Off subject question here, but it's all leading to a finial result!)

The worksheets are then residing on the users computer and can be edited and saved as they are by me using the original Code on my computer.

Path1 = "C:\Users\Frank\OneDrive\" & ThisWorkbook.Sheets("Running Order").Range("F1").Value                'F1 =  Meeting Number (ABC334) which is the name of the folder
    
    Rightfilename = Range("BN1") & (".xlsx")                  'BN1 = Right side Meeting Number and Team Name (ABC334 - Best Runners) which is the name of the worksheet

Thanks for the link AliGW. I think I've seen it before but under different circumstances.
I'll have to have a rethink about how to share files. OneDrive seems ok, but I really need people to have restricted access from more or less anywhere as easily as possible. The association I'm with has it's own website which may be a way to go, but I wanted the project to be as complete as possible before I introduced it to them.

Ho-Hum! Back to the drawing board!

Think I've cracked this one now.
Having sent the link to the people who need access to the data, this then uses their OneDrive folder to work in.

Sub Back_To_Left_Team_Name()

Dim Path1 As String

Dim Leftfilename As String

   
    Path1 = Environ("USERPROFILE") & ("\OneDrive\") & ThisWorkbook.Sheets("Running Order").Range("F1").Value   'F1 =  Meeting Number
    
    Leftfilename = Range("AL1") & (".xlsx") 'AL1 = Left Lane Meeting Number and Team Name


ActiveCell(1, 3).Select



On Error GoTo ErrMsg

'Open the team sheet from OneDrive

Workbooks.Open Path1 & "\" & Leftfilename
 
Exit Sub

ErrMsg:
MsgBox ("There doesn't appear to be a Team Sheet for this team."), , "Oops!!"

End Sub