loops to kill files

[smakatura]

I am new to loops inside VBA. but I do understand the concept behind them.

Situation:

I have a list of folders: (will call this variable: foldername)
c:\documents\FolderA
c:\documents\FolderB
c:\documents\FolderC

every weekdday I save a folder inside each of the folders with the date as the name of the folder. (daily dated reports). there are no dated folders for the weekends and holidays. (so not consitant to have all days of 1-31)

in FolderA there is a folder 6/01/11, 6/02/11, 6/03/11... (will call These variables rptmonth/rptdate/rptyear). each of the other folders has similar folders (6/01/11....)

In reality there are 30 folders but I am simplifying so that I can get the basics of the coding.

on 7/1, I manually go in and delete all entries from June from these files....this is done on a monthly basis. I would like to automate this with a macro.


Prior to doing the following questioned items, I know I need to (and know how to)

• do the dim statements as strings and integers depending on the variable,
• do an imput box to define rptmonth as whatever the current month number is,
• do an imput box to define rptyear as the current year

Then I want to (but this is where the details get fuzzy and I don't know the syntax to use)

1. open "c:\documents\FolderA" (the first folder on the list - first foldername)
2. kill "c:\documents\FolderA\" & rptmonth & "/" & rptday & "/" & rptyear"
3. do rptday = rptday+1
4. repeat 2 and 3 until rptday = 31
5. do rptday = 1
6. open ""c:\documents\FolderB" (the next folder on the list) ...ie foldername = foldername +1 when looking at the list
7. repeat 2-6 until all folders in the list have had their files deleted

I know the names of the main folders FfolderA, Folder B...) and there is a finite number of them....so I can define the list ahead of time.

due to there not being a report on every day, I know that somewhere an onerror message will need to be used. (I have used those on other things)

 

[SillyCat]

I start you off with the help for this. Deleting all those folders of a month withing other folders I would just use the command line like so:

for /f %i in ('Dir /B /AD "C:\documents"') Do For /f %j In ('Dir /B /AD "C:\documents\%i\[B]1[/B]-*"') Do ( rmdir /S /Q "C:\documents\%i\%j")

Where C:\documents is the folder name all your folders are under and you want to delete January and your folder names are m-dd-yy (I can't make folders with / in them on the version of windows I'm running here)

So to run that from within VBA use Shell()

Dim foldername As String
Dim month As Integer
Dim returnVal

month = 1 ' I am deleting January
foldername = "C:\documents\" 

returnVal = Shell("cmd.exe /C ""for /f %i in ('Dir /B /AD """ & foldername & """') Do For /f %j In ('Dir /B /AD """ & foldername & "\%i\" & month & "-*""') Do ( rmdir /S /Q """ & foldername & "\%i\%j"""")", 6)

Now this will delete all folders than match m-dd-yy for the given month which are in every folder within C:\documents\ so both C:\documents\FolderA\6-23-11 and C:\documents\backups\6-30-11 ! either ensure you only have your thirty folders in there or if the have a consistant naming pattern add it to the first For loop e.g. using your sample names from this:

for /f %i in ('Dir /B /AD "C:\documents"') Do ...

to:

for /f %i in ('Dir /B /AD "C:\documents\Folder?"') Do ...

In terms of automation from your description of how you manually do it I would have a write a macro that checks if it is the beginning of the month if so sets runs the Shell command for the previous month.

Finding out the current Day Month Year is simple:

Dim currentDay, currentMonth, currentYear As Integer

currentDay = Day(Date) ' 1..31
currentMonth = Month(Date) ' 1..12
currentYear = Year(Date) '  2011

Hope this starts to help you.

 

[smakatura]

I may not be understanding properly and not sure if I portrayed the information properly.

every weekday I save a set of files into a folder (DailyFolder). format for the name is 06-01-11, 06-02-11.... these dailyfolders get saved into a directoryfolder (c:\documents\folder A, c:\documents\folder B.....)

on 7/1 I want to delete all the daily folders that are dated for May. this means there are still documents in folderA that are for June, those need to stay.

the instructions you gave makes it look like it deletes everything inside folder A, and that wont work.

I was thinking something like a loop that might do the following

' define variables
Dim rptmonth as integer
dim rptday as integer
dim rptyear as integer
dim foldername as string
dim filename as string
 
 
'get data from user
rptmonth = inputbox("what is the report month")
rptyear = inputbox("what is the report year")
foldername = "c:\documents\"
rptday = 1
 
 
'define array of filenames
 
not sure how to do this part but need to have it realize that filename is a list of  folderA, FolderB, Folder C, FolderE....  so that when I say filename = filename +1 it goes to the next name on the list
 
 
'Kill Loop
Do
   Do
      kill("foldername & filename & "\" & rptmonth & "\" & rptday & "\" &
         rptyear"
      rptday = rptday +1
   Until
      rptday = 32
   filename = filename +1
Until 
   filename is empty (ie all filenames have been worked)

I know there needs to be an on error somehwere in there bc there will be days missing inthe month (we don't run reports on the weekends). the italisized part are where i have no idea the syntax to use.

 

[SillyCat]

The command I wrote will, for a given month, delete every mm-dd-yy folder with the correct month. So if I break the line of code down for you:

for /f %i in ('Dir /B /AD "<foldername>"') Do For /f %j In ('Dir /B /AD "<foldername>\%i\<month>-*"') Do ( rmdir /S /Q "<foldername>\%i\%j")

(Note I've replaced where your varibles are going to be in VBA with <foldername> and <month> for legibillity it isn't a valid dos command.

We have two for loops inside each other. The first is just working throught each folder within foldername e.g. Folder A, Folder B etc. and putting it into the variable %i

The second one deletes every folder within %i (e.g. Folder A then Folder B etc.) That starts with <month>- so for June in effect folders that match '6-*'

Which I think/hope is what you were after?

 

[smakatura]

So that I understand. it will go through all folders inside c:\documents and remove all folders that start with 1 and have the format m-dd-yy? correct

how will that be affected with the folder mm-dd-yy (so month is 01-12) are in folders that are named 010, 015, 120?

 

[SillyCat]

Sorry the message board interpreted some of my text inside the code block as HTML and stripped it out and it is too late to edit:
(So I've replaced my < and > with #

for /f %i in ('Dir /B /AD "#foldername#"') Do For /f %j In ('Dir /B /AD "#foldername#\%i\#month#-*"') Do ( rmdir /S /Q "#foldername#\%i\%j")

(Note I've replaced where your varibles are going to be in VBA with #foldername# and #month# for legibility it isn't a valid dos command.

If you don't get it now that it is actually there and not mucked up by the board let me know and I'll walk you through it in detail.