0

I have a VSTO on Ms Excel/Word which checks the current location of the active document (Dim MyPath as String = Application.ActiveWorkbook.FullName), and then pulls the list of files located in the same folder which contains that document (For Each MyFile As FileInfo in New DirectoryInfo(MyPath).GetFiles).

This works fine when the file is on a local drive. How can I adapt the code to make it work for files stored on the online Office 365 version of SharePoint?

Even some recommended reading would be useful as I am not sure where to start..

Code in C# or VB are both ok.


Update

I figured out I will use CSOM. Here are the steps I am thinking of:

  • First get the folder URI: Uri FolderUri = new Uri(Word.Document.Path)
  • Then get the server URL: String ServerUrl = FolderUri.AbsoluteUri.Replace(FolderUri.AbsolutePath, ""); (e.g. ServerUrl = @"httpd://contoso.sharepoint.com")
  • Connect using ClientContext Context = new ClientContext(ServerUrl)
  • Append credentials: Context.Credentials = ###
    • Either, ### = new SharePointOnlineCredentials(UserName, Password), but that involves the user actively typing credentials;
    • Whilst I would prefer to avoid this step, by relying on the credentials the user used to access the file (typically, in Word/Excel, they do File/Open/SharePoint Sites). So I guess that would be something like ### = System.Net.CredentialCache.DefaultNetworkCredentials ? perhaps along with Context.AuthenticationMode = ClientAuthenticationMode.Default?
  • Get the Folder relative URL: String FolderRelativeUrl = FolderUri .AbsolutePath (e.g. FolderRelativeUrl = @"/sites/thissite/doclib/folder/thisfolder")
  • Append a Folder request: Client.Folder ThisFolder = Context.Web.GetFolderByServerRelativeUrl(FolderRelativeUrl).Files
  • Append loading of files property: Context.Load(ThisFolder, x => x.Files)
  • Run querry: Context.ExecuteQuerry
  • Get file names: IEnumerable<String> = ThisFolder.Files.Select(x => x.Name)

Am I doing something wrong? (one of the added difficulties I have is that I am unable to test the code on the machine I develop the VSTO from..)

Full code

Uri FolderUri = new Uri(Application.ActiveDocument.Path);
String ServerUrl = FolderUri.AbsoluteUri.Replace(FolderUri.AbsolutePath, "");  
ClientContext Context = new ClientContext(ServerUrl);
Context.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
Context.AuthenticationMode = ClientAuthenticationMode.Default;
String FolderRelativeUrl = FolderUri.AbsolutePath;
Client.Folder ThisFolder = Context.Web.GetFolderByServerRelativeUrl(FolderRelativeUrl).Files;
Context.Load(ThisFolder, x => x.Files);
Context.ExecuteQuerry;
IEnumerable<String> = ThisFolder.Files.Select(x => x.Name);

1 Answer 1

1

It really depends on where you want your code to live. Excel Online is not going to is not going to run your VSTO add-in. For an overview of the various SharePoint API's, start here.
If you want to run something on the client computer, you should look into PnP-PowerShell or the C# CSOM.
If you are looking for something to run inside a SharePoint page Online, take a look at the SharePoint Framework (SPFx) for creating custom webparts.

7
  • Thanks Willman. This needs to run on a VSTO. SharePoint files are accessed via the desktop version of Office. I read about CSOM. Should I get the URL of the document via AcitveWorkbook.FullName. Then from this URL deduce the Document Library it sits in, and from there pull the list of documents available in this Library?
    – Ama
    Jan 25, 2020 at 2:45
  • Yes, you can use CSOM from within a VSTO -- if you've used nuget, you can execute Install-Package Microsoft.SharePointOnline.CSOM to add the dependencies to your solution, or you can download the SDK manually. SharePoint Online has limited support for macros and add-ins though, so even if your VSTO add-in works in Excel on the desktop, only trial and error will tell whether it will work when it is hosted in the browser.
    – willman
    Jan 25, 2020 at 4:17
  • You helped me a lot to progress on that topic. Could you take a look at my update?
    – Ama
    Jan 25, 2020 at 19:10
  • 1
    Check out this reference for doing single-signon for O365 CSOM, but I would stick with letting the user enter credentials until you've gotten the rest of your VSTO working like you want, you will end up doing a bunch of trial and error to get single-signon working, you want to eliminate the rest of your problems first. The code you've posted looks like it should get you there.
    – willman
    Jan 25, 2020 at 20:20
  • Thanks. I think you got the wrong link though?
    – Ama
    Jan 25, 2020 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.