Cycling through all worbooks in a folder

I would like to perform a piece of code on every workbook in a folder and
then close it. What would be the best way please. Thank you

This code will give you a starting point:

Sub aa()
Set fso =
CreateObject("Scripting.FileSystemObject").GetFolder("c:\OurFiles\")
For Each file In fso.Files
If file.Type = "Microsoft Excel Worksheet" Then
With file
' do what you want with this file
End With
End If
Next
Set fso = Nothing
End Sub

Note: Replace "C:\OURFILES" by your folder; this code does not recurse the
foldre tree if it has one.

"Ben" wrote:

> I would like to perform a piece of code on every workbook in a folder and
> then close it. What would be the best way please. Thank you

"AA2e72E" wrote:

> This code will give you a starting point:
>
> Sub aa()
> Set fso =
> CreateObject("Scripting.FileSystemObject").GetFolder("c:\OurFiles\")
> For Each file In fso.Files
> If file.Type = "Microsoft Excel Worksheet" Then
> With file
> ' do what you want with this file
> End With
> End If
> Next
> Set fso = Nothing
> End Sub
>
> Note: Replace "C:\OURFILES" by your folder; this code does not recurse the
> foldre tree if it has one.
>
> "Ben" wrote:
>
> > I would like to perform a piece of code on every workbook in a folder and
> > then close it. What would be the best way please. Thank you

That's great but how would I sequentially open the files then perform the
code then close the file

"Ben" wrote:

>
>
> "AA2e72E" wrote:
>
> > This code will give you a starting point:
> >
> > Sub aa()
> > Set fso =
> > CreateObject("Scripting.FileSystemObject").GetFolder("c:\OurFiles\")
> > For Each file In fso.Files
> > If file.Type = "Microsoft Excel Worksheet" Then
> > With file
> > ' do what you want with this file
> > End With
> > End If
> > Next
> > Set fso = Nothing
> > End Sub
> >
> > Note: Replace "C:\OURFILES" by your folder; this code does not recurse the
> > foldre tree if it has one.
> >
> > "Ben" wrote:
> >
> > > I would like to perform a piece of code on every workbook in a folder and
> > > then close it. What would be the best way please. Thank you

Hi Ben,

Maybe you can adapt this to your needs -

Option Explicit

Function FilesToCol(sPath As String, c As Collection) As Long
Dim sFile As String

sFile = Dir(sPath & "\*.xls")
Do While Len(sFile)
c.Add sFile
sFile = Dir()
Loop
FilesToCol = c.Count

End Function

Sub Test()
Dim bWasClosed As Boolean
Dim sFolder As String
Dim col As Collection
Dim i As Long
Dim wb As Workbook

Set col = New Collection

sFolder = "C:\Temp"

If FilesToCol(sFolder, col) = 0 Then
MsgBox "No xls in " & sFolder
Exit Sub
End If

Application.EnableEvents = False
Application.ScreenUpdating = False
ReDim va(1 To col.Count, 1 To 2)

For i = 1 To col.Count

'first test if the file is already open
On Error Resume Next
Set wb = Workbooks(col(i))
On Error GoTo errH

If wb Is Nothing Then
' not open so open it and flag to close later
Set wb = Workbooks.Open(sFolder & "\" & col(i))
bWasClosed = True
End If

' process your files here or pass wb to another procedure
' eg
va(i, 1) = col(i)
va(i, 2) = wb.Worksheets(1).Range("A1").Value

If Not wb.Saved Then
wb.Save
End If

wb.Close
Set wb = Nothing
bWasClosed = False
Next

With Workbooks.Add
.Worksheets(1).Range("A1:B1").Resize(UBound(va)).Value = va
End With

errH:
Application.EnableEvents = True
Application.ScreenUpdating = True

If Err.Number Then MsgBox "Error"

End Sub

This populates an array of file names & values in A1 of the first worksheet,
finally dumps the array into a new workbook.

In my Temp folder I had files with open events, hence I disabled
enableevents in this demo, you might not want to do that.

Regards,
Peter T

"Ben" <Ben@discussions.microsoft.com> wrote in message
news:E6610C06-34A9-4608-B202-B82A0570054E@microsoft.com...
> That's great but how would I sequentially open the files then perform the
> code then close the file
>
> "Ben" wrote:
>
> >
> >
> > "AA2e72E" wrote:
> >
> > > This code will give you a starting point:
> > >
> > > Sub aa()
> > > Set fso =
> > > CreateObject("Scripting.FileSystemObject").GetFolder("c:\OurFiles\")
> > > For Each file In fso.Files
> > > If file.Type = "Microsoft Excel Worksheet" Then
> > > With file
> > > ' do what you want with this file
> > > End With
> > > End If
> > > Next
> > > Set fso = Nothing
> > > End Sub
> > >
> > > Note: Replace "C:\OURFILES" by your folder; this code does not recurse
the
> > > foldre tree if it has one.
> > >
> > > "Ben" wrote:
> > >
> > > > I would like to perform a piece of code on every workbook in a
folder and
> > > > then close it. What would be the best way please. Thank you

I intended to close the wb only if it was not previously open, so change -

> wb.Close

to -

If bWasClosed Then wb.Close

Peter T


"Peter T" <peter_t@discussions> wrote in message
news:#$ic$pBSGHA.1780@TK2MSFTNGP12.phx.gbl...
> Hi Ben,
>
> Maybe you can adapt this to your needs -
>
> Option Explicit
>
> Function FilesToCol(sPath As String, c As Collection) As Long
> Dim sFile As String
>
> sFile = Dir(sPath & "\*.xls")
> Do While Len(sFile)
> c.Add sFile
> sFile = Dir()
> Loop
> FilesToCol = c.Count
>
> End Function
>
> Sub Test()
> Dim bWasClosed As Boolean
> Dim sFolder As String
> Dim col As Collection
> Dim i As Long
> Dim wb As Workbook
>
> Set col = New Collection
>
> sFolder = "C:\Temp"
>
> If FilesToCol(sFolder, col) = 0 Then
> MsgBox "No xls in " & sFolder
> Exit Sub
> End If
>
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> ReDim va(1 To col.Count, 1 To 2)
>
> For i = 1 To col.Count
>
> 'first test if the file is already open
> On Error Resume Next
> Set wb = Workbooks(col(i))
> On Error GoTo errH
>
> If wb Is Nothing Then
> ' not open so open it and flag to close later
> Set wb = Workbooks.Open(sFolder & "\" & col(i))
> bWasClosed = True
> End If
>
> ' process your files here or pass wb to another procedure
> ' eg
> va(i, 1) = col(i)
> va(i, 2) = wb.Worksheets(1).Range("A1").Value
>
> If Not wb.Saved Then
> wb.Save
> End If
>
> wb.Close
> Set wb = Nothing
> bWasClosed = False
> Next
>
> With Workbooks.Add
> .Worksheets(1).Range("A1:B1").Resize(UBound(va)).Value = va
> End With
>
> errH:
> Application.EnableEvents = True
> Application.ScreenUpdating = True
>
> If Err.Number Then MsgBox "Error"
>
> End Sub
>
> This populates an array of file names & values in A1 of the first
worksheet,
> finally dumps the array into a new workbook.
>
> In my Temp folder I had files with open events, hence I disabled
> enableevents in this demo, you might not want to do that.
>
> Regards,
> Peter T
>
> "Ben" <Ben@discussions.microsoft.com> wrote in message
> news:E6610C06-34A9-4608-B202-B82A0570054E@microsoft.com...
> > That's great but how would I sequentially open the files then perform
the
> > code then close the file
> >
> > "Ben" wrote:
> >
> > >
> > >
> > > "AA2e72E" wrote:
> > >
> > > > This code will give you a starting point:
> > > >
> > > > Sub aa()
> > > > Set fso =
> > > > CreateObject("Scripting.FileSystemObject").GetFolder("c:\OurFiles\")
> > > > For Each file In fso.Files
> > > > If file.Type = "Microsoft Excel Worksheet" Then
> > > > With file
> > > > ' do what you want with this file
> > > > End With
> > > > End If
> > > > Next
> > > > Set fso = Nothing
> > > > End Sub
> > > >
> > > > Note: Replace "C:\OURFILES" by your folder; this code does not
recurse
> the
> > > > foldre tree if it has one.
> > > >
> > > > "Ben" wrote:
> > > >
> > > > > I would like to perform a piece of code on every workbook in a
> folder and
> > > > > then close it. What would be the best way please. Thank you
>
>