Hi All,
I need a stable and flexible solution for accessing SharePoint via Excel VBA. I want to open SharePoint files and check whether some folders exist on SharePoint and create them if they don't. So some really simple tasks, which are easy to be coded in VBA but they don't work if the VBA macro tries to realize them on files stored on SharePoint.
What I have tried are the followings:
- Login to SharePoint under Internet Explorer. This was only a temporary solution because IE is no longer supported and this is not even working any more (no wonder of course).
- Change SharePoint URL to UNC with a function like in this stackoverflow thread: "Get the content of a sharepoint folder with Excel VBA". This was useful for opening SharePoint files under other browsers as well but it's not working any more either ("Path not found" error).
- After having set the recommended settings from this Microsoft documentations: "Error when you try to access a network drive that's mapped to a web share: User has not been authenticated", I successfully mapped the SharePoint folders to a network drive and in this way I could check folder paths and create new ones. Unfortunately this solution was not stable either: when I tried to use it again after some months, it was no longer working ("User is not authorized" error). Moreover, my clients' group policy doesn't allow to change registry settings that are recommended so this solution wouldn't have been an universal one.
So please help me how to open files and check folders on SharePoint via Excel VBA!
Bookmarks