PDA

View Full Version : Combine Workbooks based on Date



hepeful
04-03-2007, 05:41 AM
Good morning :hi:

OK, I've read and used DRJ's code for "Combining All Workbooks From One Folder" and this works great. I need to look at 17 different spreadsheets daily so this minimizes the time, however the 17 workbooks are created daily (i.e. the folder that the combine macro looks at, now has over 100 spreadsheets). The 17 spreadsheets are run from an external system and placed in a shared folder. We keep the last 10 runs, but I only want to look at todays run. Is it possible show all of these as worksheets under one workbook for the current day? Something like, if date is equal to current day THEN combine.

Please help me save time, my only option is to cut/paste these into a different folder each day and while I can do this, it defeats the "automation" of using a macro.

Thanks

mdmackillop
04-03-2007, 10:10 AM
Hi Hepeful,
Welcome to VBAX
Here's a modification of DRJ's code
Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Dim FileSpec As String
Dim fs, f, s

Set fs = CreateObject("Scripting.FileSystemObject")

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\AAA" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
FileSpec = Path & "\" & FileName
Set f = fs.GetFile(FileSpec)
'MsgBox FileSpec & vbCr & Int(f.DateLastModified) & vbCr & Date
If Int(f.DateLastModified) = Date Then
Set Wkb = Workbooks.Open(FileName:=FileSpec)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

hepeful
04-03-2007, 10:58 AM
Absolutely perfect! Very easy to follow and I was able to modify for .csv's.

Thanks.