question

Struggling avatar image
1 Vote"
Struggling asked LauroColasanti-5787 commented

Get Excel File local absolute path instead of OneDrive Url

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!

dotnet-visual-basicoffice-excel-itprooffice-onedrive-client-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

VikiJi-MSFT avatar image
1 Vote"
VikiJi-MSFT answered LauroColasanti-5787 commented

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.

150360-image.png

150249-image.png

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.


image.png (10.2 KiB)
image.png (9.8 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

Hi @VikiJi-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!

0 Votes 0 ·

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.

0 Votes 0 ·

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


0 Votes 0 ·

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!

0 Votes 0 ·

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://docs.microsoft.com/en-us/answers/questions/864837/run-time-2737-when-connecting-ole-object-word-file.html

Did you find any solution?
Thanks, Lauro


0 Votes 0 ·