Copy Multiple Files from Absolute Paths to SharePoint with Same File Names
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:
Code:
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.
Code:
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
