Open multiple directories

[vba317]

I am working with Excel 2003. I am trying to run a macro to lookin multiple directories. I get a compile error 'argument not optional. The .SearchFolders gets highlighted.

With Application.FileSearch
        .NewSearch
        If fCount > 0 Then
        
        For fCount = 0 To [COLOR=Red] .SearchFolders [/color Red]

End Select
     
    End With
   Next
              
         'Change path to suit
               
        '.LookIn = "G:\Investments\Summary\Daily"
        '.LookIn = "G:\Investments\Summary\Weekly"
        '.LookIn = "G:\Investments\Summary\Daily"
        For fCount = 0 To .LookIn = "F:\Investments\Summary\Daily"
        For fCount = 1 To .LookIn = "F:\Investments\Summary\Weekly"
        For fCount = 2 To .LookIn = "F:\Investments\Summary\Monthly"
        '.LookIn = "F:\Investments\Summary\Daily"
        .FileType = msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"

 

[PHV]

Your posted code make absolutely no sense !
Anyway:
For fCount = [!]1[/!] To .SearchFolders[!].Count[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Here is all my code. What I am trying to do is every Monday through Friday run the macro to do my updates in the daily folder. Than or either Friday, Saturday or Sunday run the macro on the weekly folder. Than on the first weekday of the month run the macro on the monthly folder.


When I changed the For fCount = 0 To .SearchFolders to .SearchFolders.Count I now get a complile error, For control Variable already in use

Sub Allfilesupdate()
'
' Allfilesupdate Macro
' Macro recorded 3/27/2009 by


Dim ws As Worksheet
Dim R1 As Long
Dim R2 As String
Dim R3 As String
Dim R4 As Long
Dim R5 As String
Dim SR As Long
Dim lCount As Long
Dim fCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False


Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
        
        
        If fCount > 0 Then
        
                
        For fCount = 0 To .SearchFolders.Count
         'Change path to suit
               
        '.LookIn = "F:\Investments\Summary\Daily"
        '.LookIn = "G:\Investments\Summary\Weekly"
        '.LookIn = "G:\Investments\Summary\Daily"
        [COLOR=Red] For fCount = 0 [/Color Red] To .LookIn = "F:\Investments\Summary\Daily"
        For fCount = 1 To .LookIn = "F:\Investments\Summary\Weekly"
        For fCount = 2 To .LookIn = "F:\Investments\Summary\Monthly"
        '.LookIn = "F:\Investments\Summary\Daily"
        .FileType = msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"

            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count 'Loop through all files.
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                 'Call BulkQuotesXL.UpdateData
                 'Start update format
                 For Each ws In Worksheets
                 
                 
        With ws
        .Activate
        
        Select Case .Name
        
      Case "BulkQuotesXL Settings"
       Columns("A").Select
        With Selection
        .Columns.AutoFit
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        End With
       Columns("B:C").Select
        With Selection
        .NumberFormat = "mm/dd/yyyy"
        .Columns.AutoFit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
        Columns("F").Select
         With Selection
         .NumberFormat = "text"
         .Columns.AutoFit
         .VerticalAlignment = xlCenter
         .HorizontalAlignment = xlRight
         End With
         Columns("K").Select
         With Selection
         .NumberFormat = "text"
         .Columns.AutoFit
         .VerticalAlignment = xlCenter
         .HorizontalAlignment = xlCenter
         End With
         Range("A3").Select
       
    Case Else
    
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
        Columns("A").Select
        With Selection
        .NumberFormat = "mm/dd/yyyy"
        .Columns.AutoFit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
    Columns("B:E").Select
        With Selection
        .NumberFormat = "0.00"
        .Columns.AutoFit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
        
    Columns("F").Select
        With Selection
        .NumberFormat = "#,##0"
        .Columns.AutoFit
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlRight
         End With
         
    R1 = ActiveSheet.UsedRange.Rows.Count
    R2 = "F" & R1
    'R3 = "A" & R1
    R4 = R1 - 17
    R5 = "I" & R4
    SR = R1 - 22
    ActiveWindow.ScrollRow = SR
    Range(R2).Select
    
    'Worksheets("wbResults").Cells(ActiveSheet.UsedRange.Rows.Count - 22, 6) = "Current"
    
    
            End Select
     
        End With
   Next
                        
                 'End update format
                 

                 wbResults.Close SaveChanges:=True
                 Next lCount
                    End If
                Next fCount
    End If
            
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

'
End Sub

 

[PHV]

complile error, For control Variable already in use
As clearly stated you can't have 2 nested loops with the same counter ...

 

[vba317]

To fix this problem should I use a different type of procedure? How am I using the same counter?

 

[PHV]

How am I using the same counter?
You have at least FOUR For fCount = !!!!

Tip: have a look here for SmartIndenter add-in:

http://www.oaltd.co.uk/Indenter/Default.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I guess I misunderstood the nature of the next command. I thought when you create a loop if you put the name of the loop (fcount) that when you did next fCount command that the program ignores the other next commands. This is not true?

 

[PHV]

In the debug window (Ctrl-G) type next and press the F1 key.