Batch Delete VB code

[pgarcia]

I found the following code. Does any one know if this will run through the
sub folders? I have around 200 folder and need to delete .m4a files. Thanks

Sub test()
MyPath = "C:\Documents and Settings\My Documents\My Music"
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

 

[Dave Peterson]

How about an alternative?

Open windows explorer.
Traverse to your My Music folder
Search for *.m4a
Include subfolders in your search
after the search is finished
click on the results window (to the right)
Ctrl-a
to select all those files.
hit the delete key on the keyboard.

 

[pgarcia]

Thanks, but the problem is, I had to stop my conver of .m4a to .mp3, so I
have list of .m4a that need to be deleted, but also from that list is .m4a
that have not been conver yet. I need to keep those and conver at a later
time. The conver takes a few days as I have round 35Gb of music.

 

[pgarcia]

Ya, blasphemer!

WHAT!?!?

There's an alternative to using Excel!?!?!

 

[Dave Peterson]

Aren't you afraid that you'll have the same filename used in different
subfolders?

And if you delete by filename alone, you could be deleting the wrong file(s).

I think I'd create a list in excel that includes the path. Then just use

with activesheet
for each mycell in .range("a2",.cells(.rows.count,"A").end(xlup)).cells
on error resume next
kill mycell.value
if err.number <> 0 then
mycell.offset(0,1).value = "Error!"
err.clear
else
mycell.offset(0,1).value = "Deleted"
end if
next mycell
end with

=======
But to answer your original question, no, your code won't look at subfolders.

 

[Dave Peterson]

And I forgot to toggle my error checking:

with activesheet
for each mycell in .range("a2",.cells(.rows.count,"A").end(xlup)).cells
on error resume next
kill mycell.value
if err.number <> 0 then
mycell.offset(0,1).value = "Error!"
err.clear
else
mycell.offset(0,1).value = "Deleted"
end if
on error goto 0 '<-- added
next mycell
end with

 

[pgarcia]

Thanks, yes will I was thinking that it would pick up the extension .m4a. You
are correct on that point. Is there a way to pick up the full path? I used
"Command Prompt" to get the name (C:\Documents and Settings\My Documents\My
Music> dir/s>M4A.txt). It does give you all the information, but on different
lines. Do you know of a VB code that will give me a full list of files name
within a director and sub folder too and put it in column A?

Did I say thanks yet? Thank!

And I forgot to toggle my error checking:

with activesheet
for each mycell in .range("a2",.cells(.rows.count,"A").end(xlup)).cells
on error resume next
kill mycell.value
if err.number <> 0 then
mycell.offset(0,1).value = "Error!"
err.clear
else
mycell.offset(0,1).value = "Deleted"
end if
on error goto 0 '<-- added
next mycell
end with

 

[Dave Peterson]

You could use VBA code, but I use this:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&

In fact, I use this version of the .bat file:

==========

@echo off
REM http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&
dir %1 /-p /b /o:gn /s > "%temp%\Listing.txt"
start notepad "%temp%\Listing.txt"
exit

==========

I created this printdir.bat file in a nice safe folder.

Then I copied the file (using windows explorer) so that I could add a shortcut
to this .bat file to windows explorer rightclick menu.

In WinXP (Home), I could do:
Windows start button|Run
type:
Sendto

And my SendTo folder opens.
For me, it's:
C:\Documents and Settings\(username)\SendTo

Then I rightclicked on an empty spot in that folder and chose to paste shortcut.

Then I could rightclick on any folder in windows explorer and chose Send to,
then PrintDir.

Then I just copy|paste from notepad to what I need--either excel, MSWord, email,
.....

Thanks, yes will I was thinking that it would pick up the extension .m4a. You
are correct on that point. Is there a way to pick up the full path? I used
"Command Prompt" to get the name (C:\Documents and Settings\My Documents\My
Music> dir/s>M4A.txt). It does give you all the information, but on different
lines. Do you know of a VB code that will give me a full list of files name
within a director and sub folder too and put it in column A?

Did I say thanks yet? Thank!

 

[pgarcia]

Oh mannnnnnnnnnnnnn!!!!!!! That was awsom!!!
It worked like a charm. I've made a .bat once, but how does this one work?
What is
REM http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623& about?


Thanks

You could use VBA code, but I use this:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&

In fact, I use this version of the .bat file:

==========

@echo off
REM http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&
dir %1 /-p /b /o:gn /s > "%temp%\Listing.txt"
start notepad "%temp%\Listing.txt"
exit

==========

I created this printdir.bat file in a nice safe folder.

Then I copied the file (using windows explorer) so that I could add a shortcut
to this .bat file to windows explorer rightclick menu.

In WinXP (Home), I could do:
Windows start button|Run
type:
Sendto

And my SendTo folder opens.
For me, it's:
C:\Documents and Settings\(username)\SendTo

Then I rightclicked on an empty spot in that folder and chose to paste shortcut.

Then I could rightclick on any folder in windows explorer and chose Send to,
then PrintDir.

Then I just copy|paste from notepad to what I need--either excel, MSWord, email,
.....

 

[pgarcia]

Ah, you wouldn't mind look at this post, would you? Thanks if you do:
Subject: Lotus Notes VB code (yes, it works)

You could use VBA code, but I use this:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&

In fact, I use this version of the .bat file:

==========

@echo off
REM http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&
dir %1 /-p /b /o:gn /s > "%temp%\Listing.txt"
start notepad "%temp%\Listing.txt"
exit

==========

I created this printdir.bat file in a nice safe folder.

Then I copied the file (using windows explorer) so that I could add a shortcut
to this .bat file to windows explorer rightclick menu.

In WinXP (Home), I could do:
Windows start button|Run
type:
Sendto

And my SendTo folder opens.
For me, it's:
C:\Documents and Settings\(username)\SendTo

Then I rightclicked on an empty spot in that folder and chose to paste shortcut.

Then I could rightclick on any folder in windows explorer and chose Send to,
then PrintDir.

Then I just copy|paste from notepad to what I need--either excel, MSWord, email,
.....

 

[Dave Peterson]

REM
is the bat file indicator for REMark. (You can use it in VBA, too, but most
people comment lines with an apostrophe).

And my memory isn't very good. So instead of searching for the link in one of
my notes files, I put a comment in the .bat file where the technique came from.
If I screw something up, I can find the original source easier.

This isn't a bad idea for any code that you found via a google search. It may
help you or the person borrowing, er, reviewing the code later.

If you shell to DOS:
Windows start button|Run
type
cmd
and hit enter

You can use:
dir /?

to see all the options that you can use with these old DOS commands.

In WinXP Home, I saw this:

C:\>dir /?
Displays a list of files and subdirectories in a directory.

DIR [drive:][path][filename] [/A[[:]attributes]] [/B] [/C] [/D] [/L] [/N]
[/O[[:]sortorder]] [/P] [/Q] [/S] [/T[[:]timefield]] [/W] [/X] [/4]

[drive:][path][filename]
Specifies drive, directory, and/or files to list.

/A Displays files with specified attributes.
attributes D Directories R Read-only files
H Hidden files A Files ready for archiving
S System files - Prefix meaning not
/B Uses bare format (no heading information or summary).
/C Display the thousand separator in file sizes. This is the
default. Use /-C to disable display of separator.
/D Same as wide but files are list sorted by column.
/L Uses lowercase.
/N New long list format where filenames are on the far right.
/O List by files in sorted order.
sortorder N By name (alphabetic) S By size (smallest first)
E By extension (alphabetic) D By date/time (oldest first)
G Group directories first - Prefix to reverse order
/P Pauses after each screenful of information.
/Q Display the owner of the file.
/S Displays files in specified directory and all subdirectories.
/T Controls which time field displayed or used for sorting
timefield C Creation
A Last Access
W Last Written
/W Uses wide list format.
/X This displays the short names generated for non-8dot3 file
names. The format is that of /N with the short name inserted
before the long name. If no short name is present, blanks are
displayed in its place.
/4 Displays four-digit years

Switches may be preset in the DIRCMD environment variable. Override
preset switches by prefixing any switch with - (hyphen)--for example, /-W.

=========
So...

The first line:
@echo off
stops you from seeing the commands as the bat file executes. Much like
application.screenupdating = false in VBA.

The next line.

dir %1 /-p /b /o:gn /s > "%temp%\Listing.txt"

%1 is the folder that you rightclicked on.
/-p says not to pause when the screen is filled up
/b says no header and no summary
/o:gn says order it by directory, then name
/s says include subfolders

is the redirection symbol.

In this case, the output is being redirected to your windows temp folder to a
file named listing.txt.

The next line just starts up notepad so that you can see the output.
start notepad "%temp%\Listing.txt"

The last line:
exit
just closes the cmd window.

You could do this all from the cmd prompt if you wanted, but using the
rightclick|sendto makes it soooo much easier.

Personally, I think being able to do it without using excel is an added benefit.

Oh mannnnnnnnnnnnnn!!!!!!! That was awsom!!!
It worked like a charm. I've made a .bat once, but how does this one work?
What is
REM http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623& about?

Thanks

 

[Dave Peterson]

I'll look, but I've never used Lotus Notes.

Ah, you wouldn't mind look at this post, would you? Thanks if you do:
Subject: Lotus Notes VB code (yes, it works)

 

[Don Guillett]

Do you know of a VB code that will give me a full list of files name

within a director and sub folder too and put it in column A?

I sent you one privately a couple of days ago.

Sub FindFilesONLY()
'change to suit
mypath = "c:\A_D\BoneyJames"
'=====
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = mypath
.SearchSubFolders = True 'False
.MatchTextExactly = False
.Filename = ".mp3" '*.mp3* did not work in 97
If .Execute(msoSortOrderDescending) > 0 Then

MsgBox "There were " & .FoundFiles.Count & " file(s) found."

For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Next i

End If

End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks, yes will I was thinking that it would pick up the extension .m4a.
You
are correct on that point. Is there a way to pick up the full path? I used
"Command Prompt" to get the name (C:\Documents and Settings\My
Documents\My
Music> dir/s>M4A.txt). It does give you all the information, but on
different
lines. Do you know of a VB code that will give me a full list of files
name
within a director and sub folder too and put it in column A?

Did I say thanks yet? Thank!