Exporting Summary Data from Alpha Five

by Dr. Peter Wayne

Ed had a problem. He was trying to export a number of records to Excel, along with the summary of one of the table's fields. He initially thought he could use a summarize operation, but learned - the hard way - that a summary wouldn't work for him.
Here is Ed's base table, jobs.dbf:

Ed needs to export a range of records from jobs along with a summary of the costs. For example, if jobs has these records:

Ed wants to export all the records plus the total of the costs, as in

Date_delvd			Job_numb				Cost
07/24/98		1						10.00
07/24/98		2						15.00
07/24/98		3						20.00
07/21/98		1						15.00

Total									60.00

Or, when seen in Microsoft Excel:

Figure 1. Same records in Microsoft Excel with total.
There's an easy way and a harder way to get these records, and their total, out to Excel.

The easy way relies on the fact that you can use an Alpha Five report to produce a listing of detail records as well as a total. Therefore, if we can only export a report to Excel, we'll have solved the problem.
In fact, that's exactly what we'll do. We can use the Report Genie to help us create a simple report with detail records and a grand total. Make sure to remove the report header and any extraneous formatting lines. Set the Font property of each item on the report to Courier, 12 pt and place a comma between each object on the report:
Figure 2. Report design with field titles and commas.
I let the Report Genie code the expressions for the totals in the Report Footer, with minimal modification. The value for COURCOS_GG is TOTAL(COURCOST,GRP->GRAND) and the value for COURCOS_TG is simply the literal string, "Total cost:".
Figure 3. Calculated fields in report footer.
Notice that the Genie also prepared a calculated date object, which I am not using. You can delete it from the list of calculated fields, if you like, or leave it - it doesn't matter.
Next, you have to configure a Windows printer driver for Generic/Text only, with output to File:

Figure 3. Adding the Generic/Text only printer driver.

Choose FILE: as the output for the printer driver
To export to Excel, all we need to do is print the report to the Generic/Text only printer:

Figure 4. Choose Generic/Text only for the report output
Windows will prompt us for the name and location of a file, which will have a .prn extension:
Figure 5. Select a file name
Here, I have chosen jobcost.prn as the name of the output file.
Next, I start up Microsoft Excel, and I open the jobcost.prn file. I have to tell Excel that I am importing a Delimited file and that I will begin the import at line 4:
Figure 6. Import parameters for Microsoft Excel.
Click on Next and tell Excel that the file uses commas as delimiters:
Figure 7. Microsoft Excel delimiter parameters for a text file
Then press Next and tell Excel that each column will be in General spreadsheet format:
Figure 8. Each imported column is in General format.
If you now choose Finish, you will see the spreadsheet as in Figure 1.

There's another way to get the output into Excel, and that is by writing it out in Xbasic. We could use Xbasic to write to a file, or we can use Xbasic to write directly to Excel using dynamic data exchange, or DDE. DDE is a method by which one program can call another and transfer content. Excel can serve as a recipient for DDE, and Alpha Five knows how to send content. We can place a button for exporting on a form based on jobs.dbf. This script, attached to the OnPush event of the button, borrows heavily from the discussion of DDE in Xbasic for Everyone, a slim little gem of a volume recommended fervently by my mother and other impartial reviewers:

''XBasic
option strict
dim tbl as p
dim nrecs as n
dim nfields as n
dim row as n
dim column as n
dim fld as p
dim type as c
dim content as c
dim cell as c
dim chan as p
dim fieldname as c

sys_shell("c:\msoffice\excel\excel.exe")	'your path to Excel may be different!
sleep(3)				'give Excel time to start up
chan=dde.open("Excel","Sheet1")

tbl=table.current()
nrecs=tbl.index_primary_get().records_get()
nfields=tbl.fields_get()
dim totals[nfields] as n
' fill the top row with the names of the fields
for column=1 to nfields
	fld=tbl.field_get(column)
	fieldname=fld.name_get()
	cell="R1C"+alltrim(str(column))
	chan.poke(cell,fieldname)
	if fld.type_get()="N" then
		totals[column]=0 
	end if
next column

tbl.fetch_first()
for row=2 to nrecs+1
	for column=1 to nfields
		fld=tbl.field_get(column)
		type=fld.type_get()
		select
			case type="N" 
				content=ltrim(str(fld.value_get(),10,2))
				totals[column]=totals[column]+fld.value_get()
			case type="C"
				content=fld.value_get()
			case type="D"	
				content=dtoc(fld.value_get())
		end select
		cell="R"+alltrim(str(row))+"C"+alltrim(str(column))
		chan.poke(cell,content)
	next column
tbl.fetch_next()
next row

row=row+2 ' skip a line
chan.poke("R"+alltrim(str(row))+"C1","Total")
for column=1 to nfields 
fld=tbl.field_get(column)
if fld.type_get()="N" then 
	cell="R"+alltrim(str(row))+"C"+alltrim(str(column))
	content=ltrim(str(totals[column],10,2))
	chan.poke(cell,content)
end if
next

chan.close()

Script 1. Script to call Excel by DDE
Any time you call another program with sys_shell() it's a good idea to issue a sleep(3) to give the other program a chance to finish loading. Also, if your computer is slow, you may have to issue an occasional extra sleep(1) command in between some of your chan.poke() statements, to give DDE a chance to transfer data.

Download the tables and forms discussed in this article.

8/1/98 - pkw

Don't forget, we need your feedback to make this site better!

Return to home