Get Excel File local absolute path instead of OneDrive Url

Question

Get Excel File local absolute path instead of OneDrive Url

Struggling 26

Hi everyone!

I am having an issue with finding the local path of my Excel file on disk when synced with OneDrive.

Here is the context: I have an Excel file on my local machine that is also synced with OneDrive. When I try the formula CELL("filename") in Excel I get the https link returned instead of the local path (C:/.../).

I need to access the local path information from my Visual Basic code. What I have is a Microsoft.Office.Interop.Excel._Workbook object. Whenever I try to return Microsoft.Office.Interop.Excel._Workbook.Path I get the https link to OneDrive instead of the local path of my currently opened Workbook. Does there exist an API call to Excel that would return me the local path instead of the URL? I saw solutions involving writing some code to recover the local path from the URL but I do not want to do that to prevent any problems in the future. Or is there an alternative to recover the information I want?

Thank you for your help!

3 answers

Your answer

Answer 1

Cristian Buse 21

This is achievable. A detail answer here: https://stackoverflow.com/a/73577057/8488913

Darren Bartrup-Cook 0 Reputation points

2024-02-15T13:14:26.6233333+00:00

That was the link I was searching for! Bing's AI was hiding this result from me and kept pushing me towards some ENVIRON type answer that was so obvious it wouldn't work that it would take an AI to think otherwise.

Answer 2

Viki Ji_MSFT 4,471

Hi @Struggling ,
Welcome to Q&A forum!

This is because the files synced to OneDrive are online files that are synced in real time. According to my test, I suggest you click on the OneDrive icon and go to Help & Settings>Settings>Office to uncheck the "Use Office application to sync Office files that I open" option to check this issue.

However, since VBA is out of our support scope, I'm not sure if it will affect the "Microsoft.Office.Interop.Excel._Workbook.Path" in VBA. Thanks for your understanding. If you have any confusion or questions about VBA later, I suggest you go to the following page to seek more professional help.
Build powerful applications for Excel

Hope the information is helpful to you. Any updates, pelase let me know.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Viki Ji_MSFT 4,471 Reputation points

2021-11-22T08:56:33.837+00:00

Hi @Struggling ,
I am checking this thread, if you have a chance to review this thread, please check if my reply is helpful.
Thanks.
Struggling 26 Reputation points

2021-11-22T16:16:37.927+00:00

Hi @Viki Ji_MSFT ,

Thank you for your answer. The thing is that I want to do it through code and not manually. Is there a way to get the local path through code without disabling the sync to OneDrive? Or disabling the sync of a Workbook through code, getting the local path and resyncing?

Thanks!
Viorel 122.6K Reputation points

2021-11-22T17:23:05.23+00:00

Did you already try combining the value of Environ("OneDrive") and the corresponding portion of URL?
Struggling 26 Reputation points

2021-11-22T20:13:45.58+00:00

Yeah the reconstruction is not correct. I have a question. When doing CELL("filename") in excel synced with OneDrive I get an https link such as "https/.../.../Documents/Desktop/filename" for a file on my Desktop for example. Where does that "Documents" in the url path comes from? My Desktop is NOT in another folder called "Documents". It seems to always be there. I just want to confirm that this will always be present when returning the https link?

Thanks!
Viki Ji_MSFT 4,471 Reputation points

2021-11-23T01:50:26.557+00:00

Hi @Struggling ,
Thanks for your reply.
Since we are focused on general issues about Office app desktop, to better help you, please refer to my first reply to seek more support and help about the code.
Thanks for your understanding.
Lauro Colasanti 121 Reputation points

2022-05-28T16:22:46.113+00:00

Hi @Struggling ,
sorry for the intrusion.
I'm having a similar problem with interop between Word and Access as you can see in my post:
https://learn.microsoft.com/en-us/answers/questions/864837/run-time-2737-when-connecting-ole-object-word-file.html

Did you find any solution?
Thanks, Lauro
bart lefevere 0 Reputation points

2023-03-07T00:39:36.0833333+00:00

Meanwhile 3/2023 in Office 365 the windows have changed but your solution worked perfectly for me!

It is a pity that the setting cannot be enforced in a simple way via VBA , because if you give a file to someone, you cannot know which settings that person uses on his local PC. You must therefore permanently inform someone what to look out for before using the file. Is there a VBA solution in the meantime? I have read the solution on "Stackoverflow" but it is not a solution that changes the setting through VBA.
Thank you in advance for your reply.
Witt-Dörring Guido 0 Reputation points

2023-03-10T01:50:15.0066667+00:00

Hi @bart lefevere ,

Yes, the solution on StackOverflow linked by Cristian Buse does not change the setting, it's even better: it allows you to get the local path without changing the setting at all.
Jesus A 5 Reputation points

2023-08-31T16:23:16.7333333+00:00

Now that MS decided that this option is deactivated and we can only Work with Sharepoint path, is there anyway to make excel, Powerquery and visual basic to give the absolute path and not the URL?

This is driving me totally insane.
Vincenz Mössenböck 0 Reputation points

2023-10-17T08:07:58.97+00:00

Is there any way, to get the absolute path of a file in a OneDrive folder without using VBA? I mean WTF.

Answer 3

Excel VBA Diary 0

If you want a solution not only for OneDrive but also for OneDrive for Buisness, please refer to the following GitHub.
"Resolve the problem of ThisWorkbook.Path returning a URL in OneDrive"
https://github.com/Excel-VBA-Diary/GetThisWorkbookLocalPath

Share via

Get Excel File local absolute path instead of OneDrive Url

3 answers

Your answer