Case Study - The Un-PivotTable
The Custom Software Approach: Excel VBA To The Rescue!
One More Loop!
(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"
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.