How to loop through xls files in dated folders

[clym]

each folder is named by date, for example a folder name may be 04-01-2008. Within each folder there is one xls file named daily.xls.
(this never changes)
In date order from the folder name, I need to open each daily.xls run my code, then close daily.xls and then open the next one etc until there are no more.

example path is C:\04-01-2008\daily.xls

Thanks for any help!

 

[Weaver]

Is there a folder for every day?

When do they start from?

 

[clym]

Is there a folder for every day?

When do they start from?

Hi.
yes there is a folder for every day for example:

C:\04-01-2008\daily.xls
C:\04-02-2008\daily.xls
C:\04-03-2008\daily.xls
C:\04-04-2008\daily.xls

starting date is 02-05-2007

 

[Weaver]

Try:

Sub loopfiles()
    Dim startDate As Date
    Dim endDate As Date
    startDate = #5/2/2007#
    endDate = Int(Now())
    For nextDate = startDate To endDate
        Workbooks.Open "C:\" & Format(nextDate, "dd-mm-yyyy") & "\daily.xls"
        With ActiveWorkbook
        'do your thing here
        'for example
        Debug.Print .Name
        End With
        'change to savechanges:=True if you're modifying files
        .Close savechanges:=False
    Next nextDate
End Sub

Sorry I didn't reply sooner - office xmas party!

PS can I ask what you're doing with your files once they're open? Maybe opening them isn't the only solution?
PPS This is untested as I don't have a c drive full of datestamped folders....

 

[clym]

Try:

PS can I ask what you're doing with your files once they're open? Maybe opening them isn't the only solution?
PPS This is untested as I don't have a c drive full of datestamped folders....

Thanks! I almost have this working after making the changes to your work below. I should have mentioned that there are gaps in the dates. I attempted to skip to the next date when the next dated folder does not exist but I have the following line written incorrectly.:
If "C:\" & Format(nextdate, "mm-dd-yyyy") & "\daily.xls" <> "" Then

Sub loopfiles()
    Dim startDate As Date
    Dim endDate As Date
    Dim nextdate As Date
    startDate = #5/2/2007#
    endDate = Int(Now())
    For nextdate = startDate To endDate
    
        If "C:\" & Format(nextdate, "mm-dd-yyyy") & "\daily.xls" <> "" Then
  
                Workbooks.Open "C:\Archive\" & Format(nextdate, "mm-dd-yyyy") & "\daily.xls"
                'mystuff
                Workbooks("daily.xls").Close savechanges:=False
        
        End If
        
    Next nextdate
End Sub

The files that are opened contain data that is used but not changed by another workbook. (Each daily.xls file will be open for about 30 minutes.)

 

[Weaver]

change to:

Sub loopfiles()
    Dim startDate As Date
    Dim endDate As Date
    Dim nextdate As Date
    startDate = #5/2/2007#
    endDate = Int(Now())
    For nextdate = startDate To endDate
        foldername = "C:\Archive\" & Format(nextdate, "mm-dd-yyyy")
        If Dir(foldername) <> "" Then
    
            Workbooks.Open foldername & "\daily.xls"
            'mystuff
            Workbooks("daily.xls").Close savechanges:=False
          
        End If
    Next nextdate
End Sub

Again, not tested on my system

 

[clym]

change to:

Sub loopfiles()
    Dim startDate As Date
    Dim endDate As Date
    Dim nextdate As Date
    startDate = #5/2/2007#
    endDate = Int(Now())
    For nextdate = startDate To endDate
        foldername = "C:\Archive\" & Format(nextdate, "mm-dd-yyyy")
        If Dir(foldername) <> "" Then
    
            Workbooks.Open foldername & "\daily.xls"
            'mystuff
            Workbooks("daily.xls").Close savechanges:=False
          
        End If
    Next nextdate
End Sub

Again, not tested on my system

I tested this and in every case foldername does hold the folder path (for example folderpath = C:\Archive\11-22-2008
but dir(foldername) is always = "" so the code never runs.

 

[Weaver]

I figured it out - the exact problem is that the dir command requires a final "\" on the end of it

so

foldername = "C:\Archive\" & Format(nextdate, "mm-dd-yyyy")

becomes

foldername = "C:\Archive\" & Format(nextdate, "mm-dd-yyyy") & "\"

and

Workbooks.Open foldername & "\daily.xls"

changes to

Workbooks.Open foldername & "daily.xls"

Apologies

 

[clym]

That solves it. Thanks Thanks Thanks!!!

Here is the final code that works.

    Sub loopfiles()
    Dim startDate As Date
    Dim endDate As Date
    Dim nextdate As Date
    Dim foldername As String
    startDate = #04/02/2007#
    endDate = Int(Now())

    For nextdate = startDate To endDate
        foldername = "C:\Archive\" & Format(nextdate, "mm-dd-yyyy") & "\"
        If Dir(foldername) <> "" Then
             Workbooks.Open foldername & "daily.xls"
             Workbooks("daily.xls").Close savechanges:=False
        End If
    Next nextdate
End Sub

 

[Weaver]

That's cool, cos I learned something myself today.