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

EDIT:
I should have mentioned, also posted here: Copy Multiple Files from Absolute Paths to SharePoint with Same File Names