How to use Excel Power Query to red files in Dropbox Plus - 2TB plan?

[longstreath]

I would like to use my dropbox storage to share excel files which are accessed by power query.

I have a folder with files several excel files in it, and one excel file that has power query that should draw information from the tables in the other files.

I can do this when the files are on my computer and reference the files, but if I share them with others the file path is incorrect as it does not point to the dropbox folder.

I have tried to use the sharing link, but this does not work and access is denied.

Can anyone help with this?

[Daphne]

Hey there @longstreath, thanks for reaching out to us here!

This would indeed be a little tricky as the linked file path would be changed for different devices. 

Perhaps this post will be of some help? Using a slightly different way to link the other files might help with opening the files on another device.

Please just bear in mind that previewing the files through Dropbox though wouldn't be a viable option for this though, the files would need to be downloaded locally.

I hope this helps, let me know if you have any questions!

[longstreath]

I dont think that would work.

In Excel under Data you now have the option to get data from the web, but you need to have a URL to get to the file. For example you could get data from a URL path such as the following stored file:

https/file-examples-com.github.io/uploads/2017/02/file_example_XLS_5000.xls

If I use Excel's Data > Get Data > From Web, and use the above path, Excel will allow me to load and transform the data.

The URL is hard coded into the query source as: 

= Excel.Workbook(Web.Contents("https://file-examples-com.github.io/uploads/2017/02/file_example_XLS_5000.xls"), null, true)

What I want to do is be able to get data from files I store in Dropbox so that when the query file looks for the source it can find the URL provided. 

I cannot seem to get this to happen with Dropbox stored files.

[Rich]

---

@longstreath wrote:

In Excel under Data you now have the option to get data from the web, but you need to have a URL to get to the file.

---

The only way to get a URL that points directly to a file is through a shared link, and then modifying the URL to replace ?dl=0 with ?raw=1. Normally, such a link would leave it up to the browser to determine how to handle the file. Dropbox just passes the raw file, but there's still a redirect involved that changes the URL. I don't think Excel will be able to get passed the redirect.

[longstreath]

Thanks Rich,

I will give that a try tomorrow, and see if it helps. I am starting to face the fact that this may not be viable which would be a real shame as remote file access by applications becomes more common.