Copy Multiple Files from SharePoint to Local

    Hi,

     

    Some times it is required to copy data from one location to another location without reading. This can be possible using COPY or XCOPY commands in windows.

    But these commands will not work when the source location is Sharepoint or any Web site.

     

    So I used VB Function  (shared by jonas Copy file from URL to folder)  and implemented the same in my work book. which is working as expected.

     

    This Application can copy multiple files from share point to Local/Any drive using VB function.

     

    Script Editor does the following:

    1. Load Statement from https Sharepoint Folder location to read the list of file names which are available.

    2 Looping to each record and storing the file name in v_SourceFile  variable along with the file location

    3.Storing destination file location with file name in v_DestinationFile  variable

    4. Calling CopySharepointFiles VB function to copy the files.

     

    FileNamesR:

     

    LOAD F1 as F1R,

         [Type  ] as TypeR,

         [Name  ] as NameR,

         [Modified  ] as ModifiedR,

         [  ]

    FROM

     

    [https://sharepoint.lllll.com/datamart/documents]

    (html, utf8, embedded labels, table is @9);

     

    LET vFilePathRel = 'https://sharepoint.lllll.com/datamart/documents';

    FOR i = 1 TO NoOfRows('FileNamesR') -1

    LET vFNameRel = peek('NameR', $(i),'FileNamesR');

    LET v_SourceFile = vFilePathRel &'/' &(left(vFNameRel,len(vFNameRel)-2))&'.xlsx';

    LET v_DestinationFile = 'C:\Users\itsme\Downloads' &'\' &(left(vFNameRel,len(vFNameRel)-2))&'.xlsx';

    let v= CopySharepointFiles(v_SourceFile,v_DestinationFile);

     

    NEXT i

     

    VB Function used :

    function CopySharepointFiles(strFileURL,strHDLocation)

     

       Set objXMLHTTP = CreateObject("msxml2.xmlhttp.6.0")

         objXMLHTTP.open "GET", strFileURL, false

         objXMLHTTP.send()

         If objXMLHTTP.Status = 200 Then

             Set objADOStream = CreateObject("ADODB.Stream")

             objADOStream.Open

             objADOStream.Type = 1

             objADOStream.Write objXMLHTTP.ResponseBody

             objADOStream.Position = 0

             Set objFSO = Createobject("Scripting.FileSystemObject")

             If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation

                 Set objFSO = Nothing

                 objADOStream.SaveToFile strHDLocation

                 objADOStream.Close

                 Set objADOStream = Nothing

             End if

             Set objXMLHTTP = Nothing

    end function

     

     

     

    Points To Remember:

    select Allow System Access and System Access to execute VB Function.

    Check Can Execute External Programs in Script Editor Settings to execute this application from publisher

     

     

    Regards,

    Kamakshi Suram