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.
LOAD F1 as F1R,
[Type ] as TypeR,
[Name ] as NameR,
[Modified ] as ModifiedR,
(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);
VB Function used :
Set objXMLHTTP = CreateObject("msxml2.xmlhttp.6.0")
objXMLHTTP.open "GET", strFileURL, false
If objXMLHTTP.Status = 200 Then
Set objADOStream = CreateObject("ADODB.Stream")
objADOStream.Type = 1
objADOStream.Position = 0
Set objFSO = Createobject("Scripting.FileSystemObject")
If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
Set objFSO = Nothing
Set objADOStream = Nothing
Set objXMLHTTP = Nothing
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