Help with dynamic source folders

[Shiamissi]

Hi everyone!
This is my first post in the forum, though not the first time I look for help in here.

I have many queries implemented where they are sourced from workbooks saved in a network drive that we share between several people.

A recurrent issue with my queries is that, whenever someone else in another laptop tries to refresh my queries, they don't run because the query cannot find the source file(s). For example, the network drive in my laptop is mapped under the letter "Z" so if someone else in another laptop has the network drive mapped under a different letter, then the query obviously doesn't work and they would need to manually update the source and change the letter of the drive in the query

This is clearly a manual operation I would like to avoid happening. Is there anyway to make the network drive source dynamic, so that the query can find the files in any laptop without taking in account the letter of the network drive?

Many thanks in advance

 

[smozgur]

Every shared folder that you are using as a letter assigned drive (mapped network drive) on your Windows computer also has an actual network path (UNC path). This doesn't change on the same network and it could be used to access source files from any computer on the same network.
Sample UNC path usage to get the contents a workbook:

= Excel.Workbook(File.Contents("\\COMPUTER\Users\username\Documents\SharedFolderName\Book1.xlsx"), null, true)

The mapped network drive version:

= Excel.Workbook(File.Contents("Z:\Book1.xlsx"), null, true)

You can see the UNC path info in the file explorer following the drive letter of the mapped drive. Or you can start a command prompt (Start->Run->type cmd and Enter), then type "net use" and hit Enter. You'll see all mapped drives with UNC paths listed.

 

[Shiamissi]

Every shared folder that you are using as a letter assigned drive (mapped network drive) on your Windows computer also has an actual network path (UNC path). This doesn't change on the same network and it could be used to access source files from any computer on the same network.
Sample UNC path usage to get the contents a workbook:

= Excel.Workbook(File.Contents("\\COMPUTER\Users\username\Documents\SharedFolderName\Book1.xlsx"), null, true)

The mapped network drive version:

= Excel.Workbook(File.Contents("Z:\Book1.xlsx"), null, true)

You can see the UNC path info in the file explorer following the drive letter of the mapped drive. Or you can start a command prompt (Start->Run->type cmd and Enter), then type "net use" and hit Enter. You'll see all mapped drives with UNC paths listed.

Thank you so much!! This worked. I genuinely thought I had to take a harder route.

 

[smozgur]

Thank you so much!! This worked. I genuinely thought I had to take a harder route.

You’re welcome!

Please remember to mark the post that answered your question to help future readers. It is the little checkmark icon next to the post frame.