How to login to SharePoint using VBA
I am creating an access program that needs to access the Company SharePoint site. If the user has previously logged in using their Microsoft credentials, the code below maps an available drive letter to the SharePoint folder. However I would like to embed a specific user name and password in my code to access the SharePoint site. I was able to do this thru Internet Explorer, but Microsoft shut IE down on June 22nd and I’m not longer able to use the workaround.
I’ve searched for 2 days and have not come up with any examples of code that allows me to login to SharePoint with a user name and password set in the code (Not necessarily the user name and password of the user). Any help would be greatly appreciated.
Code:
Function fDownloadUpdaterFile(SharepointAddress As String, sLocalPath As String, sUpdateFileName As String) As Boolean
Dim LocalAddress As String
Dim sResult As String
Dim sMappedDrive As String
Dim objFolder As Object
Dim objNet As Object
Dim objFile As Object
Dim fs As Object
Dim bResult As Boolean
'Create a mapped drive the the sharepoint folder
sMappedDrive = fNextAvaliableDrive & ":"
Debug.Print "Using mapped drive " & sMappedDrive
'20220616 - AMD Need these 3 lines to authenticate Sparepoint If they have authenticated from this computer in the past.
Dim objFd As FileDialog
Set objFd = Application.FileDialog(msoFileDialogFolderPicker)
objFd.InitialFileName = "https://[Company Name].sharepoint.com"
Set objNet = CreateObject("WScript.Network")
Set fs = CreateObject("Scripting.FileSystemObject")
Debug.Print "SP Folder to map to:" & SharepointAddress
objNet.MapNetworkDrive sMappedDrive, SharepointAddress
Set objFolder = fs.GetFolder(sMappedDrive)
'Check if the file you want to download is there
If Dir(sMappedDrive & "/" & sUpdateFileName) = "" Then
fLog "Update Failed: Could not find " & sUpdateFileName & " on sharepoint."
fDownloadUpdaterFile = False
GoTo fDownloadUpdaterFile_Exit
End If
'Download the file
Debug.Print "Copying " & sMappedDrive & "/" & sUpdateFileName & " to " & fGetFrontEndPath & sUpdateFileName
bResult = fCopyFile(sMappedDrive & "/" & sUpdateFileName, fGetFrontEndPath & sUpdateFileName)
If bResult Then
fDownloadUpdaterFile = True
Else
fDownloadUpdaterFile = False
End If
fDownloadUpdaterFile_Exit:
On Error Resume Next
objNet.RemoveNetworkDrive sMappedDrive, True, True
Set objNet = Nothing
Set fs = Nothing
Exit Function
fDownloadUpdaterFile_ERROR:
If Err.Number = -2147023652 Then
MsgBox "You need to relog into sharepoint."
Else
MsgBox Err.Number & ":" & Err.Description
Debug.Print Err.Number & ":" & Err.Description
End If
fDownloadUpdaterFile = False
GoTo fDownloadUpdaterFile_Exit
End Function
