Using DDE with FileMaker Pro - Accounting Advisors

Firstly, for those who are considering a project that involves automating both Filemaker and Peachtree, understand that this will be a complex task, since there is no means of communicating directly between the two programs. However, with an intermediary (here, I use Excel and VBA; a standard VB program will also do the trick), and patience, one can put together a fully automated solution. I will assume that the reader has read the other resources on this web site, and is comfortable with moving information using DDE. I will also assume a basic familiarity with Filemaker. These tips are intended for the user who is "almost there", to figure out the seemingly small bugs that will take up the majority of your time.
How to create a Filemaker script that writes a payment/receipt in Peachtree:

Pitfalls that I have encountered:

Don't put commas in your vendor names! This will screw up any attempt to retrieve information on that vendor, unless they are part of a larger range, where that vendor is neither the first nor last in the range.
What little information on DDE exists in Peachtree's manual is valuable, but is not always accurate.
There is no documented way to retrieve or poke information from or to the "memo" field on a payment. If you follow the manual's directions, you will be referencing a field that is only visible when you are selecting a payment to edit.
When poking checks with a vendor ID, I found that Peachtree (7.0) would not automatically enter the address from the vendor record, the way it would if I had selected the vendor name from the list. This meant that I had to retrieve the vendor's address before poking (including city, state, and zip - don't get the "CITYSTATEZIP" all in one field, you'll have to poke them back in again separately!).
Retrieving Zip Codes was a serious pain - it removes the leading zeroes (e.g., zip code 08816 becomes 8816). I wrote code (excerpted below) to account for this in VBA.

Warning! Code may be wrapped!

'Here, I request a zip code for the Vendor ID stored in Cell(intRow, 2)
.Cells(intRow, 20) = DDERequest(ChannelNumber, "File=VENDOR, Range=" & .Cells(intRow, 2) & "," & .Cells(intRow, 2) & ", Field=ADDRZIP")

'Here, I take the resulting info and add leading zeroes. Note that it will not add leading zeroes if the zip code field in Peachtree was blank.

While (Len(.Cells(intRow, 20)) < 5) And (.Cells(intRow, 20).Value <> 0)

.Cells(intRow, 20) = "0" & .Cells(intRow, 20)
.Cells(intRow, 20) = .Cells(intRow, 20).Text

Wend

Did you know that there's an alternative to DDE?

PawCOM not only eliminates the need for DDE, it also gives you access to all of the underlying tables in Peachtree, not just the selective availability offered by DDE. This ActiveX control offers both read and write access to Peachtree, and is compatible with Peachtree Accounting and Complete Accounting for Windows version 7 or later.
Plus, the DLL file can be distributed with your applications royalty-free!

Click Here to Learn More or Purchase PawCOM Now!

Accounting Advisors, Inc. 614 Park Avenue SE Atlanta, Georgia 30312 Telephone: 404-784-0275 Toll Free: 800-724-0315 Fax: 404-420-2175 Privacy Policy	[Home Page] [Our Services] [Recent Projects] [Training] [Support] Tips: [Excel] [Peachtree] [Quickbooks] [Google] [DDE] [Other] [Pay Your Invoice] [Driving Directions] [Site Index]	About Our Site
	Questions? Comments? Email Us or call (404) 784-0275 Our toll free number is (800) 724-0315 © Copyright 2004 by Accounting Advisors Web site designed and maintained by Accounting Advisors, Inc.


Quick Links - Upload a Project File - Learn How We Can Help - Read About Recent Projects - Schedule a Class - Pay Your Invoice Online - Request Technical Support Free Newsletter You'll receive Peachtree tips, Excel tips, and more! Newsletter Archives Privacy Policy Did you know? We offer technical support for Peachtree Accounting, Microsoft Excel, and DDE by e-mail, telephone, or remotely.			Using DDE with FileMaker Pro Jon Goldberg has successfully integrated Peachtree Accounting and FileMaker Pro via DDE (by way of Excel). We thank him for contributing the following content to our web site: Firstly, for those who are considering a project that involves automating both Filemaker and Peachtree, understand that this will be a complex task, since there is no means of communicating directly between the two programs. However, with an intermediary (here, I use Excel and VBA; a standard VB program will also do the trick), and patience, one can put together a fully automated solution. I will assume that the reader has read the other resources on this web site, and is comfortable with moving information using DDE. I will also assume a basic familiarity with Filemaker. These tips are intended for the user who is "almost there", to figure out the seemingly small bugs that will take up the majority of your time. How to create a Filemaker script that writes a payment/receipt in Peachtree: First, know the "Send Message" Filemaker script step. Rarely used, it will instruct Filemaker to open the document or application of your choice. Write a script that looks like this: Perform Find[Restore] (your found set should be all the records you want to export) Export Records[Restore] (Important: Have it export to the same file location every time; use .csv format, or .xls if you have Filemaker 5.) Send Message[Restore, No Dialog, (your VB app or VBA-enabled document here)] Have the script open a VB application (or, in my case, an Excel document with an "Auto_Open" macro. In the macro, open the exported .csv file, and DDE poke to your heart's content. N.B: Due to the way that Filemaker stores exported file settings, if you plan to use your solution in a networked environment, each user's .csv file must have the same pathname. Pitfalls that I have encountered: Don't put commas in your vendor names! This will screw up any attempt to retrieve information on that vendor, unless they are part of a larger range, where that vendor is neither the first nor last in the range. What little information on DDE exists in Peachtree's manual is valuable, but is not always accurate. There is no documented way to retrieve or poke information from or to the "memo" field on a payment. If you follow the manual's directions, you will be referencing a field that is only visible when you are selecting a payment to edit. When poking checks with a vendor ID, I found that Peachtree (7.0) would not automatically enter the address from the vendor record, the way it would if I had selected the vendor name from the list. This meant that I had to retrieve the vendor's address before poking (including city, state, and zip - don't get the "CITYSTATEZIP" all in one field, you'll have to poke them back in again separately!). Retrieving Zip Codes was a serious pain - it removes the leading zeroes (e.g., zip code 08816 becomes 8816). I wrote code (excerpted below) to account for this in VBA. Warning! Code may be wrapped! 'Here, I request a zip code for the Vendor ID stored in Cell(intRow, 2) .Cells(intRow, 20) = DDERequest(ChannelNumber, "File=VENDOR, Range=" & .Cells(intRow, 2) & "," & .Cells(intRow, 2) & ", Field=ADDRZIP") 'Here, I take the resulting info and add leading zeroes. Note that it will not add leading zeroes if the zip code field in Peachtree was blank. While (Len(.Cells(intRow, 20)) < 5) And (.Cells(intRow, 20).Value <> 0) .Cells(intRow, 20) = "0" & .Cells(intRow, 20) .Cells(intRow, 20) = .Cells(intRow, 20).Text Wend Did you know that there's an alternative to DDE? PawCOM not only eliminates the need for DDE, it also gives you access to all of the underlying tables in Peachtree, not just the selective availability offered by DDE. This ActiveX control offers both read and write access to Peachtree, and is compatible with Peachtree Accounting and Complete Accounting for Windows version 7 or later. Plus, the DLL file can be distributed with your applications royalty-free! Click Here to Learn More or Purchase PawCOM Now!