Section 11.3. Reports


11.3. Reports

Sometimes it is necessary to get data from a report. Data in reports can have any format and can be located anywhere. Some lines on the report will have no useful data and need to be skipped. This problem can often be solved with VBA. No one macro works in every case, but in this section we look at a general purpose macro for extracting data from reports . This macro uses settings that can be changed to handle many report formats.

Consider the sample report in Figure 11-5. It is part of a larger report giving statistics for batters and pitchers from various major league baseball teams. Suppose we need an Excel worksheet with the team name in column A, the batter name in column B, batting average (BA) in C, At bats (AB) in D, and runs batted in (RBI) in E. We are not extracting data about pitchers so we skip those lines.

We need to identify the lines containing information we want. Start by viewing the report in Notepad and inserting lines to make counting positions easy. The inserted lines are marked off in five character sections with a number indicating the tens. In this example two copies of this line have been inserted, as shown in Figure 11-6.

Using these lines as a guide, it is easy to see that the string TEAM starting in position 1 tells us to expect the name of the team starting in position 6 of the same line. Note that lines with batter information have a period in position 14. The data we want from those lines are in the positions shown in Table 11-2.

Figure 11-5. Baseball stats report


Figure 11-6. Inserting Position Counting Lines


Table 11-2. The data positions

Data item

Starting column

Length

Batter Name

1

13

Batting Average

14

4

At Bats

35

3

Run Batted In

60

3


We need to save a row when a line with a period on position 14 has been processed. Lines that do not have a period in position 14 or the word TEAM in position 1 have no data we are interested in and are ignored.

The following code will read the report and build a file that can be imported or copied and pasted into Excel. The way it behaves is determined by settings that can be changed for other reports. This macro is not a function and must be explicitly run from the Tools Macro Macros menu.

 Sub ReportExtract(  ) '********************************************** ' This is a general purpose macro for extracting ' data from reports. ' ********************************************* ' These collections are storage areas for the information ' the macro will use to extract and hold the data. Dim FindString As New Collection Dim FindLocation As New Collection Dim ItemStart As New Collection Dim ItemLength As New Collection Dim ItemString As New Collection Dim InputFile, OutputFile, WriteFlag, ShellFlag, Del As String Dim InLine, OutLine, MyString, StrItemNum As String Dim ItemNum, FlagLocation, x As Integer Dim RetCode As Variant On Error GoTo Err_Rtn ' If something goes wrong the macro will jump to Err_Rtn. ' InputFile contains the name and full path of the report file InputFile = "C:\FolderName\ReportFile.txt" ' OutputFile is the name and path of the file that will be ' created with the extracted information. Data from this ' file can be transferred into Excel via copy paste or ' by importing the file. OutputFile = "C:\FolderName\DataFile.txt" ' The macro needs to know when to write a line to ' the Output file. In this example a line is ' written when the current line in the input file contains ' a period in position 14. WriteFlag = "." FlagLocation = 14 ' Del is the delimiter that will seperate the data items. ' A comma allows us to create a csv file as the output. ' This kind of file can be opened by Excel. If any of the ' data could contain a comma a different delimiter must be ' used. Del = "," ' If you want the macro to automatically open the Output file ' set this value to Y. ShellFlag = "Y" ' In this section each data item to be extracted is described. ' The FindString entry is the identifying string for the line ' that contains the data. ' FindLocation gives the position in that line that the FindString ' is at. So, the first entry below the line is identified by TEAM ' starting in postion 1. ' ItemStart and ItemLength give the start position and length of the ' data item to be extracted. ' There can be any number of these. ' Data item one FindString.Add Item:="TEAM ", key:="1" FindLocation.Add Item:=1, key:="1" ItemStart.Add Item:=6, key:="1" ItemLength.Add Item:=50, key:="1" ' Data item two FindString.Add Item:=".", key:="2" FindLocation.Add Item:=14, key:="2" ItemStart.Add Item:=1, key:="2" ItemLength.Add Item:=13, key:="2" ' Data item three FindString.Add Item:=".", key:="3" FindLocation.Add Item:=14, key:="3" ItemStart.Add Item:=14, key:="3" ItemLength.Add Item:=4, key:="3" ' Data item four FindString.Add Item:=".", key:="4" FindLocation.Add Item:=14, key:="4" ItemStart.Add Item:=35, key:="4" ItemLength.Add Item:=3, key:="4" ' Data item five FindString.Add Item:=".", key:="5" FindLocation.Add Item:=14, key:="5" ItemStart.Add Item:=60, key:="5" ItemLength.Add Item:=3, key:="5" ' After the last item is setup the next item in ' the FindString collection is set to end of job FindString.Add Item:="end of job", key:="6" ' That ends the setting for the macro ' Open the files Open InputFile For Input As 1 Open OutputFile For Output As 2 ' Start a reading loop for the input file While Not EOF(1)     Line Input #1, InLine  ' Read a line from the input file     ItemNum = 1  ' Start looking for items with item number one     StrItemNum = Trim(Str(ItemNum))     ' This loop checks for each data item and each data item found     ' on the current line is stored in ItemString(  ).     While FindString(StrItemNum) <> "end of job"         If Mid(InLine, FindLocation(StrItemNum), Len(FindString(StrItemNum))) = _         FindString(StrItemNum) Then             On Error Resume Next             ItemString.Remove (StrItemNum)             On Error GoTo Err_Rtn             ItemString.Add Item:=Trim(Mid(InLine, ItemStart(StrItemNum), _             ItemLength(StrItemNum))), key:=StrItemNum         End If         ItemNum = ItemNum + 1         StrItemNum = Trim(Str(ItemNum))     Wend     ' All of the data on the line has now been extracted     ' Next we check for the WriteFlag.     If Mid(InLine, FlagLocation, Len(WriteFlag)) = WriteFlag Then         ' If the WriteFlag is on the current line         ' We build the output line by putting all of the data items         ' together separated by Del (the delimiter).         For x = 1 To ItemNum - 1             MyString = ""             On Error Resume Next             MyString = ItemString(Trim(Str(x)))             On Error GoTo Err_Rtn             OutLine = OutLine & MyString & Del         Next x         Print #2, Left(OutLine, Len(OutLine) - 1) ' Print the line skipping the                                                   ' last delimiter         OutLine = "" ' Clear out the Outline so it will be ready for the                      ' next write.     End If Wend Close  ' Close all files If ShellFlag = "Y" Then  ' If the ShellFlag is set to "Y"     If Right(OutputFile, 4) = ".csv" Then   ' If the output file is a CSV file         Workbooks.Open Filename:=OutputFile  ' Open the workbook     Else         RetCode = Shell("notepad.exe " & OutputFile, 1) ' or shell it in notepad     End If End If GoTo TheEnd  ' Jump to the TheEnd ' If an error has occurred this code will run. Err_Rtn: MsgBox (Error)  ' Display the error. Close           ' If any files are open close them. Resume TheEnd   ' Continue to TheEnd. TheEnd: End Sub 

This macro uses collections to store the extraction parameters. This could also be done using arrays. Arrays are easier to code, but they have to be dimensioned with a set number of members. Using collections avoids this problem, but there are limited functions with collections and error trapping has to be handled carefully.

Sometimes it is necessary to extract data from the same report periodically; e.g., a monthly sales report. Once the macro is updated with the setting for that report, the macro can be re-saved with a different name. It is important to remember the macro will only be available if the workbook containing it is open. If several versions of this code are required, it might be a good idea to create a workbook just for extracting data from reports.



Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net