Copy Multiple Files from Absolute Paths to SharePoint with Same File Names

[KimP]

Hi,

Firstly, I apologise if this has been answered multiple times but my google searches, although provided ideas, have produced as many questions as answers.

In the Excel file where the code resides, I have an excel table that has a list of absolute file paths (SharePoint). They are in various folders and sub-folders.



File


https://domain.sharepoint.com/sites/site/Shared Documents/FolderA/FolderB/File1.xlsx


https://domain.sharepoint.com/sites/site/Shared Documents/FolderA/FolderB/FolderC/File1.csv


https://domain.sharepoint.com/sites/site/Shared Documents/FolderA/File2.xlsx


https://domain.sharepoint.com/sites/site/Shared Documents/FolderD/FolderE/File3.xlsx


https://domain.sharepoint.com/sites/site/Shared Documents/FolderZ/FolderY/File2.csv












I would like to copy these files to a destination folder (SharePoint) with the same file names.
Before copying the files I need to check if the folder path exists and create it if it doesn't.

I have gotten around some of the SharePoint issues by mapping the site as a network drive with:


Sub MapDrive()

Set WshNetwork = CreateObject("WScript.Network")

On Error Resume Next
WshNetwork.RemoveNetworkDrive "A:"
On Error GoTo 0

WshNetwork.MapNetworkDrive "A:", "https://domain.sharepoint.com/sites/site/Shared Documents/FolderA"

'WshNetwork.RemoveNetworkDrive "A:"

Set WshNetwork = Nothing

End Sub


Now this is where I show my lack of VBA skills but I think it gives the general idea of what I'm trying to achieve. The 'For' loop is likely completely wrong and I have nothing in there yet for checking the folder path/creation.



Sub CopyFiles()


Dim Files As Variant
Dim FSO As Object
Dim DPath As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Set Files = Application.Transpose(Range("FileList"))
Set DPath = Range("PathToSaveReports").Value

For Each f In Files
Set FSO = CreateObject("scripting.filesystemobject")
Set FName = FSO.GetBaseName
Set FPath = FSO.GetAbsolutePathName
f = FSO.CopyFile(FPath, DPath & "/" & FName)

Next f

Set FSO = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub



Hopefully I've provided enough detail. Any suggestions appreciated.

Thanks,
Kim

[KimP]

I have also posted here in an effort to reach a wider audience: https://www.mrexcel.com/board/threads/copy-multiple-files-from-absolute-paths-to-sharepoint-with-same-file-names.1116856/