reinnovate database

Automated Documents Using Excel Spreadsheet

BAU DB does not work just like a spreadsheet, it actually links up with Excel to let you enjoy all those familiar features of Excel at the same time.
You need to have MS Excel installed to perform these features. If you do not have Excel, you can use WPS Office 2016 Personal Edition which is a freeware and is 100% compatible to Microsoft Excel. You can download the whole suite of WPS Office here.

Print an Entry to a Pre-Formatted Excel Spreadsheet

You might like to print out a formatted document, let’s say an invoice, and send it to your customer. It’s just easy to move the data from a BAU DB worksheet to a pre-formatted Excel spreadsheet for printing.
To create Excel document template, enclose the cell captions of a BAU DB worksheet with double colons “::”, and fill them in an Excel spreadsheet, that’s it.
You are familiar with Excel’s formatting features already. Open Excel and create the Invoice document in your favorite format. Most probably, you have some working document template in Excel file already. In order to move data from BAU DB to Excel, what you need to do is to replace the target cells in Excel spreadsheet with the cell captions in BAU DB worksheet and enclose them with double colons “::”.
Cell captions of the Invoice worksheet:
an invoice worksheet with cell captions
The formatted Excel spreadsheet with cell captions enclosed in double-colons:
excel template with cell captions from Invoice worksheet
Save the spreadsheet with any file name, let’s say “invoice-template.xls”, as the Invoice template. In the Invoice worksheet, press “Print” to pop out a file selection screen, and from which select the Excel template file to print out the record entry:
open and select excel template file to print the record to spreadsheet
open and select invoice template file to print the record to spreadsheet
The data of the record entry is moved onto the Excel spreadsheet:
an invoice printout

Saving and Selecting Spreadsheet Template Files

Although the Excel template files can be saved in any location, the default location of the file selection window that popped up when you press the “Print” button is the same location of the workbook files – i.e. the trade-goods.dbp and trade-goods.mdb files. If there is no other reasons, it is suggested to place the Excel template files in the same location as the database files.
place Excel template files in the same location as the database files
There is a setting which will help in the process of selecting the template file when you press the “Print” button. Go to Worksheet Setting and select the entry “INV” – the setting of “Invoice” worksheet. In the cell “Print File”, enter “invoice” and save the entry.
define the template file name in Print File
Since the defined value “invoice” is also the file name of the Excel template file, invoice.xls, and there is no other template file name starting with “invoice”, when the “Print” button is pressed, the output Excel document will be generated directly without selecting template file.
press Print button to generate Excel spreadsheet directly
Go to Worksheet Setting again, this time select the “PO” entry which is the worksheet setting of “Purchase Order”. Enter “po” in the cell “Print File”.
define PO in the Print File cell
In this case, since there is no template file named “po.xls”, only Excel files with names started with “po” are shown in the template file selection screen. This setting helps to display template files that will be used only by the Purchase Order worksheet.
press the Print button of Purchase Order worksheet
only Excel file name starting with po are displayed in the file selection screen

Spreadsheet Form Filler

In daily operation, a business always needs to manage a lot of forms like applications, registrations, timesheets, certificates, surveys or questionnaires etc. The Excel template automation feature let you handle this kind of work in a few simple steps.
Just create a new BAU DB worksheet by a single click and change the cell captions to the names of the data that have to be captured in the form. In the Excel form template file, add the corresponding cell captions, enclosed with double colons ::, into each of the blank space that have to be filled in with data.
Then you can create entries in the BAU DB worksheet and print the Excel forms out. If the input cells in the default generated worksheet is not enough, you can go to Worksheet Settings and add more single value cells or multiple value cells to the worksheet.
The Excel form to be printed:
The Excel form of timesheet to be managed
Click out a new worksheet from Main Menu:
Create a new worksheet with a single click in Main Menu
Change the cell captions in the worksheet:
Add the cell captions in the newly created worksheet
Add cell captions, with double colons, in the Excel form sreadsheet and save the file:
Add the cell captions in the Excel form spreadsheet with double colons
You can now create an entry in the worksheet:
Create an entry in the worksheet
Select the Excel file and print the entry out:
The entry is printed on the Excel file

Excel Spreadsheet Reporting – Utilize Excel to Create Reports

With some commands and directives inside an Excel template file, you can create data reports easily.
This is an example showing how to use replacing cell captions, report directives and Excel formulas together in an Excel template file to generate a report.
add directives to excel template to create report
This is the output Excel file of the multiple customer statement report generated with the above template file. The report data comes from a worksheet which holds the report data.
a printout of customer statement
We will elaborate more about report directives in the later tutorial section.

Export Worksheet Data to Excel Spreadsheet

You can always move the data from BAU DB worksheet directly to Excel spreadsheet for further reporting or manipulations without the need of any Excel template. You can move the data on List View, Entry Form or Info Pane to Excel spreadsheet with a click of the “^” button in the corresponding area.
Export List View
Click the “^” button in the top left of List View to export List View data to Excel spreadsheet:
export the data on list view to excel spreadsheet
the exported excel spreadsheet from list view of BAU DB worksheet
Export Entry Form
Click the “^” button in the top right of Entry Form to export the record entry to Excel spreadsheet:
export the entry to Excel spreadsheet
the exported excel spreadsheet from entry form of BAU DB worksheet
Export Info Pane
Click the “^” button in the top right of Info Pane to export the data list to Excel spreadsheet:
export the data in Info Pane to Excel spreadsheet
the exported excel spreadsheet from info pane of BAU DB worksheet