Excel VBA - Open workbook in sharepoint fails until I log into sharepoint and access file

Question

EDIT 3/31/14 -- No responses...still baffled by this behavior. Bump! Any thoughts?

I have set up some code in an Excel workbook on my local harddrive that navigates to a corporate sharepoint site, browses through a set of folders, and when it comes upon a file, it opens it and pulls some data down. I pieced a lot of it together from other posts and from a previous question I asked.

Here is the issue I have. If I run it without first going into the Sharepoint site and opening a file, it throws an error. However, once I have done so, it works fine. My only thought is that the Sharepoint site is expecting my corporate credentials (username and password), and since I'm not passing that in my

Set oWB = Workbooks.Open(MyPath)

command, it is denying me access. The error is throwing a description...just a long int value.

Here's the full code, with confidential stuff stripped out It basically uses recursion to get to the root child nodes. So I guess my question is two-fold...1) what's causing the issue; 2) if it is the network credentials, can I pass both a username and password somehow?:

Public Stack As New Collection
Public PrintLine As String
Public Spaces As String
Public fnum As Integer
Public outputFile As String


Sub NavigateSharepointSite()

    On Error Resume Next
    Dim spSite As String, spDir As String, spFile As String, url As String

    spSite = "https://myteamssite"
    spDir = ""
    spFile = ""
    url = spSite & spDir & spFile
    Stack.Add (Array(spSite, spDir, spFile, url, "d", 0))

    NavigateFolder spSite, spDir, url, 0

End Sub

Sub NavigateFolder(spSite As String, spDir As String, url As String, level As Integer)

    Dim davDir As New ADODB.Record
    Dim davFile As New ADODB.Record
    Dim davFiles As New ADODB.Recordset
    Dim isDir As Boolean
    Dim tempURL As String
    On Error GoTo showErr
    tempURL = "URL=" & url
    davDir.Open "", tempURL, adModeReadWrite, adFailIfNotExists, adDelayFetchStream
    If davDir.RecordType = adCollectionRecord Then      
        Set davFiles = davDir.GetChildren()  ''Returns recordset of all child records from parent
        Do While Not davFiles.EOF


            davFile.Open davFiles, , adModeRead
            isDir = davFile.Fields("RESOURCE_ISCOLLECTION").Value
            If Not isDir Then ''if not children

                spFile = Replace(davFile.Fields("RESOURCE_PARSENAME").Value, "%20", " ")
                url = spSite & spDir & "/" & spFile
                Stack.Add (Array(spSite, spDir, spFile, url, "f", level))
                If spFile Like "Quarterly*" Then

                   testthis (url)
                End If

            Else

                level = level + 1
                url = Replace(davFile.Fields("RESOURCE_ABSOLUTEPARSENAME").Value, "%20", " ")
                spDir = Right(url, Len(url) - Len(spSite))
                Stack.Add (Array(spSite, spDir, "", url, "d", level))

                NavigateFolder spSite, spDir, url, level
                level = level - 1
            End If
            davFile.Close
            davFiles.MoveNext
        Loop
    End If
    Set davFiles = Nothing
    davDir.Close
    Set davDir = Nothing


    GoTo noErr
    showErr:
    Call MsgBox(Err.Number & ": " & Err.Description & Chr(10) _
    & "spSite=" & spSite & Chr(10) _
    & "spDir= " & spDir & Chr(10) _
    & "spFile=" & spFile, vbOKOnly, "Error")

    noErr:
End Sub



Private Function testthis(MyPath As String)
    Dim oWB As Workbook '', MyPath As String

    Debug.Print MyPath
    If Workbooks.CanCheckOut(MyPath) = True Then          
        Set oWB = Workbooks.Open(MyPath)
        oWB.Application.DisplayAlerts = False
        Debug.Print (oWB.Worksheets(1).Name)
        oWB.Close False
        Set oWB = Nothing

    Else
        MsgBox ("File on Sharepoint can NOT be checked out." + Chr(13) + _
                "Make sure no one else is working in the file." + Chr(13) + _
                "Including yourself.")
        Exit Function
    End If


End Function

Just a couple of questions to test your hypothesis: Are you asked for network credentials when you go into Sharepoint and open the file yourself (without VBA automation)? If not, could the issue be that Sharepoint has not opened fully before the rest of the code executes? Perhaps the code in this question under `WAITFORACCESS:` will help you solve this? http://stackoverflow.com/questions/16657263/opening-an-ms-access-database-from-the-command-line-without-running-any-of-the-s — Instant Breakfast, Apr 11 '14 at 18:11
@KarlRookey To answer your first question, I am not prompted for credentials, as I assume the security is tied to my corporate network ID. I added the Waitforaccess error trapping into my code. I ran it without going into the Sharepoint site. It failed. I tried this 3 times. After going into the site and opening a spreadsheet, the code runs fine. — Jeremy, Apr 14 '14 at 20:11
Thinking out loud here: the code only works if you have opened a spreadsheet that is housed in Sharepoint. Is that file still open when you run the code? If so, it would be part of the `.Workbooks` collection when you `Set oWB = Workbooks.Open(MyPath)`. Looking at your code, I see the `spDir` and `spFile` values are empty and I'm not seeing how they are populated: did you strip that information out, or am I just not noticing it? Is it possible you have code (not shown) that is pulling `spDir` from the opened Sharepoint Spreadsheet file? — Instant Breakfast, Apr 14 '14 at 21:28
@KarlRookey Good thoughts, but I don't think that's the case. After opening the workbook, I close it. Also, if I go into the ribbon and click `Library > Export to Excel` (thereby exporting the object model to Excel), it works. spDir and spFile are populated in the NavigateFolder subproc...they get populated recursively as the process goes through the object model...see: `spDir = Right(url, Len(url) - Len(spSite))` for example. — Jeremy, Apr 15 '14 at 13:02
@KarlRookey Also, I apologize for the lack of identation in my code. I can't seem to figure out how to indent to deeper levels in a code block. If I can figure it out, I'll clean it up for readbility. — Jeremy, Apr 15 '14 at 13:12
I have a similar issue. It's related to sessions expiring on the SharePoint side. I have not found a solution to this issue that I'm satisfied with. — variant, Oct 01 '14 at 18:08

score 2 · Answer 1 · answered Dec 23 '14 at 08:55

You could try sending a HTTP request with your credentials, but the system will probably still ask for your credentials when you get to the point of opening the workbook, all this will really do is start the SharePoint session server-side which could possible stop the issue you're currently having (if that issue is due to authentication). Try the below code before you attempt to open the workbook:

With CreateObject("WinHTTP.WinHttpRequest.5.1")
    .Open "GET", spSite, False
    .SetCredentials  "Domain\username", "Password", 0 'Change as required.
    .Send
End With

Another option I tend to use with SharePoint, is to map a persistent network drive to the SharePoint directory and save your credentials at the point of connecting the drive - then just point to that drive in your code as you would any other drive.

Tbaker · Answer 2 · 2014-10-08T18:47:15.913

0

Really more of a work around than an answer but this might solve your issue for the time being ...

add the "Microsoft Internet Controls" Reference Library and add this to the beginning

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = False

ie.Navigate (YourSite)

ie.quit

Hopefully this will trigger your authentication and allow you to connect to the workbook...

edited Oct 08 '14 at 18:47

answered Oct 08 '14 at 13:13

Tbaker

197
1
1
10

Excel VBA - Open workbook in sharepoint fails until I log into sharepoint and access file

2 Answers2

Linked