How to copy files to sharepoint with Excel VBA

[Arpanakumar]

Hello!

Thanks to chandoo forum for always giving me best solutions.

Today i was assigned to work on move files from folder to share point from excel.

There are 50+ file paths in Column "B".

What macro should do is go to folder and pick up the file and add to share point with respective folders..Am not really sure if this is possible.

I have no file to attach.

 

[Monty]

Try this.

Hope this helps..?Let me further requirment to modify as per your requirment.

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
fnum = FreeFile()
outputFile = "c:\your directory\Tree.txt"
Open outputFile For Output As fnum
Spaces = " "
spSite = "https://your Sharepoint site/"
spDir = ""
spFile = ""
url = spSite & spDir & spFile
Stack.Add (Array(spSite, spDir, spFile, url, "d", 0))
Print #fnum, spSite
Print #fnum, Spaces & spDir
NavigateFolder spSite, spDir, url, 0
Close #fnum
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 ', _
' "username", _
' "password"
If davDir.RecordType = adCollectionRecord Then
Set davFiles = davDir.GetChildren()
Do While Not davFiles.EOF
' davFile.Open davFiles, , adModeReadWrite
davFile.Open davFiles, , adModeRead
isDir = davFile.Fields("RESOURCE_ISCOLLECTION").Value
If Not isDir Then
spFile = Replace(davFile.Fields("RESOURCE_PARSENAME").Value, "%20", " ")
url = spSite & spDir & "/" & spFile
Stack.Add (Array(spSite, spDir, spFile, url, "f", level))
PrintLine = ""
For i = 1 To level + 1
PrintLine = PrintLine & Spaces
Next i
Print #fnum, PrintLine & spFile
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))
PrintLine = ""
For i = 1 To level
PrintLine = PrintLine & Spaces
Next i
Print #fnum, PrintLine & "/" & spDir
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
' Do something with files here...

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

noErr:
' No errors
End Sub

 

[Arpanakumar]

Thanks Monty as always.
Let me check and will get back to you with any questions.
Firstly wanted to understand the code.