i FSt1, I'm trying to use your code but I'm getting a "object variable or
with block variable not set" inside the If statement that says "mybook =
Worksheets.count" there are no other "set" commands" Do I need to "set" any
of the Dim'ed variables?

Thanks

"FSt1" wrote:

hi,
not real sure this would work for you but you might be able to play with it...
Sub macUpdateFiles()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim cnt As Long

SaveDriveDir = CurDir
MyPath = "H:\"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
Else
mybook = Worksheets.Count
cnt = mybook.Count
MsgBox ("The Directory contains " & mybook & " files")
End If

Application.ScreenUpdating = False
cnt = 0
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

' your code here

mybook.Close True
cnt = cnt + 1
FNames = Dir()
Loop
MsgBox ("The " & MyPath & " Directory contains " & cnt & " excel files")
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

End Sub

regards
FSt1

"open a adobe file from a command button" wrote:

I have a some code that cleans up a Excel spreadsheet very well. Now I have
20 or 25 spreadsheets that are formatted the same way and I want to clean
them up. How would I go about having this code ran on all the spreadsheets
in a directory? Do I have to do them one at a time?

Thanks