2

I am trying to return the local C Drive directory of a locally sync'd Sharepoint Excel File using a C# Excel Add-in. Looking up the Excel.Application.ActiveWorkbook.FullName or .Path properties for an opened local Sharepoint file will return the Sharepoint URL (e.g. "https://xxx.sharepoint.com/sites/...") and not the Local C: Drive Directory string.

I have found existing solutions for OneDrive that use Environment Variables to parse out a Local Directory string (see "C# Version" comment in the following thread): https://social.msdn.microsoft.com/Forums/en-US/1331519b-1dd1-4aa0-8f4f-0453e1647f57/how-to-get-physical-path-instead-of-url-onedrive?forum=officegeneral

However, I'm hoping to find a tried and true solution to cover Sharepoint local file directories as well. The problem is, the OneDrive method does not work to produce the correct local directory string because the Sharepoint URL is quite disparate from the Local Directory string.

Comparison example:

  • Sharepoint URL returned by ActiveWorkbook.Path:

https://xxx.sharepoint.com/sites/Clients/Shared Documents/Region/Country/Client Name/Review

  • Actual Local File Directory:

C:\Users\MyUser\Sharepoint Parent Folder Name\Clients - Client Name\Review

You can see that much of the Sharepoint URL is left out of the actual Local File Directory (namely "/SharedDocuments/Region/Country"). Also, "Clients" and "Client Name" are concatenated with a " - " separator. The local directory created after Sharepoint Local Sync is clearly some truncated version of the URL path, depending on the Sharepoint folder that was selected for local sync. This makes it difficult to programmatically recreate a Local Directory string like in the OneDrive solution.

I'm hoping someone has encountered this before and could offer some guidance. It's wild to me that you can right click on a locally sync'd Sharepoint file and find a "Location" with the Local Directory string right there in Properties, but identifying it with C# is not straightforward. Let me know if any additional details could be provided, and many thanks.

Hugh Manatee
  • 21
  • 1
  • `I'm hoping someone has encountered this before and could offer some guidance. ` been looking for years, and still looking for a clean solution ! – Ama May 29 '22 at 15:21

1 Answers1

3

OneDrive stores that information in the registry under \HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive.

user16972986
  • 31
  • 2
  • 1
    Thanks! Just found this nugget, helped me to loop through all local SharePoint & OneDrive mounts in VBA :-). https://stackoverflow.com/questions/58062253/excel-vba-find-local-file-location-of-files-on-sharepoint/71753164#71753164 – Koen Rijnsent Apr 05 '22 at 14:32