Interfacing with MS Office

Did you know that MoneyWorks works well with Microsoft Office? You can pull reports directly into Excel, mail merge directly into Word, set your budgets straight from Excel and more.

To show you how this works, we've provided some sample Excel and Word documents. You can download these and use them "as is" (they are useful in their own right), or you are welcome to explore the associated VBA code and alter them for your own purposes.

The samples are for Windows, and include:

Budgeting

A sample Excel spreadsheet that allows you to extract and set budgets in the currently open MoneyWorks document. Two Excel functions are implemented, GetBalance and GetMovement. These extract balance and movement data for the nominated accounts. In addition a macro is provided that extracts actual information for the named accounts over the named time periods, allows it to be altered, and then resubmitted as either MoneyWorks A or B budgets.

Do MoneyWorks Report

Two spreadsheets to extract a MoneyWorks report and place it directly onto the spreadsheet.

The Excel Report spreadsheet should be placed in the "Scripts" folder in the "MoneyWorks Custom Plug-ins" folder (Excel and Word documents placed in this folder are directly accessible through the Command menu in MoneyWorks). When started the spreadsheet will ask for a report (you can choose whether to use one in the Standard or Custom plug-ins folders), and this report will then be placed starting at the currently selected cell of the spreadsheet.

The MoneyWorks Report spreadsheet is similar to the "Excel Report" spreadsheet, except that this will not automatically start when opened (so don't put it into the "MoneyWorks Custom Plug-ins/Scripts" directory). Click the button to be prompted for a report, which will be placed into the current cell of the spreadsheet.

Interest Calculation

This Excel template extracts the current debtor information from the open MoneyWorks document, and works out the interest payable based on the nominated rate and the minimum balance (only debtors with overdue balances greater than the minimum are applicable for interest). The amount of interest due is displayed, and you are prompted as to whether you want to take these charges back into MoneyWorks (as invoices) or not. Note that for MoneyWorks to be able to import these interest invoices, you must put the import map file "InterestImportMap.impo" into the "Import Maps" folder in your "MoneyWorks Custom Plug-ins" folder.

Mail Merge

A Word document that provides a direct mail merge with MoneyWorks. Place this file into your "Scripts" folder in your "MoneyWorks Custom Plug-ins" folder and it will appear at the bottom of the Command menu in MoneyWorks. To activate the mail merge, highlight the records in the MoneyWorks Names file that you wish to merge against, and choose "MoneyWorks Mailmerge" from the bottom of the Commands menu. You will have the opportunity to modify the mail merge letter, and then press "Ctrl-Shift-M" to complete the merge.

Overpayment schedule

Extracts a list of overpayments (unallocated debtor receipts) from MoneyWorks and places them in the spreadsheet, allowing you to see who has overpaid what and when.

Ratio Viewer

Graphs the Current Ratio and Liquidity Ratio of the currently open MoneyWorks documents. These ratios (and others) are often used to monitor the health of an organisation.

Note that all these Excel and Word documents need to have macros enabled before they can operate with MoneyWorks. When you open one, you will be warned that it contains a macro - you should click "Enable Macros". Needless to say, the documents are provided "as is", and Cognito Software Ltd takes no responsibility for any loss or damage caused in the extremely unlikely event that they misbehave.

The download file is a zipped archive of the sample Office documents in Excel and Word. These are done in Office 2000 -- if you have Office 97, you can still open and look at these, but the macros will need to be altered for them to be able to run.

Download Office 2000 templates (140K, Windows Only)

Note: You will need the full version of MoneyWorks for Windows version 3.0.4 (or later) to be able to use these (they will not work with the demo).

Posted in COM/VBS | Comments Off on Interfacing with MS Office