Macro to open every file in a folder (Excel 2002)

Topic

New Reply

Hi,
I would like my macro below to have a loop, having it open every file in the folder one at a time, then copy some of that opened file’s data into the summary file, then close the file and open the next file and copy some of its’ data to the summary file, etc. I’m unsure on how to make the individual file names variable… all the files will start with “ProWellness Walking Campaign-” and then it will have each person’s name attached to the end of the file name.. I’m not sure that that is even important as I want every file in the folder to be opened (regardless of it’s file name), however they need to be opened one at a time, thus the looping thingy. As always, your help is very much appreciated!
Thanks!!
Lana

Sub Consol()
‘Open the first file in the folder and retreive the goal & actual data
ChDir “K:GroupsWellness”
Workbooks.Open Filename:= _
“K:GroupsWellnessProWellness Walking Campaign-Lana.xls”

‘Copy & Paste the Goals worksheet to the Summary file
Windows(“ProWellness Walking Campaign-Lana.xls”).Activate
Sheets(“Goal”).Select
Range(“A7:K16”).Select
Selection.Copy

Windows(“ProWellness Walking Campaign-Summary.xls”).Activate
Sheet2.Select
Range(“A65536”).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

‘Copy & Paste the Actual worksheet to the Summary file
Windows(“ProWellness Walking Campaign-Lana.xls”).Activate
Sheets(“Actual”).Select
Range(“A15:K24”).Select
Application.CutCopyMode = False
Selection.Copy

Windows(“ProWellness Walking Campaign-Summary.xls”).Activate
Sheet2.Select
Range(“A65536”).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Windows(“ProWellness Walking Campaign-Lana.xls”).Activate
ActiveWindow.Close

End Sub

Reply | Quote

Replies

You can copy the following code into a module in the ProWellness Walking Campaign-Summary.xls workbook:

Sub Consol()
Const strPath = “K:GroupsWellness”
Dim strFile As String
‘ Source
Dim wbkS As Workbook
Dim wshS As Worksheet
‘ Target
Dim wbkT As Workbook
Dim wshT As Worksheet

‘ No display is more efficient
Application.ScreenUpdating = False

‘ Target workbook
Set wbkT = ThisWorkbook
‘ Sheet – supply correct name
Set wshT = wbkT.Worksheets(“Summary”)

‘ Name of first source workbook
strFile = Dir(strPath & “*.xls”)
‘ Loop
Do While Not strFile = “”
‘ Open workbook
Set wbkS = Workbooks.Open(strPath & strFile)
‘ Goal worksheet
Set wshS = wbkS.Worksheets(“Goal”)
‘ Copy range
wshS.Range(“A7:K16”).Copy
‘ Paste
wshT.Range(“A65536”).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
‘ Actual sheet
Set wshS = wbkS.Worksheets(“Actual”)
‘ Copy range
wshS.Range(“A15:K24”).Copy
‘ Paste
wshT.Range(“A65536”).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
‘ Close source workbook
wbkS.Close SaveChanges:=True
‘ Name of next workbook
strFile = Dir
Loop

‘ Clean up
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Notes:
– The ProWellness Walking Campaign-Summary.xls workbook itself should NOT be stored in the K:GroupsWellness folder, because then the code would try to open it too.
– You must substitute the name of the target sheet in the line

Set wshT = wbkT.Worksheets(“Summary”)

Reply | Quote