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

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

Hi there,

You might be able to use something like this ...


Sub LoopThroughFolder()
Dim FSO As Object, fsoFolder As Object, fsoFile As Object
Dim wb As Workbook, blnOpened As Boolean, strFolderPath As String
strFolderPath = "C:\Users\Zack\Documents\Dashboard Kits"
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FolderExists(strFolderPath) Then
MsgBox "The specified folder '" & strFolderPath & "' does not
exist!", vbCritical, "ERROR!"
Exit Sub
End If
Set fsoFolder = FSO.GetFolder(strFolderPath)
For Each fsoFile In fsoFolder.Files
If InStr(1, fsoFile.Type, "Excel") = 0 Then GoTo SkipFile
If WbOpen(fsoFile.Name) = False Then
Set wb = Workbooks.Open(fsoFile.Path)
blnOpened = True
Else
Set wb = Workbooks(fsoFile.Name)
blnOpened = False
End If
Call YourCodeHere(wb) 'the workbook passed as a variable
If blnOpened = True Then
wb.Close savechanges:=False 'True
End If
Set wb = Nothing
SkipFile:
Next fsoFile
End Sub

Function WbOpen(wbName As String) As Boolean
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function

Sub YourCodeHere(ByVal wkb As Workbook)
MsgBox "The workbook being worked on is '" & wkb.Name & "'.",
vbInformation, "INFORMATION"
End Sub


HTH

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"


"open a adobe file from a command button"
osoft.com wrote in
message ...
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

Thank You very very much. I will try to first understand the code, then
apply it. I may be asking questions in the next few days.

Thank you both again!!

"Zack Barresse" wrote:

Hi there,

You might be able to use something like this ...


Sub LoopThroughFolder()
Dim FSO As Object, fsoFolder As Object, fsoFile As Object
Dim wb As Workbook, blnOpened As Boolean, strFolderPath As String
strFolderPath = "C:\Users\Zack\Documents\Dashboard Kits"
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FolderExists(strFolderPath) Then
MsgBox "The specified folder '" & strFolderPath & "' does not
exist!", vbCritical, "ERROR!"
Exit Sub
End If
Set fsoFolder = FSO.GetFolder(strFolderPath)
For Each fsoFile In fsoFolder.Files
If InStr(1, fsoFile.Type, "Excel") = 0 Then GoTo SkipFile
If WbOpen(fsoFile.Name) = False Then
Set wb = Workbooks.Open(fsoFile.Path)
blnOpened = True
Else
Set wb = Workbooks(fsoFile.Name)
blnOpened = False
End If
Call YourCodeHere(wb) 'the workbook passed as a variable
If blnOpened = True Then
wb.Close savechanges:=False 'True
End If
Set wb = Nothing
SkipFile:
Next fsoFile
End Sub

Function WbOpen(wbName As String) As Boolean
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function

Sub YourCodeHere(ByVal wkb As Workbook)
MsgBox "The workbook being worked on is '" & wkb.Name & "'.",
vbInformation, "INFORMATION"
End Sub


HTH

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"


"open a adobe file from a command button"
osoft.com wrote in
message ...
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

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