(Office 2007 users! Take a break. The code on this page will not work for you. Several complex code alternatives exist. We are looking for a simpler solution to present our Office 2007 users).
After going through all of the set up for loops for one workbook, one of two things is very likely happening. You are really wanting to go the next step to find out how to do multiple files, or you are REALLY wanting to call it a day. Well, this is a good place to rest. To do the looping of files, we need to add more overhead.
For instance, we have to know where to look for the files. That’s another variable. Since we’re reading from a whole lot of field reps’ files, we’ll probably want to store the field rep name along with his or her data.
So, if you’re eager, carry on. If you’re not quite sure, maybe we’ll see you back here tomorrow - be sure to bookmark this page!
We need a way of building an uber-loop, a way of reading all of the files in a particular folder. This requires some discipline on the part of the analyst, though. Each week, when the reports are emailed in, the analyst has to make a new folder for the week with the date as a part of the folder name, or delete ALL of the old files and add all of the new ones.
Our program can be told to read through a file, close it, and read the next file just like it does rows and columns. BUT, you must be careful not to include duplicates (somebody always wants to make a change and resend their file…), not to put files from different weeks into the same folder, and you must be sure that you have all of the files before you start!
Other overhead: We’ll make a special Excel file that doesn’t have anything in it but the macro and place that in a folder just above the weekly folders. In other words, we place an Excel file called ReportReaderMacro.xls in a folder called C:\FieldReports. That way, the macro file is never read as a data file. Have a look at the file structure.
Let’s see how to set up the master macro file. We need to know where our macro file lives, and where the field rep files will be. We created a folder called C:\FieldReports and the macro file and all of the consolidated report files will be in this folder. If you intend to delete all of the old files every week and put the new files into the now-empty folder, you can make a small change to the code. Still, the macro file lives with the consolidated reports in one folder and the individual files live in a folder below.
This code is written so that wherever you decide to store the macro file, whether on your own C:\ drive or somewhere on the network, the macro recognizes the location.
MasterFilePath = ActiveWorkbook.Path
The next line then looks for files in a folder below this path. You can choose between the two lines of code based on whether or not you want to keep archival copies of field reports (first line) or not (second line).
ThisWeeksFilesPath = ActiveWorkbook.Path & "\" & _ Format(Date, "yyyymmdd") OR ThisWeeksFilesPath = ActiveWorkbook.Path & "\FieldReportFiles"
ThisWeeksFilesPath = ActiveWorkbook.Path & "\" & _ Format(Date, "yyyymmdd")
ThisWeeksFilesPath = ActiveWorkbook.Path & "\FieldReportFiles"
We also add a column header so that the field rep name is added to each line. This helps us if we later want to run reports based on employee or region. Our code is written to pull the employee name from the name of the Excel file with that rep’s report.
You may decide to modify the report to include employee id, region, or other information in the report itself. Use the code as written as a guide for adding column headers and reading the data from the Excel file. You can see in the final code that the following lines have been added in the appropriate places.
MasterWorkbook.Sheets(1).Cells(1, 4) = "Field Rep" RepName = Left(SourceWorkbook.Name, _ InStr(1, SourceWorkbook.Name, ".") - 1) MasterWorkbook.Sheets(1).Cells(FirstBlankRow, 4) = RepName
For the loop itself, let’s look at built-in feature of Microsoft Office. This is not really a part of Excel, rather it is a part of all Office applications. The loop below would also work in Word, for instance (with some slight modifications).
(OUCH. The code below does not work for Office 2007 and above. We are looking for a reasonable alternative)
Application.FileSearch.NewSearch Application.FileSearch.FileType = msoFileTypeExcelWorkbooks Application.FileSearch.LookIn = ThisWeeksFilesPath Application.FileSearch.Execute
The code above looks in the folder we specified earlier for any file that is an Excel workbook. Then we loop through the files; opening them, reading them, and closing them without saving changes.
For Each ReportFile In Application.FileSearch.FoundFiles Set SourceWorkbook = Workbooks.Open(ReportFile, ReadOnly:=True)
The rest of our code we leave alone. The full source code is listed on the next page and you can try the final macro by typing in the source code yourself (always the best idea, though you may not be able to take the time) or by downloading the zipped file from our website which contains this macro as well as all of the facsimile data files.
Training and application development are a powerful combination for streamlining your business processes. Contact us to find your perfect balance between the two.