Can a SharePoint credential prompt activated by a data connection refresh be accounted for in VBA?

Question

I have an Excel workbook that has an active data connection to a SharePoint list on a company server. The SP list is just a listing of all the files in an SP document library at that point in time. I have a VBA subroutine that is responsible for refreshing this data connection to see what is in the library at that time and then move some info from the list (document name, document author, submission timestamp, etc.) to a different workbook.

The SharePoint site uses Active Directory credentials to authenticate and the SharePoint is also mapped as a network drive on the PC running the code. But even so, refreshing this data connection sometimes results in a credential prompt that looks just like the image at the end of my post. If I manually enter the same AD credentials again, the connection request is authenticated and the list updates in Excel.

My question is this: how can I account for this in my code? Ideally, I would like for this to trigger an email alert or something, but the thing is that the line of code (ThisWorkbook.RefreshAll) that performs the connection refresh does not run to completion until the credential prompt is dealt with, so I can't set up any handlers in the lines of code that follow. I can't have this refresh potentially resulting in code that just hangs on this line until someone happens to notice something is wrong (it is running on an unattended PC). Anyone know anything that could help deal with my issue?

Answer 1

Since the drive is locally mapped, you should be able to just go directly to the file and manipulate it however you need, importing it, instead of having an active data connection. It would allow you more flexibility than a more rigid data connection.

This website has a good example showing how to do what you're looking for, but the way I'm imagining would be more efficient considering the circumstances.

Answer 2

This really depends on how you are doing your connection and in some instances it is not possible, but you can append Username and Password to a URL to pass your credentials, such as defined here (for other languages but you get the gist):

https://www.connectionstrings.com/sharepoint/

Now the reality is, you probably aren't doing a REST connection and you might have to as discussed here: https://www.experts-exchange.com/questions/28628642/Excel-VBA-code-using-authentication-to-SharePoint.html

They recommended:

Public Sub CopyToSharePoint()
On Error GoTo err_Copy

Dim xmlhttp
Dim sharepointUrl
Dim sharepointFileName
Dim tsIn
Dim sBody
Dim LlFileLength As Long
Dim Lvarbin() As Byte
Dim LobjXML As Object
Dim LstrFileName As String
Dim LvarBinData As Variant
Dim PstrFullfileName As String
Dim PstrTargetURL As String
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim fldr As Folder
Dim f As File
Dim pw As String
Dim UserName As String
Dim RetVal
Dim I As Integer
Dim totFiles As Integer
Dim Start As Date, Finish As Date

UserName = InputBox(Username?") pw = InputBox("Password?")

sharepointUrl = "[http path to server]/[server folder to write to]"

Set LobjXML = CreateObject("Microsoft.XMLHTTP")

Set fldr = fso.GetFolder(CurrentProject.Path & "\[folder with files to
upload]\") totFiles = fldr.Files.Count

For Each f In fldr.Files

  sharepointFileName = sharepointUrl & f.Name

'****************************   Upload text files 
**************************************************

  If Not sharepointFileName Like "*.gif" And Not sharepointFileName
Like "*.xls" And Not sharepointFileName Like "*.mpp" Then

    Set tsIn = f.OpenAsTextStream
    sBody = tsIn.ReadAll
    tsIn.Close
  
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")
    xmlhttp.Open "PUT", sharepointFileName, False, UserName, Password
    xmlhttp.Send sBody
       Else

'****************************   Upload binary files 
**************************************************
  
    PstrFullfileName = CurrentProject.Path & "\[folder with files to upload]\" & f.Name
    LlFileLength = FileLen(PstrFullfileName) - 1

    ' Read the file into a byte array.
    ReDim Lvarbin(LlFileLength)
    Open PstrFullfileName For Binary As #1
    Get #1, , Lvarbin
    Close #1

    ' Convert to variant to PUT.
    LvarBinData = Lvarbin
    PstrTargetURL = sharepointUrl & f.Name


    ' Put the data to the server, false means synchronous.
    LobjXML.Open "PUT", PstrTargetURL, False, Username, Password

   ' Send the file in.
    LobjXML.Send LvarBinData

  End If
     I = I + 1   RetVal = SysCmd(acSysCmdSetStatus, "File " & I & " of " & totFiles & " copied...")    Next f

  RetVal = SysCmd(acSysCmdClearStatus)   Set LobjXML = Nothing   Set
fso = Nothing


err_Copy: If Err <> 0 Then   MsgBox Err & " " & Err.Description End If

End Sub 

Realistically, I think this answer may get you going down the right road: https://sharepoint.stackexchange.com/questions/255264/sharepoint-api-and-vba-access-denied

Regardless, this is a problem and good luck. I had better luck using MS Access to link the list as a table and then using Excel to just call Access and get what I needed.

Private Sub cmdSyncSP_Click()
On Error GoTo ErrorCode
    Application.Cursor = xlWait
    Dim app As New Access.Application
    'Set app = CreateObject("Application.Access")
    app.OpenCurrentDatabase Application.ActiveWorkbook.Path & "\SP_Sync.accdb"
    app.Visible = False
    app.Run "doManualCheck"
    app.CloseCurrentDatabase
    Set app = Nothing
    MsgBox "Sync has finished.  Refresh and proceed to copy your data.", vbInformation + vbOKOnly, "Success"
ExitCode:
    On Error Resume Next
    Application.Cursor = xlDefault
    Exit Sub
ErrorCode:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Sync Error"
    Resume ExitCode
End Sub