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/thread...names.1116856/