Consulting

Results 1 to 2 of 2

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

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location

    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:
    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

  2. #2
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    I have also posted here in an effort to reach a wider audience: https://www.mrexcel.com/board/thread...names.1116856/

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •