This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
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.
Cheque Amount in Words Example.zip
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
Winthrop Development Consultants Blog 
PingBack from http://blogs.msdn.com/developingfordynamicsgp/pages/modifier-vba-samples.aspx
LikeLike
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?
LikeLike
Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP
LikeLike
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**"
LikeLike
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
LikeLike
hi. thanks for the info. it’s already working… 🙂
LikeLike
By David Musgrave One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report
LikeLike
I recently had a support case where the partner consultant was trying to use the RW_ConvertToWordsAndNumbers
LikeLike
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 ..?
LikeLike
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.
David
LikeLike
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.
LikeLike
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
David
LikeLike
Modified report check with stub on top. But I cannot print more than two checks at a time. It is going one line up in every page.
LikeLike
Hi Manoj
Page size is normally controlled by the printer driver and the paper size described by the driver.
You could also try adjusting your Header and Footer sizes to decrease the size per page. Also you can adjust the Records per Report body value on the additional header (if used).
David
LikeLike
Hello everyone. The VBA script does work but my challenge is that it gives me run time debugg error and it wont print mean while in some cases it will print properly.
In the scale of 10 it will give run time debugg error 8 times.
Please how do i resolve this issue.
Thank you
LikeLike