2

I have a routine in excel vba which retrieves files and file listings from a sharepoint server, in order to pull specific data from multiple files. This works great if the user has gone to the server in windows explorer beforehand (entering \server.address.com\Directory\Folder\Structure\ in the address bar) and logging in using the windows login information.

If the user hasn't logged on to that since restarting, the system throws an error because it cannot access the file system on the sharepoint server.

I thought I would be able to open an explorer window using shell:

Shell "explorer.exe /e, \\server.address.com\Directory\Folder\Structure\", vbNormalFocus

But it simply opens up in the user's documents folder if they haven't logged in yet. I also tried using the file system object to connect to the folder, hoping for a prompt then:

 Dim FSO As Object, FLD As Object
 Set FSO = CreateObject("scripting.filesystemobject")
 Set FLD = FSO.GetFolder("\\server.address.com\Directory\Folder\Structure\")

But no joy.

Also, there are no spaces in the directory structure, so I haven't need to escape the path (though I did try anyways).

I also tried forcing the user to map the folder in windows explorer, from the drivemapper code found at Get the content of a sharepoint folder with Excel VBA but I get the error "The operation being request was not performed because the user has not been authenticated."

Any idea what else I can try in order to authenticate the end users without having them all add sharepoint to their explorer favorites (my manual workaround).

Thanks

Community
  • 1
  • 1
Matt V
  • 53
  • 4
  • 1
    this might help? http://stackoverflow.com/a/27616988/1467082 – David Zemens Apr 02 '15 at 15:45
  • Unfortunately neither worked.. the first said "The URL does not use a recognized protocol" (possibly because I'm opening to a server address rather than http address?), and the IE method gave me an error saying the path was incorrect (likely because explorer hasn't authenticated the user) – Matt V Apr 02 '15 at 19:13
  • 1
    I'm fresh out of suggestions, since I don't use Sharepoint in any automation. i've had similar issues trying to direct users to a "Help" page on an internal Intranet site which requires authentication -- the easiest solution unfortunately is just to trap the error and exit the procedure, Give the user a notification that s/he needs to log in manually, then try again. It's not pretty, but it works... – David Zemens Apr 02 '15 at 19:22
  • I ended up doing something similar as well, but I'm still hoping someone finds a magical solution to this. I put the server address in an inputbox, and popped it up asking the user to copy it to their clipboard and then when they press ok an explorer window opens up for them to paste it into. Seemed to be the least invasive patch I could find. – Matt V Apr 02 '15 at 19:52
  • Could you post your code when you are trying to log on sharepoint? Also did you think to open a connection just like a DB? I have connected to a SP by opening a connection. – Ionut Mar 29 '18 at 07:12

1 Answers1

0

Capture the error, have a popup that tells them they need to login. When they press Ok, execute something like the following code: 

ActiveWorkbook.FollowHyperlink Address:="https://yourSharePointSitehere", NewWindow:=True
tysonwright
  • 1,525
  • 1
  • 9
  • 19