Beginner’s Tutorial
> Automated Documents Using Excel Spreadsheet
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:
The formatted Excel spreadsheet with cell captions enclosed in double-colons:
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:
The data of the record entry is moved onto the Excel spreadsheet:
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.
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.
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.
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”.
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.
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:
Click out a new worksheet from Main Menu:
Change the cell captions in the worksheet:
Add cell captions, with double colons, in the Excel form sreadsheet and save the file:
You can now create an entry in the worksheet:
Select the Excel file and print the entry out:
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.
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.
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 Entry Form
Click the “^” button in the top right of Entry Form to export the record entry to Excel spreadsheet:
Export Info Pane
Click the “^” button in the top right of Info Pane to export the data list to Excel spreadsheet:
