Excel VBA code using authentication to SharePoint

The code below allow me to upload a file to sharepoint using an Excel VBA macro function but it requires me to first go to the sharepoint site and authenticate, I would like to pass the user credentials within the code to allow more automation.


Dim SharepointAddress As String
Dim LocalAddress As String
Dim objNet As Object
Dim FS As Object

' Where you will enter Sharepoint location path
SharepointAddress = "\\sharepoint path to document library"  & "\"          
 ' Where you will enter the file path, ex: Excel file
LocalAddress = "your file path"                                     
Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")
If FS.FileExists(LocalAddress) Then
FS.CopyFile LocalAddress, SharepointAddress
End If
Set objNet = Nothing
Set FS = Nothing

Open in new window

LVL 8
a0k0a7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wilder1626Commented:
Hi

Is this what you are looking for?

Dim User As String
User = Environ("username")

If User = "user name" Or User = "User name" Then  '' Enter user name that will be able to use it

Dim SharepointAddress As String
Dim LocalAddress As String
Dim objNet As Object
Dim FS As Object

' Where you will enter Sharepoint location path
SharepointAddress = "\\sharepoint path to document library" & "\"
 ' Where you will enter the file path, ex: Excel file
LocalAddress = "your file path"
Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")
If FS.FileExists(LocalAddress) Then
FS.CopyFile LocalAddress, SharepointAddress
End If
Set objNet = Nothing
Set FS = Nothing

MsgBox "File Uploaded"

Else

MsgBox "You are not allowed to upload the file"

End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
a0k0a7Author Commented:
I want to hard code the user credentials, there's no need to ask to the end user the authentication for security purposes.

For example, the end user executing the VBA code is not going to have access to the sharepoint library.
0
Wilder1626Commented:
Sorry, I may be slow to understand here. :)

Can you give me more input of what exactly you want to do with the authentication? How do you want it to work?
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

a0k0a7Author Commented:
Hi Jean, thanks a lot for making time to help me out.

I want the VBA code to have the credentials (user name and password hard coded)  

For example, the person using the Excel to upload files to SharePoint is not an authorized user to explore the SharePoint library.

Does that answer your question?
0
Wilder1626Commented:
I don't think this is possible to hard code the user and password. At the same time, It would become to easy to hack somebody's access at all time by doing this process.

I would not recommend it.
0
Wilder1626Commented:
Another option could be to prompt the user to enter a password to be able to send it on SharePoint. Could that be a solution?
0
a0k0a7Author Commented:
I really need to pass the user authentication, I can *lock* the Excel so the user running the VBA *don't* have access to view that information from the Visual Basic Editor
0
Rainer JeschorCommented:
Hi,
what type of authentication have you enabled on the Sharepoint site?
Forms or Windows?
Which Sharepoint version and edition?
Thanks
Rainer
0
a0k0a7Author Commented:
Hi Rainer, we are using SharePoint Online 2013 from Office 365, thanks for making time to answer my question.
0
Rainer JeschorCommented:
Hi,
thanks, and what kind of authentication?
Thanks.
Rainer
0
a0k0a7Author Commented:
I think it's forms but I'm not sure, how I can validate that, right now the same login works with outlook web access.
0
a0k0a7Author Commented:
@Reiner, it's claims-based authentication mode
0
a0k0a7Author Commented:
@Wilder1626, I would like to hard code the credentials but if you have a way to get the code working prompting the user to enter the username and password, it would work for me. Thanks!
0
Wilder1626Commented:
@a0k0a7,

when you say that it would prompt for a user and password, are you asking for the real Window log in and password from the user or you will manage the user and password dbase?
0
a0k0a7Author Commented:
@Wilder1626, thanks for making time to help me out, ideally I would like that authentication to happen within the code, seamless to the user executing the VBA code but any workaround is game right now.
0
Wilder1626Commented:
Sorry. for the delay.
Ok, I will look at it this weekend and i will send you an updated code.
0
a0k0a7Author Commented:
Thanks!
0
a0k0a7Author Commented:
I'm trying this code I saw at https://social.msdn.microsoft.com/Forums/office/en-US/1b26aca0-4579-4cc3-bdaa-ea945452db01/upload-files-to-sharepoint-site-via-vba?forum=sharepointdevelopmentlegacy


If you don't have your answer yet, I upload files to SharePoint using xmlhttp in Access VBA.

Add a reference to Microsoft HTML Object Library and Microsoft Scripting Runtime to your mdb/accdb.

Insert this sub:

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

**********************************************************************************

You need to pass the username/password for each file. You can put those in a variable via InputBox as I have done, or you can look them up in a table, or whatever. If you are uploading only text files you can delete the Else clause in the If statement, otherwise you will need the Else clause to handle binary files.

Good luck - HTH!

Open in new window

0
a0k0a7Author Commented:
I created a new question, related to this one, at the moment trying to debug the error or figure out what I am missing. Any help is appreciated it.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28637519.html
0
a0k0a7Author Commented:
I found the solution to this question, it was complex and required changes on the IE Internet Options Tools. It contains several VBA modules, there's no way you can upload files to SharePoint Online just using the 'PUT' statement. Anyways, I am going to give the points to Jean-Marc (Wilder1626) just because he tried to help me, even do what he posted didn't work for me as it is.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.