Cycling through all worbooks in a folder
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
|