Online Path returned rather than local path

Question

Hi,

After updating my Office yesterday, Excel now returns the online Path rather than the local path.

I have an Excel file which is saved in a One Drive folder, which is synced to my local PC. It used to be that when I used "ThisworkBook.path" in VBA, I got the local path of the file. After the update, I get the Online SharePoint path.

The same happens if I insert the following into a cell:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

Within VBA, I use this path to open other files from the same location. I don't want to hardcode the path, since the macro should also be run on other PC's. Here the OneDrive folder could easily have a different location.

Why has this been changed and is there any way to get the local path?

Alternatively, is there any way to use the online path when opening files via VBA? Here I also have situations where I first need to check if the file exists at all (e.g. using "Len(Dir(tFullFileName))").

Br/ Espen

Answer 1

Hi Espen,

Thanks for using Microsoft products and posting in the community, I'm glad to offer help.

According to your description, you can open the OneDrive app settings > go to the Office tab > untick "Use Office applications tosync Office files that I open", then reopen your workbook.

Based on my test, it will make =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1) return a local path instead of a URL.

However, since VBA is beyond our support scope, I'm not sure if it will affect "ThisworkBook.path" in VBA, it would be great if it works in VBA. But if it doesn't work, to avoid providing any incorrect information, you can post a new thread in Excel / Microsoft OfficeProgramming / Microsoft 365 Family, this is the category dedicated to solving the related issues, and there will be professional can further assist you.

Besides, you can vote for the following idea in UserVoice to solve the problem from the root, it is the best way to let the relevant team hear your needs, anyone's suggestion could have a chance to be adopted by Microsoft, your understanding and contribution will be highly appreciated: Add option to avoid OneDrive from changing Excel references to other files

Hope the above information can be helpful.

Best Regards,

Arck

Answer 2

Hi Espen,

I’m writing this reply to follow up this thread, may I know if you have checked my last reply? When you have time, you are welcome to come back and let us know if you still need help here.

Hope you are all well during this period, stay safe and have a nice day : )

Best Regards,

Arck

Answer 3

Hi, 

I had the exact same problem after an update and this solution fixed my problem. Seems like a very impactful change that will affect almost every user in every organization! Every teammate of mine will now be forced to manually change their settings. 

Regards,

Isaac

Answer 4

Hi Arck,

Thank you very much for your quick replay.

One question:

If I change this OneDrive setting, wouldn't that impact how my files are synchronized? Can I still trust that my files will be synchronized to SharePoint immediately, whenever I save the file (in for example Excel)?

And then there is the issue that I would have to ask each user to change this setting as well.

I agree with Isaac that this is a quite significant change. Hence I also had a look at the "change request" you suggested, but I am not sure that covers this issue.

Best Regards,

Espen

Answer 5

Hi Espen,

Disabling "UseOffice applications to sync Office files that I open" will make the file stored in OneDrive sync folder opened as a local file, resulting co-authoring cannot work properly because Office will no longer be able to automatically merge changes from different versions of documents.

But after editing and saving the files, OneDrive will still be able to sync the changes you made. For more details, please refer to Use Office applications to sync Office files that I open

Best Regards,

Arck