vba wscript.shell copy file from folder to another folder based on cell path or filename

Question

I want to do it with vba wscript.shell because copying files is faster and I want to copy files based on path or filename in excel cell based on the selection in column "E" and output the destination folder using "msoFileDialogFolderPicker"

I have sample code but need to change.



Sub copy()
xDFileDlg As FileDialog
xDPathStr As Variant
sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c dir C:\copy\*.* /b /s").stdout.readall, vbCrLf), "\")
'For j = 0 To UBound(sn)
'If DateDiff("d", FileDateTime(sn(j)), Date) > 30 Then sn(j) = ""
'Next

sn = Filter(sn, "\")

For j = 0 To UBound(sn)
FileCopy sn(j), "C:\destcopy" & Mid(sn(j), 2)
Next
 Set xDFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
 xDFileDlg.Title = "Please select the destination folder:"
 If xDFileDlg.Show <> -1 Then Exit Sub
 xDPathStr = xDFileDlg.SelectedItems.Item(1) & "\"
End Sub

Thanks

roy

You ask for help to modify the code, but you do not explain what you want accomplishing.. Should we deduce only looking to your (not working) code and the picture you show us? What from the above picture to be used? And how? To do what? What from your code does not work as you want and you need to be changed\appended? — FaneDuru, Nov 26 '21 at 13:18
@FaneDuru , from the excel image above that will be used, I want to copy the file based on the path from column A with vba wscript.shell and also based on the selection in column E marked with "V". the sample code I posted is not working. and output folder using "msoFileDialogFolderPicker". and I want the vba code to be modified or replaced. for information my sheet name is "master" — roy, Nov 26 '21 at 13:32
What from your actual code to be kept? Why are you talking about "vba wscript.shell because copying files is faster", since you want using a **file path**, not a folder to extract its files? — FaneDuru, Nov 26 '21 at 13:39
@FaneDuru , if you want to change actual code no problem and can i use vba wscript.shell to copy file based on reference path or filename in cell? — roy, Nov 26 '21 at 13:48
@FaneDuru , Because if I use "vba wscript.shell" it's faster because there are hundreds of files copied — roy, Nov 26 '21 at 13:53
Where, in your code, you use `Shell` to copy anything? Where are those hundreds of files path? In the sheet you show us? If not, where? If yes, how to you use `Shell` to copy them? Do you wan using VBScript objects which are not in the code you show? I must confess I cannot get you. Now, I could see your last comment which clarify the shell issue. But what about the rest? Can you explain **in words** what you want accomplishing? Something like: to iterate between cells in A:A column and copy the files in ... (where)? Do you want using `FileDialog` for each case? if not, how? — FaneDuru, Nov 26 '21 at 14:00
@FaneDuru , for the shell code that I show is only an example and the code does not work then I ask for the solution. hundreds of files I mean the original files. So the excel sheet that I show is an example. If you're asking me how do I copy files using the shell if I know then I don't need to post. If you want to provide a solution with a different code then I have no problem. thank you — roy, Nov 26 '21 at 14:11
I am afraid I cannot help you. I asked you to explain in words what you try accomplishing and I made a suggestion regarding the way you should do that. I cannot do more, from this point of view. If you do not help us to understand what you need, you cannot be helped, I am afraid... I will leave my office soon. — FaneDuru, Nov 26 '21 at 14:32
@FaneDuru , i want to copy files based on path in column A with the options in column E marked "V" and for output folder i want with "msoFileDialogFolderPicker" — roy, Nov 26 '21 at 14:46
I asked ** Do you want using FileDialog for each case? If not, how?** but nothing clarified. I never start a project without completely knowing what is to be done. I will leave my office in about 10 minutes... — FaneDuru, Nov 26 '21 at 14:49
@FaneDuru , yes I want to use a fixed filedialog the output folder remains one — roy, Nov 26 '21 at 14:58
Not understood. Once for all, or for each of them? I am driving now... — FaneDuru, Nov 26 '21 at 15:32

FaneDuru · Accepted Answer · 2021-11-27T08:47:31.920

Please, test the next code. It assumes that you need to select the destination folder for copying of all files there. Otherwise, some milliseconds saved by VBScript object mean too little against the necessary seconds to browse for each file destination folder to be copied. But, if this is what you want, I can easily adapt the code to do that:

Sub copyFiles()
  Dim sh As Worksheet, lastR As Long, arrA, i As Long, k As Long
  Dim fileD As FileDialog, strDestFold As String, FSO As Object
  
  Set sh = ActiveSheet
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row ' last row on A:A column
  arrA = sh.Range("A2:E" & lastR).Value2                   'place the range in an array for faster iteration
  Set FSO = CreateObject("Scripting.FileSystemObject")
  With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select the destination folder!"
        .AllowMultiSelect = False
        If .Show = -1 Then
            strDestFold = .SelectedItems.Item(1) & "\"   'select the destination folder
        End If
  End With
  If strDestFold = "" Then Exit Sub                         'in case of  not selecting any folder
  For i = 1 To UBound(arrA)
     If UCase(arrA(i, 5)) = "V" Then                         'copy the file only if a "V" exists in column E:E
        If FSO.FileExists(arrA(i, 1)) Then                    'check if the path in excel is correct
            FSO.CopyFile arrA(i, 1), strDestFold, True     'copy the file (True, to overwrite the file if it exists)
            k = k + 1
        Else
            MsgBox arrA(i, 1) & " file could not be found." & vbCrLf & _
                        "Please, check the spelling and correct the file full path!", vbInformation, _
                        "File does not exist..."
        End If
     End If
  Next i
  MsgBox "Copied " & k & " files in " & strDestFold, , "Ready..."
End Sub

I have tried the code from you and according to what I want but there is no selection based on column E so I can choose which path I want to copy. — roy, Nov 27 '21 at 03:40
@roy I missed that part... Please, test the updated code and send some feedback. — FaneDuru, Nov 27 '21 at 08:48

vba wscript.shell copy file from folder to another folder based on cell path or filename

1 Answers1