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").GetFold er("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").GetFold er("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").GetFold er("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)).V alue = 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" wrote in message
...
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").GetFold er("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
...
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)).V alue = 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" wrote in message
...
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").GetFold er("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