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
...

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

Does this work if you have the file extention listed in the file name or
spaces in the name? I need to delete .aac & .wma files. Also, can you run
through sub folders? Meaning, I have 200 folders (albums) and I already have
the list, I need to delete the files within the folders.

Directory is: C:\Documents and Settings\My Documents\My Music
Sub Directory: C:\Documents and Settings\\My Documents\My Music\50 Cent
File name: 01 01 Track 1 Unknown Artist Unknown Album (4-24-2008 8-04-25
AM).wma

"Per Jessen" wrote:

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
...

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
...

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
...

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
...

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
...

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
...

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