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"
<(E-Mail Removed)> wrote in
message news951E85A-4138-4697-9D3F-(E-Mail Removed)...
>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"
> <(E-Mail Removed)> wrote in
> message news951E85A-4138-4697-9D3F-(E-Mail Removed)...
> >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
> >