How to I create a macro that opens every excel file in a

[mrissi]

directory then runs a nested macro, closes the file and opens the next file in the same directory.

I have the sub macro written and I have the close and save done; But I can't figure out how to open each file.

Any help would be appreciated!

Thanks!

 

[Tom Urtis]

Something like this might help. Modify for path declaration, and insert your existing macro code where indicated.

Sub AllFiles()
Application.ScreenUpdating = False
Dim myFile As String, myPath As String
myPath = "C:\Your\File\Path\Name\"
myFile = Dir(myPath & "*.xls")
Do While myFile <> ""
Workbooks.Open myPath & myFile
myFile = Dir()

'Your macro code goes here

ActiveWorkbook.Close True
Loop
Application.ScreenUpdating = True
End Sub

 

[mrissi]

Thanks, I think we are getting there, but this just keeps opening the first workbook in the list over and over again. Any suggestions?

 

[Bruno_x]

From EXCEL HELP :

' DIR : Returns filename with specified extension.
'If more than one *.ini
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next *.INI file in the
' same directory.
MyFile = Dir

Similar :

Sub AllFiles() 
Application.ScreenUpdating = False 
Dim myFile As String, myPath As String 
myPath = "C:\Your\File\Path\Name\" 
myFile = Dir(myPath & "*.xls") 
Do While myFile <> "" 
Workbooks.Open myPath & myFile 
myFile = Dir

'Your macro code goes here 

ActiveWorkbook.Close True 
Loop 
Application.ScreenUpdating = True 
End Sub

[/b]

 

[mrissi]

I must be doing something wrong -- it keeps just opening the first file. It also won't close the first file, just keeps trying to re-open it.

Sub AllFiles()
Application.ScreenUpdating = False
Dim myFile As String, myPath As String
myPath = "D:\Total Sale Value Output All Stores\"
myFile = Dir(myPath & "*.xls")


Do While myFile <> ""
Workbooks.Open myPath & myFile
myFile = Dir




Range("e1").Select
ActiveCell.FormulaR1C1 = "MaxAsOfDate"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "WeekDay"
ActiveCell.Columns("A:A").EntireColumn.Select
ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, -1).Columns("A:B").EntireColumn.Select
Selection.NumberFormat = "mm/dd/yy"
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "General"
ActiveCell.Offset(2, -5).Range("A1").Select


If IsEmpty(ActiveCell) Then Exit Sub

' Begin loop.
Do

ActiveCell.Offset(0, 4).Select

ActiveCell.FormulaR1C1 = ""
ActiveCell.FormulaR1C1 = "=IF(RC[-4]<>"""",MAX(C[-4]),"""")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]<>"""",WEEKDAY(RC[-1],1),"""")"


' Steps down one row to the next cell.
ActiveCell.Offset(1, -5).Select

' Test contents of active cell; if empty, exit loop
' or Loop While Not IsEmpty(ActiveCell).

Loop Until IsEmpty(ActiveCell)

ActiveWorkbook.Close savechanges:=True

Loop
Application.ScreenUpdating = True


End Sub

 

[NateO]

Howdy Mike,

I must be doing something wrong -- it keeps just opening the first file. It also won't close the first file, just keeps trying to re-open it.

A slightly different approach:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> OpnFiles()
<SPAN style="color:darkblue">Dim</SPAN> i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>, cl <SPAN style="color:darkblue">As</SPAN> Range, wb <SPAN style="color:darkblue">As</SPAN> Workbook
<SPAN style="color:darkblue">With</SPAN> Application.FileSearch
    .NewSearch
    .LookIn = "D:\Total Sale Value <SPAN style="color:darkblue">Output</SPAN> All Stores\"
    .Filename = ".xls"
    <SPAN style="color:darkblue">If</SPAN> .Execute() > 0 <SPAN style="color:darkblue">Then</SPAN>
        Application.ScreenUpdating = 0
        <SPAN style="color:darkblue">For</SPAN> i = 1 <SPAN style="color:darkblue">To</SPAN> .FoundFiles.Count
            <SPAN style="color:darkblue">Set</SPAN> wb = Workbooks.Open(.FoundFiles(i))
            <SPAN style="color:darkblue">With</SPAN> wb.Sheets(1)
                .[e1:f1].Value = [{"MaxAsOfDate","WeekDay"}]
                .[A:A,e:f].EntireColumn.AutoFit
                .[a:b].NumberFormat = "mm/dd/yy"
                .[c:c].NumberFormat = "General"
                <SPAN style="color:darkblue">Set</SPAN> cl = .[a65536].End(3)
                <SPAN style="color:darkblue">With</SPAN> .Range("e3", cl(, 5))
                    .Formula = "=IF(RC[-4]<>"""",MAX(C[-4]),"""")"
                    .Value = .Value
                <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
                <SPAN style="color:darkblue">With</SPAN> .Range("f3", cl(, 6))
                    .Formula = "=IF(RC[-5]<>"""",WEEKDAY(RC[-1],1),"""")"
                    .Value = .Value
                <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
                <SPAN style="color:darkblue">Set</SPAN> cl = <SPAN style="color:darkblue">Nothing</SPAN>
            <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
            wb.<SPAN style="color:darkblue">Close</SPAN> True: <SPAN style="color:darkblue">Set</SPAN> wb = <SPAN style="color:darkblue">Nothing</SPAN>
        <SPAN style="color:darkblue">Next</SPAN> i
        Application.ScreenUpdating = 1
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

I was having trouble discerning exactly what you are trying to do here. Can't figure out why these formulae should fill as such, seems duplicative...But possibly a start... Hope this helps.

 

[Tom Urtis]

Just woke up in California.

mrissi - - my code tested fine before I posted it; the problem must be somewhere in what your macro is doing. I didn't go through your code step by step because if Nate's works for you then so much the better...as you can see there is more than one way to code your request. Post back if you still have problems.

Thanks for the follow-up Nate - - really exciting playoff season for the Wild !! For a while I thought they might pull off a Cinderella dance in June.

 

[mrissi]

Thanks guys that worked like a charm. I really appreciate your help!!! If I can ever reutrn the favor, please do not hesitate to email me directly. :D

 

[NateO]

Hello again, yes indeed, a few ways to skin this cat.

You're welcome. Glad to hear you're up and running.

Yes indeed Tom, although we came up really short against Disney on Ice, hard to figure out after having watched them take out Colorado and Vancouver... Still, good stuff for a young team/franchise. Are you a Ducks or Sharks fan? I would assume the latter...

Have a good one y'all.

 

[mrissi]

Must be nice to live in Southern California right now, Oakland, The Ducks and the Angels all in the respective finals.... I, being from Philadelphia, have not expirienced the joy of winning... anything.... at all... in the last 23 years....