Re: Using an Excel sheet for batch delete

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Colin

This should cover A and B.

C. Do you want the routine always to look up the file extension, or should we first check if the filename include an file extension ?

D. Should I include at statement to clear column B before the transfer is started ?

Sub test()

Set fs = CreateObject("Scripting.FileSystemObject")

SourcePath = InputBox("Enter source path : ")
If SourcePath = "" Then Exit Sub

sExists:
If fs.FolderExists(SourcePath) = False Then
SourcePath = InputBox("The path " & SourcePath & " don't exists" _
& vbLf & vbLf & "Enter path : ")
GoTo sExists
End If
If Right(SourcePath, 1) <> "\" Then SourcePath = SourcePath & "\"

DestPath = InputBox("Enter destination path : ")
If DestPath = "" Then Exit Sub
dExists:
If fs.FolderExists(DestPath) = False Then
DestPath = InputBox("The path " & DestPath & " don't exists" _
& vbLf & vbLf & "Enter path : ")
GoTo dExists
End If
If Right(DestPath, 1) <> "\" Then DestPath = DestPath & "\"

LastRow = Range("A1").End(xlDown).Row

For r = 2 To LastRow
FileToMove = SourcePath & Cells(r, "A").Value
If fs.fileexists(FileToMove) = True Then
fs.movefile FileToMove, DestPath
Cells(r, "B") = "Moved"
Else
Cells(r, "B") = "Not Found In Source Path"
End If
Next
Set fs = Nothing
End Sub

Best regards,
Per

"Colin Hayes" <Colin@xxxxxxxxxxxxxxxxxx> skrev i meddelelsen news:lpHS7QAXHKoIFwcL@xxxxxxxxxxxxxxxxxxxxx

Hi Per

OK I tried it out and it works fine. Thank you - I'm grateful.

Here is what happened here :

A. It moved the files in my list , but it does give an error of 'Invalid procedure Call Or Argument' after the last file at the line 'fs.movefile fToMove, DestPath'.


B. Also , where a filename in column A is not found in the source folder , the whole program stops.

In column B , could each filename be marked 'Moved' or 'Not Found In Source Path' when the routine runs? The routine could then run though smoothly from top to bottom without stopping.

This would be better than removing the file from the list as the present routine does , and would mean it could ignore unfound files and just mark in column be the success or failure of the Move.


C. Do you think too , that the routine could be made to ignore the file extension when checking if the file is present in the source folder?


Thanks again Per


Best Wishes



.

Quantcast