Excel 2003 Macro: Aggregating data across several different workbooks
I wanted to create a macro using excel 2003 for a group of excel files where I can aggregate only the data I need to see into one s/s; the only problem is that I have no idea where to start. I am using a directory at work where I save down about 15-20 excel s/s' into a daily date labeled folder. To expedite our daily process of having to look into each s/s, search for the correct tab (which sometimes doesn't exist for that particular day) in the workbook, sort by data filter, and check it against list of identification codes; I would much rather create this in a macro. So I am looking for the macro to open today's dated folder in the directory, find the correct labeled tab (let's call it NYK123), open each file to match the id codes (in column H) and department codes (in column J) against the table I have on the original s/s, and display the results on the original s/s. Also new issues have the entire rows font is red and need to be displayed in the original s/s. Any help you guys can provide would be much appreciated.
To start you going:
Dim oFSO As New Scripting.FileSystemObject Dim fsoFile As Scripting.file Dim fsoFolder As Scripting.Folder Dim sReportsFolder As String: sReportsFolder = "C:\temp\" Set fsoFolder = oFSO.GetFolder(sReportsFolder) For Each fsoFile In fsoFolder.Files Dim xlWorkbook As New Workbook Set xlWorkbook = Workbooks.Open(fsoFile.Name) Dim xlWorksheet As Worksheet Set xlWorksheet = xlWorkbook.Sheets("Sheet1") ' put your logic here... Next
WARNING: written from memory, should compile