Excel Opening Multiple Workbooks in a folder

[vaneagle]

Is it possible to creat a script where the script identifies how many excel files are in a particular folder, opens the first workbook, copies the info specified by a range selection, closes that workbook, copies the range into a pre-formatted workbook, then systematically goes through every other file in the folder performing the same process until it has copied the information required?

 

[thebigcheese]

vaneagle, below is some code that I have used in the past, however I have modified the original code to make it more generic for you.

I have used this to consolidate an organisations budget files (over 500)

This method assumes you have one WB in which you store the code. A template file somewhere and a bunch of source datafiles.

Give it a try!



'VARIABLE DECLARATION
Dim strSchDir As String ' Declare Variable for Data Directory
Dim strTplDir As String ' Declare Variable for Template Directory
Dim strOutDir As String ' Declare Variable for Output Files Directory
Dim ThisFile As String ' Declare Variable for the name of this file

Sub MergeFiles()

'SET CURRENT FILENAME
ThisFile = ActiveWorkbook.Name

' OPEN FILE TEMPLATE AND ACTIVATE WORKSHEET NAMED DATA
' (This relies on a valid directory for the template.xls spreadsheet)

strTplDir = "c:\DataDirectory"
Workbooks.Open Filename:=strTplDir & "\template.xls", ReadOnly:=True
Worksheets("Data&quot.Activate

'GO TO CELL A2 (READY TO APPEND DATA)

Range("A2&quot.Select

'SET DIRECTORY LOCATION FOR DATA FILES AND TEMPLATE FILE
Windows(ThisFile).Activate
strSchDir = "c:\DataDirectory"
strOutDir = "c:\DataDirectory"

ChDir (strSchDir)
Datafile = Dir(strSchDir & "\*.xls&quot

'LOOP THROUGH EACH DATA FILE AND OBTAIN DATA

Do While Datafile <> &quot;&quot;
If (Datafile <> ThisFile) And (Datafile <> &quot;template.xls&quot Then

Workbooks.Open Filename:=Datafile
'Application.StatusBar = &quot;Reading School Files &quot; & &quot;\..\&quot; & Datafile

'Process Each Cashflow Worksheet

For Each Worksheet In Sheets
If Worksheet.Name = &quot;Sheet1&quot; Then

' Get Base Budget Data From Budget Spreadsheets
Windows(Datafile).Activate
Worksheet.Range(&quot;data&quot.Select
Selection.Copy

'Place Data in Budget Data Row
Windows(&quot;template.xls&quot.Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Move to Next Available Cell Ready to Repeat
ActiveCell.Offset(1, 0).Select
Windows(Datafile).Activate

End If
Next Worksheet
ActiveWorkbook.Close SaveChanges:=False
End If
Datafile = Dir()
Loop

Application.ScreenUpdating = False


'INVOKE SAVE AS DIALOG TO PROMPT FILE SAVE

ChDir (strOutDir)
Application.StatusBar = &quot;Save Your New Consolidated File&quot;
Application.Dialogs(xlDialogSaveAs).Show
'Close Workbook
ActiveWorkbook.Close

'RETURN TO UTILITY FILE

Windows(ThisFile).Activate

Application.StatusBar = oldStatusBar

Exit Sub


End Sub

 

[vaneagle]

Thanks..

I'll give it a go...