Using an Excel sheet for batch delete

HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps built
in to the routine , or entered via a popup) and delete files of the same
name in turn.

Once a file is deleted , then the routine would go back to Excel and
look up the next file name in the column for the next delete , and so on
until it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

Hi

Try this:

Sub test()
MyPath = "c:\temp\"
Set fs = CreateObject("Scripting.FileSystemObject")

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

For r = 2 To LastRow
fs.DeleteFile MyPath & Cells(r, "A").Value
Cells(r, "A").ClearContents ' Remove file from list after deleting it
Next

End Sub

Best regards,
Per

"Colin Hayes" skrev i meddelelsen
news

HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps built in
to the routine , or entered via a popup) and delete files of the same name
in turn.

Once a file is deleted , then the routine would go back to Excel and look
up the next file name in the column for the next delete , and so on until
it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

This little macro will do that. Enter your path in the "Const" line. HTH
Otto
Sub DeleteFiles()
Const ThePath = "C:\aaThe Folder\"
Dim rColA As Range
Dim i As Range
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
Kill ThePath & i.Value
Next i
End Sub
"Colin Hayes" wrote in message
news

HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps built in
to the routine , or entered via a popup) and delete files of the same name
in turn.

Once a file is deleted , then the routine would go back to Excel and look
up the next file name in the column for the next delete , and so on until
it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

In article , Otto Moehrbach
writes
This little macro will do that. Enter your path in the "Const" line. HTH
Otto
Sub DeleteFiles()
Const ThePath = "C:\aaThe Folder\"
Dim rColA As Range
Dim i As Range
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
Kill ThePath & i.Value
Next i
End Sub


Hi Otto and Per

Thanks for your suggestions. Very useful , and solved my problem.

As an extension of this , could a macro be made to Move the file from
Folder A to Folder B , rather than delete it?

Perhaps with a popup requesting source and destination folders?


Thanks again




"Colin Hayes" wrote in message
news

HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps built in
to the routine , or entered via a popup) and delete files of the same name
in turn.

Once a file is deleted , then the routine would go back to Excel and look
up the next file name in the column for the next delete , and so on until
it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

Thanks for your reply.

Look at this:

Sub test()

Set fs = CreateObject("Scripting.FileSystemObject")

fToMove = Application.GetOpenFilename(, , "Select file to move")

DestPath = InputBox("Enter destination path : ")
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 & "\"

fs.movefile fToMove, DestPath
End Sub

Regards,
Per

"Colin Hayes" skrev i meddelelsen
...
In article , Otto Moehrbach
writes
This little macro will do that. Enter your path in the "Const" line. HTH
Otto
Sub DeleteFiles()
Const ThePath = "C:\aaThe Folder\"
Dim rColA As Range
Dim i As Range
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
Kill ThePath & i.Value
Next i
End Sub


Hi Otto and Per

Thanks for your suggestions. Very useful , and solved my problem.

As an extension of this , could a macro be made to Move the file from
Folder A to Folder B , rather than delete it?

Perhaps with a popup requesting source and destination folders?


Thanks again




"Colin Hayes" wrote in message
news

HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps built
in
to the routine , or entered via a popup) and delete files of the same
name
in turn.

Once a file is deleted , then the routine would go back to Excel and
look
up the next file name in the column for the next delete , and so on
until
it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

Hi Per

OK thanks for that.

It's nearly there , but remember it needs to get the file names from
column A , rather than ask for files via the directory tree.

It should ask for source folder and destination folder once at the
beginning , and then look down column A moving the file names shown in
each cell from source to destination.

It would have these steps;

A. Ask for the source directory.

B. Ask for the destination directory.

C. Lookup each filename from column A , and move them in turn until it
reaches the bottom.

Just like your delete routine , but Moving from one folder to another.
So a combination of the two routines would be best.

Can your routine be adapted to do this , please?

Thanks.





In article , Per Jessen
writes
Thanks for your reply.

Look at this:

Sub test()

Set fs = CreateObject("Scripting.FileSystemObject")

fToMove = Application.GetOpenFilename(, , "Select file to move")

DestPath = InputBox("Enter destination path : ")
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 & "\"

fs.movefile fToMove, DestPath
End Sub

Regards,
Per

"Colin Hayes" skrev i meddelelsen
...
In article , Otto Moehrbach
writes
This little macro will do that. Enter your path in the "Const" line. HTH
Otto
Sub DeleteFiles()
Const ThePath = "C:\aaThe Folder\"
Dim rColA As Range
Dim i As Range
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
Kill ThePath & i.Value
Next i
End Sub


Hi Otto and Per

Thanks for your suggestions. Very useful , and solved my problem.

As an extension of this , could a macro be made to Move the file from
Folder A to Folder B , rather than delete it?

Perhaps with a popup requesting source and destination folders?


Thanks again




"Colin Hayes" wrote in message
news
HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps built
in
to the routine , or entered via a popup) and delete files of the same
name
in turn.

Once a file is deleted , then the routine would go back to Excel and
look
up the next file name in the column for the next delete , and so on
until
it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

This should do it:

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
fs.movefile SourcePath & Cells(r, "A").Value, DestPath
Cells(r, "A").ClearContents ' Remove file from list after moving it
Next
fs.movefile fToMove, DestPath
End Sub

Regards,
Per

"Colin Hayes" skrev i meddelelsen
...


Hi Per

OK thanks for that.

It's nearly there , but remember it needs to get the file names from
column A , rather than ask for files via the directory tree.

It should ask for source folder and destination folder once at the
beginning , and then look down column A moving the file names shown in
each cell from source to destination.

It would have these steps;

A. Ask for the source directory.

B. Ask for the destination directory.

C. Lookup each filename from column A , and move them in turn until it
reaches the bottom.

Just like your delete routine , but Moving from one folder to another.
So a combination of the two routines would be best.

Can your routine be adapted to do this , please?

Thanks.





In article , Per Jessen
writes
Thanks for your reply.

Look at this:

Sub test()

Set fs = CreateObject("Scripting.FileSystemObject")

fToMove = Application.GetOpenFilename(, , "Select file to move")

DestPath = InputBox("Enter destination path : ")
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 & "\"

fs.movefile fToMove, DestPath
End Sub

Regards,
Per

"Colin Hayes" skrev i meddelelsen
...
In article , Otto Moehrbach
writes
This little macro will do that. Enter your path in the "Const" line.
HTH
Otto
Sub DeleteFiles()
Const ThePath = "C:\aaThe Folder\"
Dim rColA As Range
Dim i As Range
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
Kill ThePath & i.Value
Next i
End Sub


Hi Otto and Per

Thanks for your suggestions. Very useful , and solved my problem.

As an extension of this , could a macro be made to Move the file from
Folder A to Folder B , rather than delete it?

Perhaps with a popup requesting source and destination folders?


Thanks again




"Colin Hayes" wrote in message
news
HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps
built
in
to the routine , or entered via a popup) and delete files of the same
name
in turn.

Once a file is deleted , then the routine would go back to Excel and
look
up the next file name in the column for the next delete , and so on
until
it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

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








In article , Per Jessen
writes
This should do it:

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
fs.movefile SourcePath & Cells(r, "A").Value, DestPath
Cells(r, "A").ClearContents ' Remove file from list after moving it
Next
fs.movefile fToMove, DestPath
End Sub

Regards,
Per

"Colin Hayes" skrev i meddelelsen
...


Hi Per

OK thanks for that.

It's nearly there , but remember it needs to get the file names from
column A , rather than ask for files via the directory tree.

It should ask for source folder and destination folder once at the
beginning , and then look down column A moving the file names shown in
each cell from source to destination.

It would have these steps;

A. Ask for the source directory.

B. Ask for the destination directory.

C. Lookup each filename from column A , and move them in turn until it
reaches the bottom.

Just like your delete routine , but Moving from one folder to another.
So a combination of the two routines would be best.

Can your routine be adapted to do this , please?

Thanks.





In article , Per Jessen
writes
Thanks for your reply.

Look at this:

Sub test()

Set fs = CreateObject("Scripting.FileSystemObject")

fToMove = Application.GetOpenFilename(, , "Select file to move")

DestPath = InputBox("Enter destination path : ")
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 & "\"

fs.movefile fToMove, DestPath
End Sub

Regards,
Per

"Colin Hayes" skrev i meddelelsen
...
In article , Otto Moehrbach
writes
This little macro will do that. Enter your path in the "Const" line.
HTH
Otto
Sub DeleteFiles()
Const ThePath = "C:\aaThe Folder\"
Dim rColA As Range
Dim i As Range
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
Kill ThePath & i.Value
Next i
End Sub


Hi Otto and Per

Thanks for your suggestions. Very useful , and solved my problem.

As an extension of this , could a macro be made to Move the file from
Folder A to Folder B , rather than delete it?

Perhaps with a popup requesting source and destination folders?


Thanks again




"Colin Hayes" wrote in message
news
HI All

I have an Excel worksheet with a list of file names in column A.

I'd like to use this list to look into a named directory (perhaps
built
in
to the routine , or entered via a popup) and delete files of the same
name
in turn.

Once a file is deleted , then the routine would go back to Excel and
look
up the next file name in the column for the next delete , and so on
until
it reaches the end of the list.

Can someone help with this , please?

Grateful for any advice.



Best Wishes

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" skrev i meddelelsen
...

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

Hi Per

OK Thanks for that. I tried it out , and it works perfectly. Thanks.

On the points you make :

C - My list of filenames in column A have no file extension. When I run
the routine it does not find them in the source folder.

When I add the file extension , it finds them.

It would be helpful if it ignored files extensions altogether and just
matched on the actual file name. Clearly , when it moves them , it does
need to move the file to the destination folder with extension intact.

Perhaps a .* command could do this.

My list could have hundreds of filenames , and to have to add the
extension before running the routine would be laborious indeed. Best if
it could just ignore extensions completely , if it is possible.

D - Yes it would an idea to clear column B and make wide enough to take
the text.


Thanks again Per - I'm very grateful.



Best Wishes


Colin





In article , Per Jessen
writes
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" skrev i meddelelsen
...

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