Save excel workbook directly to Sharepoint using VBA

[greenwell]

Hi all

I have an excel workbook that I want to upload and save to sharepoint using a button with a macro attached to it. Each user will have the same username and password so I want to eliminate the need for them to enter it!

I found some advice which told me to map the sharepoint drive then save to the mapped drive. To map the drive which works perfectly but when I got to save it to the mapped drive I get a pop up asking for me to login and enter my credentials again!

Is there a better way to save it directly to sharepoint without the need to map or does someone more enlightened than me know a way to avoid this credential pop up happening each time.

If I enter the login info it saves the file fine I just don’t want this step if at all possible.

I'm using excel 2013 and I believe the sharepoint is 2013 also but it could be 2010 although I hope this doesn't make a difference for what I want to find here.

Many thanks in advance for your help,

Sub sharepointmap()
 
Dim objNetwork
Dim objDrivePath
Dim strDriveLetter
Dim strRemotePath
Dim strUserName
Dim strPassWord
Dim filename As String
 
strDriveLetter = "Z:"
'strRemotePath = "\\sp.sharepoint\MyFolder"
Set objNetwork = CreateObject("WScript.Network")
strUserName = "Username"
strPassWord = “Password”
 
objNetwork.MapNetworkDrive strDriveLetter, strRemotePath, False, strUserName, strPassWord
 
filename = Range("Filename").Value & " - " & Format(Range("reportingdatevalue").Value, "dd.mm.yyyy") & ".xlsm" 
    iReply = MsgBox(Prompt:="Is the form complete and ready to upload to Sharepoint?", _
     Buttons:=vbYesNo, Title:="Save to Sharepoint") 
     If iReply = vbYes Then
        ActiveWorkbook.SaveAs filename:= _
        “Z:\" & filename, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
     Else
        Exit Sub 
     End If
objNetwork.RemoveNetworkDrive "Z:", unmount
MsgBox ("Upload complete! Thank you.")
End Sub

 

[paulma1960]

What I'd like to know is what data/object type to declare objNetwork as, so that when I put a period after it, I get a list of the possible functions.