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

[Frankie_The_Flyer]

Hi!

The project progresses and I'm now able to produce worksheets from various data sources then name them from cell values and save them to a folder created in OneDrive (also created from cell values).
I now need to be able to open a specific worksheet in a specific folder from any computer in the country using cell values.
I'm able to achieve this by opening the worksheet from the OneDrive folder on my personal computer using

Please Login or Register  to view this content.

However, to be able to open the worksheet from any computer I think I'll need to go directly to OneDrive. I've replaced the Path1 opening text with the OneDrive address and the secret bit that gets into my particular OneDrive files;

Please Login or Register  to view this content.

but using the cell values to find the correct folder and worksheet doesn't seem to work as the code ends up opening a blank, "Read Only" worksheet.

As I will need to be able to open one of many folders (and worksheets within them) from variable cell values I can't put the URL for the required folder into the Code. Putting a \ or / between the https address and cell references doesn't appear to do anything.

Any ideas please? ("We're" so close to achieving the overall project!! )

Frankie

[AliGW]

When you say you want to open the file from any computer, what exactly do you mean? To be clear what I am asking here: don't you have OneDrive installed on the other PCs?

[Frankie_The_Flyer]

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

[AliGW]

I don't think you will be able to do it this way, I am afraid. When you share files in OneDrive, as you know, you have to provide a link to the user, and that link is usually sent via E-mail. The person that is going to be using the link needs in some way to be identified by OneDrive.

That aside, when using VBA the workbooks all need to reside on the computer that you are using, so from that point of voew alone, what you seem to be proposing, if I have understood correctly, is not going to be possible.

[Frankie_The_Flyer]

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.

Please Login or Register  to view this content.

[AliGW]

That would be the solution - to use local drives.

This might be of interest: https://stackoverflow.com/questions/...-with-onedrive

[Frankie_The_Flyer]

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!

[Frankie_The_Flyer]

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.

Please Login or Register  to view this content.

[AliGW]

Frankie - we really appreciate your coming back and sharing this - very useful and helpful. In fact, there have been one or two queries along these lines recently, so this will be very pertinent to many to come, I am sure.

Could you just change the thread title to this so that it is picked up by people in a similar situation: Opening an Excel worksheet from OneDrive on a Network (SharePoint)

Thanks again.

PS You might want to update your user profile, which seems now to be out of date in terms of your Excel version.

PPS I have changed the thread title for you.