How to Create an Excel File from Renewal List





 
Recently, some of our clients have requested a report that would include customer and policy information into an Excel spreadsheet.  You might say "The Expression Builder should do that!"  Unfortunately, it is unable to combine information from both customer and policy database into one data source.

In this next example, one of our customers needed to generate a roll-over report for an insurance company.  The Excel file was generated, and then emailed to the inquiring insurer.

NOTE: This procedure will require the use of WORD integration in Power Broker
Click here for the Microsoft Word Integration Express document



1. From the main Power Broker desktop, go to Forms, Create or Edit Word97/XP documents

Create a new WORD form (e.g. INGROLL). 

Use Policy Basic. Merge fields are not significant for the creation of the Excel file.

Unless you will be generating and printing this letter to all customers, simply select merge field Policynum.

Save form and return to Power Broker.
 

















 
2.  From the main Power Broker screen, choose Policies, and then Print Renewal List - Master List from the Reports menu.

Click on the MS Word output button.

Change the date from today to 18 months from today as your date to ensure you cover the full period of renewals that may have already processed to next year.

Include One Insurer (for roll-over) or . Click Proceed.

A window will open asking you for the insurer code.

Click on the 'INGROLL' document from the Microsoft Word Forms list, click on 'Select, and then click on Proceed. 

Save Memo to Customer's File would be answered with a "N".  Click Proceed.

 Once Excel and Word have loaded and Mr. Clip It says "You are ready to begin editing your document, go to Windows Explorer and look in the BRO\<Usercode> folder (e.g. BRO\DK for Dianne Knutson). The file name will be same as the WORD doc name created - e.g. INGROLL.DBF.

Copy this file to a temporary folder on your own computer:

e.g.  C:\TMP

Please make note that the TMP folder must be created first.

Close Windows Explorer.
 


 		 3.  To cancel your Print job before it gets started, choose Ctrl, Alt, and Delete and choose Task Manager, and End Task on Microsoft Word.  Close the Task Manager, and then close everything else that's open.
 



 		 4.  Go to MS-DOS and at C:\TMP\>, copy the .DBF to an .XLS –

Type COPY INGROLL.DBF  INGROLL.XLS 

OR

In Windows Explorer, go to C:\TMP and you will find a file called INGROLL.DBF.  Right click over this file and rename it to INGROLL.XLS.

Windows may make a disclaimer that your file will not work if you change the file extension.  That's fine, we know it's going to work.

Exit DOS OR Windows Explorer so that everything is closed on your desktop, and then go to Start, Programs, and open Microsoft Excel.  Choose "File, Open" and then browse to your C:\TMP folder and you will see a file called INGROLL.XLS.  Open this file.
 
 

 

 		 5.  Edit INGROLL.XLS as required.  Note,you may delete columns in the Excel spreadsheet if not needed.  The Excel file will contain all data fields of the Policy Basic template.
 

.		 6.  Print or send INGROLL.XLS via email attachment as required.