0

I'm a user who maintains low-level VBA tools for myself and some colleagues.

Some of the tools download or upload files from SharePoint online, treating the URLs as filepaths.

I'm not familiar with the mechanics of why this works, but I get the impression it has something to do with "WebDAV" (?) as I'm able to use filepath or url versions of the file locations.

Since upgrading to office 365, some of my colleagues have started to experience frequent "Access Denied" error messages when trying to interact with SharePoint online from Excel, Outlook and Access.

I don't have a clear idea of why this is happening, but my expectation is that SharePoint is validating the requests from my colleagues via some kind of onedrive or sharepoint online authentication ID stored somewhere as part of their federated identity management - everything is single sign on for MS at my company

In an attempt to deploy a more reliable method of accessing SharePoint, I stumbled upon the below, but I wasn't able to implement it because I don't have the user permissions to create an App in SharePoint.

https://global-sharepoint.com/sharepoint-online/in-4-steps-access-sharepoint-online-data-using-postman-tool/https://global-sharepoint.com/sharepoint-online/in-4-steps-access-sharepoint-online-data-using-postman-tool/

After that, I reverted back to the idea that there already exists some type of authentication token or cookie that is stored on my colleagues computers or linked to their ID in some centrally accessible location.

I believe I may have a path forward in the solutions described in the below posts, but I'm struggling with where to begin with them - I can't even figure out where I would modify the code to add my company's specific SharePoint online domain name.

VBA Autheticate to SharePoint Office 365

SharePoint API and VBA - Access Denied

I'd love to figure this out myself, but recognize there is a considerable learning gap I need to overcome in order to get there. Can anyone recommend some good resources I could begin working through to get to a place where these solutions are more useful to me?

Thanks

Improve this question

2 Answers 2

Reset to default
0

I'm assuming that your VBA script is using the MXML2.XMLHTTP60 object to send POST/GET requests. There's a Youtube tutorial which helped me understand how these requests work. Starting with the basics and going through some of the more advanced videos from that channel should help getting a good headstart with your mentioned solutions.

If that doesn't work out, i recommend consider using Power Automate instead of VBA. It's cloud based and has a more elegant and easier way to connect all Office365 applications with a built in authentication process. Of course that would require programming the scripts from scratch, which could, in some cases, still be less tedious.

Improve this answer
2
  • I hear you on the power automate piece. I'm less familiar with JavaScript than I am VBA but I do want to eventually transition to a combination of power automate and office scripts to replace the tools in using. The main roadblock is the interaction between workbooks without tables in them that is difficult to manage in power automate.
    – usernamedgreg
    Commented Aug 7, 2021 at 11:29
  • You're correct I'm using the xml 6 reference library. I'm familiar with a lot of the html basics, but I'm less familiar with the SharePoint specifics. I'll definitely peruse those videos to see if it helps
    – usernamedgreg
    Commented Aug 7, 2021 at 11:32
0

If anyone has happened upon this question at some point in the future

MS's focus on cloud applications forced me to rethink the implementation of some of the end user tools we had implemented. It was ultimately easier to use powerapps, power automate and the graph API, than make webDAV work. Such is the inexorable march of progress.

Improve this answer

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.