Receiving DDE information?

[bdonelson]

Hello, I hoping someone has some experience with DDE. I am attempting to setup a dynamic link with DDE from another application to Filemaker.

Does anyone know of a way to do this with Filemaker?

Thanks,

Bill

[fseipel]

Filemaker supports a DDE *send* execute script command; however, it has no inbuilt capability to receive DDE execute commands. It might be possible to receive DDE commands with a plugin during idle time.

If the goal is to manipulate data, and the other application can only communicate using DDE, an alternative option might be to write a separate program translating DDE requests into XML requests that Filemaker server can understand.

[bdonelson]

Thanks for the idea. I am not sure, if that would fit my needs. I attempting to display "live" stock market information for the symbol from the current record. I have a DDE "feed" from the trading platform software. I do not have a need to save this information.

[fseipel]

http://www.angelfire.com/biz/rhaminisys/ddclient.html would probably do the trick; it can be used from a VBSCRIPT and is an ActiveX control that can talk to the DDE server. A C++ plugin could talk to your DDE server; this might be the most elegant approach. DDE is a legacy protocol, so if there's another way to communicate, you might try it?

Another alternative, a bit 'ugly' would be to use Excel as an intermediary. e.g. Excel gets data using DDE, writes it to a file, then it comes back into Filemaker. Excel could run in background, continuously updating data files, or it could run on demand in a hidden window. You would use Application.DDERequest, Application.DDEInitiate, Application.DDEPoke. Files thus generated could then be imported into Filemaker automatically. That is, Excel has a fairly rich command set to deal with DDE. This may results in updates being a bit slower but it would get the job done.

Yet another option is to purchase a high level language that has simple DDE commands and good support/examples, e.g. Winbatch http://www.winbatch.com/ -- then just call the Winbatch script from Filemaker.

[bdonelson]

Thanks, I will look into that.

I have actually tried the Excel option, that is I think I did it correctly.

I inserted the Excel spreadsheet as an object, but http://fmforums.com/forum/showtopic.php?tid/210257

Bill

[fseipel]

I do not think inserting the Excel worksheet into Filemaker will work; the reason is, I don't think it can do anything with 'live' Excel data, only show a container that, when clicked on, loads the Excel data.

I've done contract work in Excel to interchange data with Filemaker and might be able to assist you on this project.

If the Excel worksheet can retrieve live data, then I see these as the best options, in order:

1.) Presumably, Excel exchanges DDE data and gets current prices into the Excel worksheet using commands such as DDEREQUEST. These DDE commands can be provided in Filemaker using a Visual C++ plugin ; an example of these commands (albeit to communicate with Excel) is here: http://support.microsoft.com/kb/279721 -- the plugin would provide external functions to execute DDEREQUESTS, DDECONNECT, etc in VC++. This is the most elegant solution but requires modification of the example to communicate with the stock software, and intergration of the example into a plugin.

2.) Use the ACTIVEX component described in previous message, in a VBSCRIPT to get the data. The ACTIVEX will allow the VBSCRIPT to get the data; Filemaker will run the VBSCRIPT. VBSCRIPT will write the results to disc, and the Filemaker script will import this data.

3.) Use Excel worksheet as-is; however, add a VBA macro to it. The VBA macro will get a list of stocks from a file, get the data using DDE, and output results back to a file, then it will close. A Filemaker script will write the file comprising list of stocks to look up, then open the Excel file (to run macro), then import the results back into Filemaker automatically. This is less elegant but would work. If data is to be updated continuously, VBA macro could instead write data out at intervals to a file, and stay open. A filemaker script could import the data at intervals.

If you just need current stock prices, that can be achieved through WebViewer. Does your stock software support webservices, or any other means of data interchange other than DDE?

[bdonelson]

The first solution does sound like the best option.

I wish the stock software did support webservices, it would help things.

I think will see what I can do with this, if not maybe my customer will agree to some additional services.

Thanks again,

Bill

[fseipel]

I did some further work on this, I was able to get the Microsoft example integrated into a plugin and retrieve data using the DDE Request method. It opens Excel, reads cell A2, and returns its contents into a Filemaker field. Of course, for a solution, you'd want a separate Filemaker function to do a DDE Execute (open stock program if not already open), a DDE Request, possibly a DDE Poke, definitely a DDE Initialize, DDE Unitialize, DDE Connect, and DDE Disconnect. My test case only returned a fixed cell using a single plugin function; I'd be happy to send a compiled sample as proof-of-concept prior to engaging in paid work.

[comment]

If you can get an Excel spreadsheet to update dynamically, couldn't you just import the Excel data into Filemaker?

[fseipel]

Yes, this solution would involve a Filemaker script that write a text file to disc comprising what DDE commands to execute. Then the Filemaker script would open an Excel file; this file would have a VBA script that runs when worksheet opens, that loads this file, executes the DDE commands, and writes a text file to disc, then closes Excel (or leaves it open, if many requests are to be received). Filemaker script would wait until text file exists/stops changing, then import it. Or, Excel could create a second text file to signal it was done, Filemaker could delete both when finished importing them. This solution was discussed briefly earlier in the thread.

The plugin solution is more elegant. Ultimately, the plug-in would directly query the stock software and NOT Excel; I don't have the stock software so I tested the plugin by querying Excel 2007. I'm assuming the stock software, is FOREX or something similar.

There might also be some utility in a DDE plugin for other purposes within Filemaker, even though it is legacy technology (e.g. complete control of Excel, Word from Filemaker). DDE can likely also be used to integrate Peachtree, FoxPro, etc with Filemaker presenting some interesting possibilities.

[comment]

I know very little about DDE (and have no plans to change that), but I am under the impression that it can "push" data into Excel. So the spreadsheet could be kept up-to-date at all times, and all Filemaker needs to do is import it.

[fseipel]

The spreadsheet will contain formulas (DDE requests). Updating it can be achieved by forcing a recalculation, either by opening it, or through VBA, VBSCRIPT using OLE, or succesive DDE Execute commands. Saving the spreadsheet using File|Save as, will not generate a file importable into Filemaker, because the worksheet, comprises formulas, not values; the formulas query FOREX or whatever stock trading platform is used, and get current data from it. As noted, a VBSCRIPT or successive DDE Executes, and/or custom VBA in Excel, will work, but it's a complete kluge. The executes or VBSCRIPT would have to include a COPY command and paste special|values only, somewhere else on the same sheet or to a different sheet, because it would be necessary to save the file with fixed values to allow Filemaker to import it.

The original source of the data is the stock trading platform, and it acts as a DDE server to other client applications.

Using Excel as an intermediary has the following disadvantages: 1.) Requires Excel be installed on every computer running the solution 2.) Slower and less reliable 3.) Error handling is problematic; does Excel present dialogs if it fails to connect to stock trading platform; what if Excel sheet generates an error? 4.) May not work if Excel's DDE security settings aren't correct 5.) Harder to maintain code since it will be a kluge of VBA, VBSCRIPT, and Filemaker script. What's being discussed is using Excel as a server to process data from another source and then import it into Filemaker. I've done this, it doesn't work well for the reasons outlined above.

The objective here is to get data from the stock trading platform over DDE; the only reason Excel is being discussed is because the vendor probably provides examples of DDE requests as Excel functions, and to that extent the Excel worksheet is useful as a template of the DDE commands required.

[comment]

Well, as I said, I don't know much about it. However, I am quite certain that when you import a spreadsheet that contains calculations, Filemaker imports the calculated values - not the formulae. Maybe cells containing DDE queries are different in this aspect, though it's hard to see why.

I also don't see why you would need "Excel be installed on every computer running the solution". It only takes one workstation to import records for all users.

I agree that the entire setup is a kludge. It will remain a kludge for as long as the data source is in an obsolete format - no matter how it's handled. That's why I would spend as little effort and money as possible on a solution, and concentrate on getting the data provider to provide an XML feed.

[fseipel]

I stand corrected on this; Filemaker never imports formulas. Filemaker would import the last *calculated* values when the spreadsheet was active. For instance, if you put a =NOW() function in cell A1 to fetch current timestamp, in a worksheet, and save the worksheet, if you then import into Filemaker, it will import the timestamp the last time the spreadsheet was recalculated (typically, when it was opened/saved). It will NOT import the *current* timestamp (time you import the file) when you import the Excel file . This makes perfect sense, because importing, does not force a recalculation of the worksheet. That's because importing, does not open Excel so its calculation engine is not even active. This issue can also bite you if you have multiple workbooks referencing each other, and one is updated while another referencing it, isn't even open; if you then import, you'll get 'stale' data. So the DDE functions would evaluate to stale data, whatever they read when file was last saved in Excel, if you just attempted to import the file.

Since the formula is not re-evaluated prior to import, it would be necessary to open the file in Excel and save it using VBSCRIPT/DDE; this will refresh values. Following this, the spreadsheet would contain current data, and could be imported into Filemaker.

Normally this nuance of Excel behavior never arises because the formulas don't reference dynamic data; in situations such as process control, LIMS, ERP, etc where Excel gets current data from another source, this issue does arise.

I'm assuming the DDE data is brought in on-demand to Filemaker, from any computer with the solution open; in this case, you've got to command a copy of Excel to load the file to refresh the data (see above discussion). If you use DDE Execute to OPEN and SAVE the file (to update values), this might be possible; but it will fail if that computer is off or no user is logged on. That's why I stated Excel required on each PC.

Bridging the DDE to another format (what you allude to in your message) is indeed possible; in this case a program would submit DDE requests at intervals and publish results to a file. Ideally, this program would get its list of symbols, etc from a configuration file, so if symbols changed, it would still publish the correct information; Filemaker would then import this CSV file at intervals. Filemaker could also export the list of symbols to fetch values for (so configuration file is always up to date); that solution is somewhat attractive in that Filemaker server could then import the resulting file thereby providing up-to-date values to all clients, without a plugin, and without Excel.

[comment]

I don't want to beat this to death, but it's quite common to have a dedicated workstation (even unmanned) to perform periodical imports.

I believe that if the Excel spreadsheet were kept open and set to auto-save, there would be no need for anything else - other than a simple import.

I presume another option would be to force the file to save just before importing it, by issuing a Send DDE Execute[] command from Filemaker.

Bridging the DDE to another format (what you allude to in your message)

I don't think I have alluded to anything like that. I said using Excel as a "translator" for the incoming data should be considered a temporary stop-gap until direct import from the data source becomes possible.

[fseipel]

Attached is a solution that demonstrates inter-application communication between Excel and Filemaker without a plugin using VBSCRIPT. The table contains three ticker symbols, and three dynamic formulas (I do not know the DDE syntax, so instead, I just used =RAND()*50 to generate a dollar amount between 0 and $50 the changes each time sheet is loaded/generated; replace these functions in the FM DB with the formulas to fetch dynamic data). Of course, you may want to just replace these with a calculation field based upon the symbol name, to generate the functions. Also included is a timestamp field to allow Filemaker to know when the last time the data was updated.

Two FM scripts are included; one generates the Excel file (meant to be run when ticker symbols change, or if Excel file didn't already exist). It uses a VBSCRIPT to set open Excel, add a workbook, define the cell contents, save the Excel file, close the workbook, and then quits Excel and imports the results. It deletes worksheets 2 and 3 to prevent FM from presenting a dialog asking which worksheet to import. I also included a script to get the timestamp from the Excel file itself using DOS commands; the script waits until the file is updated before importing it, thereby preventing a scenario in which Filemaker is attempting to open the file (and locks it) while Excel is trying to save it, another issue with the other proposal.

The second script opens the file, then re-saves it, to refresh the data, then imports back into FM. This is appropriate only if the ticker symbols haven't changed, but is a bit quicker; especially if there are hundreds or thousands of symbols coming in. If you already have MooPlug, ScriptMaster, etc you can use those to check the timestamp on the Excel file; they're much cleaner with a single function to do this.

For importing, it matches the ticker symbols to the FM database so the prices fall into the correct field.

As discussed, there are numerous problems with the other methods discussed. Since the ticker symbols being monitored likely change, you need to generate the Excel file dynamically; exporting an Excel file from Filemaker with formulas, won't cut it, because it won't auto-update, requires answering a dialog before Excel will even load it, etc, etc.

This solution does check the timestamps to determine if data is fresh. The DOS timestamp doesn't include seconds, so for that I just check if it's within 2 min of the current time. Much better than waiting a fixed duration and importing -- if a virus scan is running or computer is bogged down, it may sometimes take longer.

A bit off-topic, but, most of my professional work has been in industrial process control where reliability is paramount; in this environment, PLC's handle the application execution layer and computers handle only data acquisition, configuration, and presentation of the operator interface. Adding more computers to an application, typically decreases application reliability, unless they're redundant. A reliability scale might go like this: embedded controller>>>PLC>>>single computer>>multiple computers (without redundancy). Putting this in perspective, when is the last time your alarm clock crashed vs your PC needed rebooted?

If the Excel file is set to auto-save, what happens if Filemaker happens to import it when it's saving? File locking will potentially cause errors, halting Excel and reducing reliability.

If Excel quits, or the data isn't fresh, all this must be dealt with in the code (e.g. by placing timestamp =NOW() in cells adjacent to the prices or in a cell at top of worksheet). Every computer added to the solution requires additional maintenance and upkeep. The import script needs to check the timestamps of the data to be sure what it's importing is fresh (it will not be if Excel stops running). These are some of the reasons why a C++ plugin is a preferred resolution.

Since Filemaker uses non-standard paths, conversion to UNC is included in the script logic; each script sets filepaths and filenames at the beginning, to the Documents folder using the Filemaker environment variable to get the path to Documents.

Import through another mechanism (XML, webservices) likely won't become possible if it's a legacy application; in a typical scenario, upgrades are cost-prohibitive or no longer available. If those upgrades are possible they're the optimal choice.

DDE is problematic within Filemaker (without a plugin to provide standard DDERequest, DDEPoke, etc as discussed) because it supports only DDE Execute. Put it this way: You can't even tell Explorer to navigate to a web page, unless an Internet Explorer is already loaded. Trying to keep track of how many copies of an app are running, and which window is active, with a single DDE Execute capability, is problematic. Filemaker's implementation of DDE is half-baked, virtually useless.

kluge.zip