1. File Processing

A File Writing Programme	Practice
Exercise	Assignment

Learning Outcomes

On completion of this chapter you will know:

· The nature of a file

· How to open and close a file

· How to add data to a file and read data from it.

Download pdf version

Introduction

Up to now we have managed to calculate a complete payroll and display all of the calculated data – including the name of the employee whose salary we are calculating. We are not yet, however, able to keep a permanent record of our transactions and each time we calculate a new payroll the one that went before it is lost. Once we shut down Excel then even the last payroll that we calculated is lost. This is because all of our data has been stored in the computer’s memory. By nature this is volatile. As an example of this volatility, each time we run a program the data in its variables is wiped out once the program stops running. Similarly when we turn the computer off all of the data in its memory is lost.

In order to keep permanent record of our data we must save it to a file. Listing 7‑1 below is an adaptation of our payroll programme where after each payroll calculation the data, instead of being written onto the Immediate Window is instead written to a file..

A file writing programme

Go to top

As stated above Listing 10‑1 is an adaptation of our payroll programme. We have not included any of the functions that perform the calculations of the gross, tax etc, because we have not made any alteration to any of them. The first difference we see in Sub Pay is at line 12.

Open “Employees” For Append As 1

This can be explained as “open a file called Employees. If the file does not exist create it and then open it. If it already exists open it and go to the end of the file so we can append records to it. The file is to be opened on channel 1”.

Lines 13 -26 is a While loop which contains most of the body of Sub Pay. At line 13 a value is got for the variable strName. At line 14 this is tested for being blank. (When the InputBox dialogue box appears if you click on OK without entering any data into the text box then a blank will be stored in the variable strName.) If it is not a blank, or in other words if the user had entered an actual piece of text then the body of the While loop is entered at line 15. Here the normal processing we have been used to commences until line 23 is completed. In our previous version once the nett was calculated we printed the calculated values out on the screen. In this case, however, we write our calculated data to a file. Thus line 24 commences with Write # 1. This means “write to the file that is opened on channel 1 the values of all of the variables that are listed on the rest of this line”. These are the variables that contain the values for name and surname of employee, the hours, rate, gross, tax, superannuation and nett.

Line 25 is the main read of the loop and the employee’s name is prompted for. Line 26 then passes control back to the While at line 14 where the name is once more tested for being blank.

Once a blank is received for the name control passes to line 27 where the file opened on channel 1, i.e. the file “Employees”, is closed..

If we run this programme and add a number of employees and their payroll details all of the data will be saved to this file “Employees”. If we now use Notepad to open this file the data will look as in Figure 10‑1

Figure 10‑1

Listing 10‑1

1	Sub Pay()
2	Dim sngHours As Single
3	Dim curRate As Currency
4	Dim curGross As Currency
5	Dim curTax As Currency
6	Dim curNett As Currency
7	Dim curSuper As Currency
8	Dim intSuperCode As Integer
9	Dim strName As String
10	Dim strSurname As String
11	Dim strFullName As String
12	Open "Employees" For Append As 1
13	strName = InputBox("Enter first name of employee")
14	While Len(strName) <> 0
15	strSurname = InputBox("Enter surname of employee")
16	sngHours = InputBox("Enter Hours worked")
17	curRate = InputBox("Enter Rate")
18	intSuperCode = InputBox("Enter superannuation code")
19	strFullName = strName & " " & strSurname
20	curGross = calculateGross(sngHours, curRate)
21	curTax = calculateTax(curGross)
22	curSuper = calculateSuper(intSuperCode, curGross)
23	curNett = calculateNett(curGross, curTax, curSuper)
24	Write #1, strFullName, sngHours, curRate, curGross, curTax, curSuper, curNett
25	strName = InputBox("Enter first name of employee")
26	Wend
27	Close #1
28	End Sub
29
30	Sub ReadData()
31	Dim strFullName As String
32	Dim sngHours As Single
33	Dim curRate As Currency
34	Dim curGross As Currency
35	Dim curTax As Currency
36	Dim curNett As Currency
37	Dim curSuper As Currency
38	Open "Employees" For Input As 1
39	Do
40	Input #1, strFullName, sngHours, curRate, curGross, curTax, curSuper, curNett
41	Debug.Print strFullName, sngHours, curRate, curGross, curTax, curSuper, curNett
42	Loop Until EOF(1)
43	Close #1
44	End Sub

In Listing 10‑1 we also have a programme for reading the data from the file. This programme spans lines 30 – 44. Lines 31 – 37 declare variables that are similar to those that were declared for Sub Pay. In fact, the variables declared at lines 31 – 37 correspond with the data that was saved to the file at line 24. This is because at line 24 we wrote one String variable, one Single variable and five Currency variables to the file. When we read the file each data item must be stored in a variable.

At line 38 we open the file. This time we open it for Input, which means that we are reading data from it instead of writing data to it. The reading of the data is controlled by a Do loop. At line 40 one String variable, one Single variable and five Currency variables are read form the file. These variables are printed to the screen at line 41.

At line 42 we have the control for the loop. The syntax here needs some explanation. Firstly we need to examine in more detail how the data is written to the file at line 24. The Write # statement writes the content of each variable to the file and then puts a comma at the end of it before writing the contents of the next variable. When the contents of the last variable i.e. curNett, is written to the file Write # puts a linefeed character at the end to indicate that one complete record has been printed. This is why the contents of a file written by the Sub Pay above looks like the data shown in Figure 10‑1. In a file stored on disk, however, the data does not quite look like this, since all of the data is stored on a single track one record after another as shown in Figure 10‑2 below..

Figure 10‑2

When this file is opened for Input, as at line 37, a file pointer is positioned at the first character of the file, as shown above. Every open file also has a property called EOF which stands for end of file. As long as the file pointer is somewhere in the middle of the file, EOF is false. At line 39 the Input # command works as follows:

1. Data is read starting at the position of the file pointer and continues until the comma is met. This data is then stored in the variable strName. By now the file pointer is now positioned over the first character of “smith”

2. Data is read again, starting at the new position of the file pointer and continues until the next comma. This data is stored in strSurname. The file pointer is now positioned over the first digit of 20.

3. The digits of the number is now read and continues until the next comma. This data is stored in sngHours. The file pointer has now moved on to the first digit of the next number.

This process continues until data has been put into all of the variables in the list following Input #. By now the file pointer will be positioned above the first character in the second record, in other words above the m of “mike”. As there is more data left in the file EOF will still be false.

Control will now pass to line 40 where the data is printed and then on to line 41 where we have the control of the loop. Here EOF is still false and the syntax Loop Unitl EOF means “continue looping until EOF is true”. Because of this control will pass back to line 40 where data is read once more beginning at the position of the file pointer and continuing until data has been put into the last variable. By now the file pointer will be positioned over the s of “sam” and EOF will still be false.

The data will be printed out at line 41 again and since EOF is false control is passed back to line 39. Line 40 will now read the data as before, repositioning the file pointer as it reads. By the time the final piece of data is read into curNett the file pointer will be pointing just beyond the last item in the file – in other words EOF will be true.

Line 41 will print the data as before and then control passes to line 42 where the condition will now be false and thus the loop terminates and control passes to line 43, where the file is closed.

Practice

Go to top

Copy the code in Listing 10‑1 into your programme area. Ensure that the other functions for calculating the gross, tax etc are also copied into the same module. Now run the programme and add about four employees. Open the file with Notepad and check that the data is the same as that entered and that the programme calculated the values of the gross, tax , superannuation and nett before printing them to the file..

Finally run the programme once more in debug mode as shown in chapter 9 and check the values of the variables at each stage of the processing.

Exercise 10

Go to top

Modify the code you created in Exercise 8 so that instead of printing the text to the Immediate Window it prints it to a file. Next write another programme which reads each name from the file and prints it in the Immediate Window.

Assignment Part 8

Go to top

Modify the programme created for Assignment Part 7 so that on completion of each sale the data is written to a file called “Sales” as well as being printed on the screen. It’s the same data that will be written to the file as was printed on the screen before. The simplest way to do this is to create a procedure called saveData. This will have exactly the same agruments as showData, but instead of printing the data to the screen it saves it to the file “Sales”.

You should be able to check that the data is written correctly by opening the file with Notepad and examining its contents

Next write another programme that will open the file and read each record back and then print the data read. A While loop will control the reading of the data so that the code will be prevented from reading past the end of the file.