Hi Nesha,

Why don't you create 3 separate files that get the data from the master file? This would give each user their own view of the data and they can still refresh the query to get the latest info. Note: the master file must be closed for the query to be able to refresh.

Mynda

Hi Mynda,

Thank you for your reply. Well, I've done what you've suggested, I created 3 separate files and they are each connected by a query to the master file.

In order to share these 3 files to separate sectors within my team, and since we are all on Microsoft 365 platform, I had to upload all files on my OneDrive.

When creating a Share link I have another problem now, and that is that files won't refresh when opened in Excel web browser, but they will refresh when opened in Excel app.
See enclosed print screens of messages that I get when opening in web browser. And yes, master file is closed.

So, do you know, can you help me how to make a share link that those files will open in Excel app and not on web?

Nesha

Hello,

Without a file to check it is difficult to give any help. Some questions though that might help us to pinpoint where the issue lies.

What is the connection path in the PP query to the master file in the other three files? It needs to point to the file location in OneDrive.
Once you have opened any file from OneDrive in Excel app you can pin it, Have you tried that?

Br,
Anders

Hi Nesha,

If you are all running Microsoft 365 (presumably you are all part of the same business tenant?) then you should set up the OneDrive app on your PC's and sync files that way.  You will then be able to access the files via Windows Explorer and open the files directly in Excel on your PC's.

Sync Files in OneDrive in Windows

Regards

Phil

Hi Phil,

Yes, my team including me already have installed and synced OneDrive, managed by IT.

I understand how to open in both cases, but members of my team are not power users, so when I share Excel files, they get generated link to their Outlook Inbox that when activated (clicked on), it opens web version.

My aim is to send them a link that opens in Excel app so they don't get stuck with messages I've sent previously. So, when I click on Share in Excel app, upper right corner, it automatically generates web link.

That is that I still don't know how to create (link)...and I don't see that what you propose gives that solution. Or am I wrong?

Thanks for your time and effort!

BR
Nesha 

Hi Anders,

Also, thank you for your time and effort.

The path in Query is:

= Excel.Workbook(File.Contents("C:Usersmy name.my surnameOneDrive - my team namemy foldermy file.xlsx"), null, true)

It looks fine and it opens in (my) Excel app just fine. That should be for other users from my team (still didn't share to anyone).

So, I need a (generated) link that when used by users (from my team) will automatically open in Excel app.

That doesn't happen when you share from opened Excel file, upper right corner, Excel generates web link.

BR
Nesha

Hi Nesha,

What I was saying is that if you are all using OneDrive then you can access the shared file(s) directly from your PC either using Windows Explorer or from within Excel.

If your file is located at C:Usersmy name.my surnameOneDrive - my team namemy folder then browse to that folder and open the file. Your user's should be able to do this too.  No need to generate sharing links and send them via email.

If you want to make it really easy for users, open the file in Excel then pin it to the list you see in the Open file screen. Or pin the Excel file to the Taskbar, or create a shortcut on the user's Desktop.

regards

Phil

Hello Nesha,

As long the users that are to work with any of the three separate files have the master file in same path it will work just fine, if not then the query update will fail, as shown in 2.png in your previous post. Below is an example of a link (it is broken as I don’t intend to share) to a file in my OneDrive, you should have similar as the path to your master file If you intend to have it in OneDrive.

https://1drv.ms/u/s!AnqhyFgmH6Fngc0yy5kep

You should be able to get good advice from the company IT department (I assume you have one) about how to go about. Another option is to use SharePoint or the old fashioned shared network folder.

Br,
Anders

Hi Anders,

All files including the master file are in the same folder on my OneDrive.

I will go with what Phil suggested, I will share link that is not auto generated but the one that I will copy/paste from Win/Explorer or Excel/Info-right click option and with little modification.

Thanks anyway!

BR
Nesha

Hi Phil,

So I cannot count on auto generated link since its made to be link to a web version, hence I just copy-pasted from Excel menu. It looks like either I just can't find a way or people at 365 didn't intended that way.

I had to do little modifications, my link is

https://aigo-my.sharepoint.com/personal/........../Documents/....../FILE.xlsx? web=1

But I had to get rid off "?web=1" part.

Also, I have to make email (to my team) myself and input this link. To get this link (for each file) instead of directly going from the Excel file to Share and chose people (since it would auto generate me the unwanted web link), in my web browser I have to not open the file, but click on OneDrive folder/File - Sharing and when on the right side Manage Access options opens, I have to choose Direct Access instead of Links giving access.

Hopefully at the end (in few days) will give the result I was looking for.

Thanks!

BR
Nesha

Hi Nesha,

I'm still not sure you are accessing the file(s) as I mean.

With the OneDrive app installed on your PC you will be able to access files on OneDrive from Windows Explorer.

So if you open Windows Explorer you can navigate to the OneDrive folder which you said is something like 

C:Usersmy name.my surnameOneDrive - my team namemy folder

On your team's PC's they will be able to do the same.

You do not need to use your browser and go into OneDrive online.

There's no need to create sharing links, the files are already shared/sync'ed (via the OneDrive app) and available directly on people's PC's.

You just need to make sure that the files are in a folder that is accessible to everyone that needs to use them.  But as you are all using corporate OneDrive/365 this should just be a routine setup that your IT people can do.

Regards

Phil

Hi Phil,

I'm not sure does everyone in my team have OneDrive app, but they all have it as a web, and you did help me, well, you gave me an idea.

I intend to use your guidance, in a way... I will use my WinExplorer, create shortcuts for each file and use these links into my email for my team.

I'm pretty sure this will work, but yes, I do have to test it first, which I'm still not in position to do, hence mine "in few days".

BR
Nesha

Hi Nesha,

If you are all using MS 365 but not using OneDrive to sync files to the PC then you are not using the system to its full potential.  It would make this situation a lot easier for you.

Cheers

Phil

Hi Phil,

But I am using MS365 and OneDrive. Files are there and all I did is opened OneDrive in Windows Explorer, created shortcuts (on my desktop) for each file, opened each properties, copied links and deleted "?web=1" from the end of each link.

And it works fine, users got emails with these links and it opened Excel app from each link.

Only thing is that I had to go this way, instead to use MS generated link, since it opens Excel web which won't do the job for my team.

Anyway, you triggered this idea in your post that I marked as useful answer.

Thanks again for your time, patience and effort, Appreciate it.

BR
Nesha