It is possible to attach front-end data entry systems, external data analysis, executive query facilities etc to MoneyWorks using Visual Basic (or other COM/OLE Automation-compliant scripting languages).
The commands
Open: Open the specified document
Usage:
Open (path as string) ' list of objects to open
Example:
mwObject.Open ("C:\My Document\XYZ Accounts.mwd3")
This will
open the specified document, closing any currently open document first. This command can also be used to open report files &c (but this is not very useful)
This can also be used to make network connections using the moneyworks:// URL scheme. See
MoneyWorks URL Scheme
Possible errors:
10500 MoneyWorks can not service OLE requests at this time (modal dialog up)
Quit: Quit application
Example:
mwObject.Quit()
Asks
MoneyWorks to quit.
Possible errors:
10500 MoneyWorks can not service OLE requests at this time (modal dialog up)
10505 MoneyWorks did not quit (user may have aborted)
ImportFile:
Imports data from a text file using a specified import map.
Usage:
boolVar = ImportFile(textFilePath as string, mapNameOrPath as string)
Example:
isok = mwObject.ImportFile ("C:\My Documents\names.txt", "NameMap.impo")
This will import the data from the text file. You must already have set up and saved an import map to suit the data being imported. MoneyWorks determines where the records are to go from the import map (i.e. if its a Name import map, they will be names). If any errors are encountered with the data, the entire import is aborted, and your script will get an error number 10503.
ImportText
Imports data from a string using a specified import map.
Usage:
boolVar = ImportText(data as string, mapNameOrPath as string)
Example:
isok = mwObject.ImportText ("Fred" & Chr(9) & "Fred Bloggs" & Chr(9) & "2" & Chr(13), "C:\Files\MyMap.impo")
In this case,
the text to be imported is supplied directly to the importtext command. (Here, the import map would be set up to accept the three fields)
Error numbers you should be prepared to handle when importing:
10501 The named import map could not be found
10503 Errors in import: non-specific
10506 No document is open
The mapNameOrPath parameter may be xml text containg a single "args" tag with the following attributes:
<?xml version="1.0"?>
<args file="transaction"
map="updatemap"
update="true"
post="true"
seqnum="999"/>
Yes, the <?xml version="1.0"?> is required.
file must be present. Useful values are "transaction", "account", "user", or "build". You can import into other files this way but there is no point—just use the regular syntax. If it's "account", the import data should be in the same format you get when you Copy accounts from the accounts list.
map must specify the name of an import map in the Plug-Ins if the file is "transaction".
update is optional for transactions. If true the import data must specify a single invoice to replace an existing transaction whose sequence number is specified using the seqnum attribute. If the invoice identified by seqnum is not posted, then it gets deleted upon successful import of the new transaction (new one effectively replaces it). If the invoice is posted (and providing any payments are not processed for GST), it is cancelled. If there were any payments on it, they become overpayments which may then be (manually) allocated to the new (or any other) invoice.
post is optional for transactions. Transactions are posted on successful completion of the import
Note that from Applescript, the quotes in the xml will need to be escaped with a \
If the file is "build", You are importing build recipe data for manufactured products. The import data must contain the fields: ProductCode, Qty, PartCode in that order, tab-delimited.
If the file is "user", you are importing any persistent data that you may need to store—usually in support of your external system—the import data must contain the fields: Key, Data, in that order, tab-delimited (key is up to 9 chars (must not start with '#')). Data can be up to 255 chars.
Export: export data from specified file
Usage:
strVar = Export (filename as string, searchExpr as string, destPath as string)
[Result: anything] ' if no destination file is specified, reply is tab-delimited text of records
Exporting is even more versatile than importing. Some of the variations are shown below:
Example 1:
rsltStr = mwObject.Export ( "Transaction", "Gross > 0", "")
This returns the matching transaction records as a tab-delimited string. You get every field from the record, including some gobbledgook ones that won't mean much to you.
Note that the result (even if it is an empty string must be assigned to a string variable (this is only because of the idiosyncratic VB syntax).
Example 2:
rsltStr = mwObject.Export ("Job", "", "C:\Some Jobs.txt")
This exports all job records (since there is no search specification) to a file called "Some Jobs" on the hard disk. If the destination file already exists, MoneyWorks will overwrite the file with the data, obliterating whatever was in the file before. Take Care.
Special Searches
Using "=" as the search expression will export a single "record" containing just the field names for the file
Using "*" as the search expression will give you the highlighted records in the main list window belonging to the file you have asked for, or all records if there is no highlighted selection
Using "**" as the search expression will give you the highlighted records in the main list window belonging to the file you have asked for or none if there is no highlighted selection.
The export syntax allows the 'filename' parameter to contain formatting information that specifies what is to be exported:
"TableName[.sortfield[-]][#formatstring]"
sortField can be included to sort the export by that field
sortfield can have a "-" appended to specify descending sort
formatstring can specify what to export. Everything in the format string is returned verbatim except for anything inside [...] which is treated as an expression which can reference the fields of the file being exported. Thus if you want tab-delimited, then put tabs between the expressions. You can use metacharacters \t \r \n \xHH (hex) or \\. Note that since Applescript expands the same metacharacters, you are best to use meta-metacharacters in applescripts, e.g. \\t \\r \\x0a etc
As of v6.1.1, If the format string is exactly the text "xml", "xml-terse", or "xml-verbose" then the records will be exported as xml.
Examples
mwObject.export("Name", "", "") ' same as usual
mwObject.Export( "Name.Code-", "', "") ' export in usual format but sorted descending by code
mwObject.export("Name.Code-#[Code],[Phone]\\r", "", "") ' export code and phone number separated by a comma with lines delimited by a Return character
mwObject.export("Name.Code-#\"[Code]\",\"[Phone]\"\\r\\n", "", "") ' as above, but fields are quoted, and line delimiter is MSDOS-style
mwObject.export("Ledger.concat#\"[AccountCode + if(Department != ``, `-` + Department, ``)]\",\"[Lookup(AccountCode, `Account.Description`)+ if(Department != ``, ` (` + Lookup(Department, `Department.Description`) + `)`, ``)]\",\"Y\",\"[Lookup(AccountCode, `Account.TaxCode`)]\"\\r", "", "") ' exports account codes in banklink format
mwObject.export("Name#xml", "", "") ' export as xml
Possible errors:
10502 The logical file name (i.e. "Job" in the above example) is not valid (you may have misspelled it)
10504 Bad search expression. MW could not do the search (try it out in the advanced Find dialog box to make sure it works)
10506 No document is open
DoReport: run a named MoneyWorks report
Usage:
DoReport(reportName as string, startDate as string, endDate as string, outputMode as integer, destinationFilePath as string, jobDialogs as Boolean)
startDate: a date (as text, e.g. "31/3/96") within the starting period to run the report for
endDate: a
date (as text, e.g. "31/3/96") within the ending period to run the report for
outputMode: where to send the report : 1 = print, 2 = preview, 3 = export, 0 = return text
destinationFilePath: the text file to export into (if output option is text file)
jobDialogs: if false, no dialogs are shown
Result: if outputMode is not 1..3, result is tab delimited text of report, otherwise it is an empty string
Example
strRslt = mwObj.DoReport("Profit Report", "1/4/96", "1/4/96", 3, "C:\EIS folder\Profit rep.txt", FALSE)
This runs the reports for the (entire) period in which the date 1/4/96 falls. The result is exported to the text file and the settings dialogs are bypassed (the user does not need to do anything). Any settings you want (except the period range) must already have been set up in the report. If you need to do the same report with different settings, save copies of the report with those settings already set.
NOTE: Only reports of type .CREP can be run this way.
Possible errors:
10500 MoneyWorks can not service OLE requests at this time (modal dialog up)
10506 No document is open
10507 Report name not recognised
10508 User cancelled report (this can happen even if you suppress the dialogs)
Evaluate: Evaluate an expression using the built-in expression parser
Usage:
Evaluate expressionText
Example:
mwObj.Evaluate("Today()")
returns today's date (as text)
mwObj.Evaluate( "1 + 1" )
returns "2"
mwObj.Evaluate("Lookup(`1000`, `Account.Descripton`)")
Returns the name of account 1000. Note that quotes in the expression have been done using backquotes ` instead of ". MoneyWorks treats ` as " for this purpose. In VB you can also embed a " in text by doubling it, e.g. ("Lookup(""1000"", ""Account.Descripton"")")
Example visual basic program
Dim mwObj As MoneyWorksApplication
Dim x As StringSub test()
Set mwObj = GetObject("", "MoneyWorks.Application") ' attach to running MW
mwObj.Open("c:\Users\Rowan\Documents\MoneyWorks\Acme Widgets Gold.mwd7")
x = mwObj.ImportFile("C:\import text.txt", "NameMap.impo")
mwObj.Quit
End Sub