Need help with Excel VBA to get file names and dates in folder

[DougP]

I have to keep track of teacher weekly lesson plans. these are stored in various folders such as
Grade1\2010-11\
Grade2\2010-11\
etc

the names of the file changes from folder to folder but the date the file was last saved should fall between: say Monday - Friday of that week.
the week date is a cell in Excel. so I want to march through the cells to get the date value, then look in the Grade1\2010-11\
folder for any file whose date is in the range.
Example:
B17 cell has the date 8/23/2010
code needs to look in folder Grade1\2010-11\
for a file with a date in the range of 8/23/2010 +6 (or 8-29-10)
If we find a file set cell B18 to a "Y" if not set it to "N".
then repeat for all cells from C17 to AM17 ( this is every week in the school year.
then move to the next cell D17 which has a value of 8-30-10 and find a file between date 8-30-10 +6.
etc
etc
so it should look something like this when done

cell B17 C17 C18 ....
Date 8-23 8-30 9-6 .... <cells have date already
Grade1 Y Y N .... < need "Y" or "N"
Grade2 N Y Y .... < need "Y" or "N"
etc

TIA

DougP

 

[SkipVought]

Hi,

What code do you have so far?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

and what is the FULL PATH?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DougP]

Sorry the idea won't work, since some of the teachers post several weeks on the same date and some of them post a week before or weeks later 'cause they forgot one. I was debugging it and the date in the cell was 8-30-10 that weeks plan in the folder was dated 8-23-10, way out of range.
Monday was the beginning of the week and the 23rd was the Monday before school even started.
I did fiddle with it and found that out.

Or maybe you'all have another idea?

next fall I might suggest they spell them all similar i.e. Come up with a naming convention. like 083111.doc or something so the date is part of the file name. right now the names are different from week to week /month to month and even more so from teacher to teacher.

they're in a folders called
S:\lessonplans\Grade1\2010-11
S:\lessonplans\Grade2\2010-11
...
...



DougP

 

[SkipVought]

Do you really care what's in the file name?

Don't you really want to key off the date in some cell on sheet1?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DougP]

Well, yes, the date in the cell is the Mondays Date for a particular week the lesson plan is for. The name of the file has some sibilance of that date in it. say teacher one names theirs LessonPlan8-23.doc
Teacher2 uses 8-23-8-27.doc
teacher 3 might call it BookPlanWeekof8-23 to 8-28
the names are all over the place. So I thought the file date would be a good to use even if they didn't do it on the date it was due.
but they have laptops they take home so the dates of the files are as random as the names. They are studying something for 2-3 weeks in a row so the lesson plan is the same and they do save as 3 times and rename it.
I took over this job in December '10. they had no IT person or any thought of how to structure file names, or anything. so when they ask me to do something I try to steamline it.
I need to come up with Policies and Procedures for how to do things.

So I'll just let this slide for now OK,

case closed.

Thanks though



DougP

 

[SkipVought]

No need to know any specific file name!

I believe that a general date range may meet your criteria. Just test the internal date as specified.

    Dim oFSO As Object, oFile As Object, sPath As String, i As Integer
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    For i = 1 To 12
        sPath = "S:\lessonplans\Grade" & i & "\2010-11"
        
        For Each oFile In oFSO.GetFolder(sPath).Files
            With oFile
                If .DateLastModified >= Date - 30 Then  '[b]open files modified within past 30 days[/b]
                    With Workbooks.Open(.Path)
                        'get stuff from this workbook if it passes muster
                        
                        .Close False
                    End With
                End If
            End With
        Next
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}