One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer Functions) was the RW_ConvertToWordsAndNumbers() function to convert a currency amount into words. After the code was added, it was realised that Report Writer calculated fields of return type string are limited to 80 characters. This means that if your amount in words is longer than 80 characters, only the first 80 characters will be returned and the rest will be truncated and lost.
This example using the "Check with Stub on Top" report was created to provide a method of using Visual Basic for Applications (VBA) to overcome this 80 character limitation. The code uses the Continuum Integration Library to execute Dexterity sanScript code and also uses the Dynamic User Object Store (DUOS) to allow the Dexterity code to return its results to VBA. The DUOS is used because the DUOS table (SY90000) is easily visible to Dexterity as the SY_User_Object_Store table and to VBA as the DUOS objects.
The VBA code creates a Collection and creates 3 blank DUOS records to store 3 string properties. Then the code uses pass through Dexterity to call the RW_ConvertToWordsAndNumbers() function and then uses the RW_ParseString() function to split the returned result into 3 lines of 50 characters which are then stored into the SY_User_Object_Store table. When the code returns to VBA, it reads the DUOS records and displays the results to the report and finally removes the records in the DUOS.
NOTE: This customisation uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.
Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.
Please see the "Installation Instructions.txt" file in each version's archive for more information.
Related Posts:
05-Jul-2010: Added Related Posts.
PingBack from http://blogs.msdn.com/developingfordynamicsgp/pages/modifier-vba-samples.aspx
Recently, our customer complain the vendor check name is too long and not able to fix into the check.
We have created 2 textbox at the check. We use VBA to measure the vendor check name length. If it is too long, we move the remaining name into 2nd text box.
But, when user print the report directly to the printer without viewing. The VBA code is not execute. On the other hand, the VBA code will execute when user viewing the report then only print the report. We are using Check with Stub on Top.
May i know is it the VBA only execute when viewing the report?
Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP
hi,
in what part of the vba code where I can add asterisks before and after the amount in words so that it would look like this "**One thousand dollars only**"
The VBA code uses 3 lines to return the amount in words to the report. If you want asterisks only at the beginning and end you will need to check if the second or third line is used before adding the asterisks:
If Trim(PrintObject.Properties("String 2")) = "" Then String1LASTF1 = "**" + CStr(PrintObject.Properties("String 1")) + "**" String2LASTF1 = "" String3LASTF1 = ""ElseIf Trim(PrintObject.Properties("String 3")) = "" Then String1LASTF1 = "**" + CStr(PrintObject.Properties("String 1")) String2LASTF1 = CStr(PrintObject.Properties("String 2")) + "**" String3LASTF1 = ""Else String1LASTF1 = "**" + CStr(PrintObject.Properties("String 1")) String2LASTF1 = CStr(PrintObject.Properties("String 2")) String3LASTF1 = CStr(PrintObject.Properties("String 3")) + "**"End If
If you want asterisks on each line, try this:
String1LASTF1 = "**" + CStr(PrintObject.Properties("String 1")) + "**"String2LASTF1 = "**" + CStr(PrintObject.Properties("String 2")) + "**"String3LASTF1 = "**" + CStr(PrintObject.Properties("String 3")) + "**"
David
hi. thanks for the info. it's already working... :-)
By David Musgrave One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report
I recently had a support case where the partner consultant was trying to use the RW_ConvertToWordsAndNumbers
i want to add "only" after the amount. for eg : six thousand dollars only...I used the cat operator . But seems it not working...can u help ..?
Manoj
If you are using VBA to get this working, you will need to add the "only" text from VBA. The VBA code runs after the Report Writer has completed the section being printed. You cannot the results from VBA code in RW calculated fields.
I had trouble applying to CheckWtihStubonTopandBottom. Prints to screen great. Prints only the header when sent directly to the printer. The problem seems to related to a similar issue in Article ID : 884601.
Hi Rick
Without seeing your code I cannot say if it is related. I would suggest that you move any code that does not need to be repeated over and over into the Report_Start() and Report_End() events. Whether that be opening and closing an ADO connection or creating and destroying DUOS objects.
Note: If running GP 2010, you can use the new RW function which will handle the 80 character limit.
blogs.msdn.com/.../announcing-report-writer-function-rw-converttowordsandnumbersparse.aspx
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.