-
Solved: Combine Workbooks based on Date
Good morning
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
Last edited by hepeful; 04-03-2007 at 10:56 AM.
Reason: Solved my issue
-
Hi Hepeful,
Welcome to VBAX
Here's a modification of DRJ's code
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Absolutely perfect! Very easy to follow and I was able to modify for .csv's.
Thanks.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules