Hi folks,
I am refining the MS Excel DDE interface and would like to know if it's working with other versions of Excel. Can you give me some feedback please? Extract the exe to a folder that contains *.xls files, select some cells and click RichLink or PoorLink. No changes will be made to the *.xls file unless you click the Write button.
Source is also included, but it won't build properly for the time being - this is work in progress :cool:
Hi JJ
The application works without crashing. It reflects changes using rich- and poorlink.
Unfortunately, the write process fails somehow and displays a dialog box with the message
Quote"### Write R9C2 NOTPROCESSED ###"
It seems that the program doesn't recognize .xlsx files.
Biterider
Interesting, thanks. Which Excel version is that? If you load an xls file: same behaviour?
Hi JJ
It is Excel version 2013 (15.0.5179.1000 32 bit).
The described write problem is using an xls file. If you have an xlsx file, it pops up a a warning message, that no excel file is found in this directory.
Biterider
Ah, ok - that's a minor problem because I just let it search for *.xls files (corrected in the attached version). It works fine with *.xls
x, too.
The message instead signals some kind of write protection, e.g. when using the (read-only) Excel viewer, see below. Is your file write-protected, or is the macro security set to a high level?
(http://www.jj2007.eu/pics/RichLink.jpg)
It works fine here on Win7-64 with Excel 2003, Excel Starter 2010 and the Excel viewer (no writing for the latter, of course).
Attached a new version, plus one xls
x file for testing.
Some consider DDE obsolete, but recently Microsoft seems to support it again, at least with Excel and Access. I wonder if the functionality of this proggie can be achieved with COM, too :cool:
DDE Function (https://support.office.com/en-us/article/dde-function-79e8b21c-2054-4b48-9ceb-d2cf38dc17f9)
QuoteAccess for Office 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007
This from May 2018, About Dynamic Data Exchange (https://docs.microsoft.com/en-gb/windows/win32/dataxchg/about-dynamic-data-exchange):
QuoteUses for Windows Dynamic Data Exchange
DDE is most appropriate for data exchanges that do not require ongoing user interaction. Usually, an application provides a method for the user to establish the link between the applications exchanging data. Once that link is established, however, the applications exchange data without further user involvement.
DDE can be used to implement a broad range of application features — for example:
Linking to real-time data, such as to stock market updates, scientific instruments, or process control.
Creating compound documents, such as a word processing document that includes a chart produced by a graphics application. Using DDE, the chart will change when the source data is changed, while the rest of the document remains the same.
Performing data queries between applications, such as a spreadsheet querying a database for accounts past due.
Hi JJ
The .xlsx problem still remains here. It starts to work if I copy an xls file into the directory.
Biterider
Quote from: Biterider on January 02, 2020, 04:52:02 AM
The .xlsx problem still remains here.
Yep, wrong version - sorry! But can you write now to the file?
Quote from: jj2007 on January 02, 2020, 07:12:07 AM
Quote from: Biterider on January 02, 2020, 04:52:02 AM
The .xlsx problem still remains here.
Yep, wrong version - sorry! But can you write now to the file?
Hi jj2007,
Office 2019, the program starts, but that's all ..
---------------------------
xls:
---------------------------
### Command [app.activate()]: NOTPROCESSED ###
---------------------------
OK Cancel
---------------------------
Thanks, LiaoMi :thup:
xlsCommand "[app.activate()]" doesn't do much, and there is no particular reason why it should choke. But it seems that DDE is working
in principle, because xlsConnect (http://www.jj2007.eu/MasmBasicQuickReference.htm#Mb1085) worked.
I found several sites (e.g. here (https://getadmx.com/?Category=Office2016&Policy=excel16.Office.Microsoft.Policies.Windows::L_Ignoreotherapplications) and here (https://docs.microsoft.com/en-us/office/troubleshoot/excel/excel-opens-blank)) suggesting this:
QuoteClick the Office (2007) button.
Click Excel Options.
Click Advanced.
Scroll down to the General section.
Make sure that the check box "Ignore other applications that use Dynamic Data Exchange (DDE)" is clear (not ticked).
Click OK.
Apparently this is also the official Microsoft solution (//http://) :cool:
Hi JJ
Version 3 works now with .xlsx files.
Writing is still a problem. I deactivated all security options in the Trust Center but still no success.
Biterider
Quote from: Biterider on January 02, 2020, 07:07:55 PMWriting is still a problem. I deactivated all security options in the Trust Center but still no success.
Thanks for testing this, Biterider. DDE has always been messy and badly documented, but
if it runs, it is very powerful. Apparently Microsoft got pressure from Bloomberg and others to reactivate it in the new Office versions :tongue:
Excel "write" doesn't work for me, no matter how I try, everything else works fine :thup:
https://docs.microsoft.com/en-us/windows/win32/api/_dataxchg/ (https://docs.microsoft.com/en-us/windows/win32/api/_dataxchg/)
Thanks for testing this. I just launched my Win10 machine, it has Excel 2003 installed but when I open an .xls or .xlsx the Windows installer is showing up telling me it must configure the free Excel viewer. Every time it does that, mysteries of M$ :cool:
And it fails partly with strange messages, but I can see the rich text table after a while... the usual Micros**t mess :sad:
Where can I download DDESpy?
https://stackoverflow.com/questions/14923011/where-can-i-download-ddespy (https://stackoverflow.com/questions/14923011/where-can-i-download-ddespy)
Quote from: LiaoMi on January 11, 2020, 05:48:25 AM
Where can I download DDESpy?
Thanks a lot, LiaoMi - DDESpy helped me to discover a weird little bug: for a DdeClientTransaction, I had passed a string handle instead of a string pointer. It still worked, strangely enough, but DDESpy reported a problem.
I am not sure, though, whether that fixed the
Write button problem for you. Here it works fine with Win7-64, Win10 Home and my XP VM.
Grateful for a test :thup:
Hi JJ
Unfortunately, the write operation still fails (### Write R9C2: NOTPROCESSED ###).
One thing I noticed now is that if the workbook is open when you start your application, it completely fails and displays a Chinese message instead of the table contents.
Biterider
Yes, I know about the chinese stuff. It needs more error checking.
But I really wonder why you can't write. That works fine for me with Excel 2003 and Excel Starter 2010, on WinXP, Win7 and Win10. Can you manually edit the spreadsheet? Same problem with one of your own spreadsheets? Sometimes the OS restricts editing of files downloaded from the Internet, see https://thirtysix.zendesk.com/hc/en-us/articles/202921675-How-to-Unblock-a-File-Downloaded-from-an-Email-or-the-Internet
Hi jj
I'm able to modify the content of the I2 cell or any other in the spreadsheet. No issue there.
Biterider
Hi jj2007,
:biggrin: I also came across Chinese words. Now write works for me... A problem may occur if there is no permission to edit the file. Then you need to restart the entire process, after that it works. Another problem may arise if another document is opened in excel. In this case, it is not possible to contact the document. But basic functions work when everything is started correctly.
Thanks to both of you :thup:
Biterider, can you try using Z9S2 instead of R9C2 under the Write button?
QuoteThe cell-column notation of ServerItem is different in different localized version of Office, such as, when using R1C1 style in a localized version of Excel, you have to use the localized R1C1 string, in German it is Z1S1, in French it is L1C1 and e.g. in Spanish it is F1C1, in Polish W1K1
Hi JJ
That did the trick, localization! :thumbsup:
Biterider
Here is an interesting project, it has descriptors for all(Project) functions from user32.dll, dde-xltable-server-master.zip\dde-xltable-server-master\docs\NDde\Source\NDde\Internal\DDEml.cs - ZIP archive, unpacked size 6 200 956 bytes
public delegate IntPtr DdeCallback(
int uType, int uFmt, IntPtr hConv, IntPtr hsz1, IntPtr hsz2, IntPtr hData, IntPtr dwData1, IntPtr dwData2);
[DllImport("kernel32.dll")]
public static extern int GetCurrentThreadId();
[DllImport("user32.dll", EntryPoint="DdeAbandonTransaction", CharSet=CharSet.Ansi)]
public static extern bool DdeAbandonTransaction(int idInst, IntPtr hConv, int idTransaction);
[DllImport("user32.dll", EntryPoint="DdeAccessData", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeAccessData(IntPtr hData, ref int pcbDataSize);
[DllImport("user32.dll", EntryPoint="DdeAddData", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeAddData(IntPtr hData, byte[] pSrc, int cb, int cbOff);
[DllImport("user32.dll", EntryPoint="DdeClientTransaction", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeClientTransaction(
IntPtr pData, int cbData, IntPtr hConv, IntPtr hszItem, int wFmt, int wType, int dwTimeout, ref int pdwResult);
[DllImport("user32.dll", EntryPoint="DdeClientTransaction", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeClientTransaction(
byte[] pData, int cbData, IntPtr hConv, IntPtr hszItem, int wFmt, int wType, int dwTimeout, ref int pdwResult);
[DllImport("user32.dll", EntryPoint="DdeCmpStringHandles", CharSet=CharSet.Ansi)]
public static extern int DdeCmpStringHandles(IntPtr hsz1, IntPtr hsz2);
[DllImport("user32.dll", EntryPoint="DdeConnect", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeConnect(int idInst, IntPtr hszService, IntPtr hszTopic, IntPtr pCC);
[DllImport("user32.dll", EntryPoint="DdeConnectList", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeConnectList(int idInst, IntPtr hszService, IntPtr hszTopic, IntPtr hConvList, IntPtr pCC);
[DllImport("user32.dll", EntryPoint="DdeCreateDataHandle", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeCreateDataHandle(int idInst, byte[] pSrc, int cb, int cbOff, IntPtr hszItem, int wFmt, int afCmd);
[DllImport("user32.dll", EntryPoint="DdeCreateStringHandle", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeCreateStringHandle(int idInst, string psz, int iCodePage);
[DllImport("user32.dll", EntryPoint="DdeDisconnect", CharSet=CharSet.Ansi)]
public static extern bool DdeDisconnect(IntPtr hConv);
[DllImport("user32.dll", EntryPoint="DdeDisconnectList", CharSet=CharSet.Ansi)]
public static extern bool DdeDisconnectList(IntPtr hConvList);
[DllImport("user32.dll", EntryPoint="DdeEnableCallback", CharSet=CharSet.Ansi)]
public static extern bool DdeEnableCallback(int idInst, IntPtr hConv, int wCmd);
[DllImport("user32.dll", EntryPoint="DdeFreeDataHandle", CharSet=CharSet.Ansi)]
public static extern bool DdeFreeDataHandle(IntPtr hData);
[DllImport("user32.dll", EntryPoint="DdeFreeStringHandle", CharSet=CharSet.Ansi)]
public static extern bool DdeFreeStringHandle(int idInst, IntPtr hsz);
[DllImport("user32.dll", EntryPoint="DdeGetData", CharSet=CharSet.Ansi)]
public static extern int DdeGetData(IntPtr hData, [Out] byte[] pDst, int cbMax, int cbOff);
[DllImport("user32.dll", EntryPoint="DdeGetLastError", CharSet=CharSet.Ansi)]
public static extern int DdeGetLastError(int idInst);
[DllImport("user32.dll", EntryPoint="DdeImpersonateClient", CharSet=CharSet.Ansi)]
public static extern bool DdeImpersonateClient(IntPtr hConv);
[DllImport("user32.dll", EntryPoint="DdeInitialize", CharSet=CharSet.Ansi)]
public static extern int DdeInitialize(ref int pidInst, DdeCallback pfnCallback, int afCmd, int ulRes);
[DllImport("user32.dll", EntryPoint="DdeKeepStringHandle", CharSet=CharSet.Ansi)]
public static extern bool DdeKeepStringHandle(int idInst, IntPtr hsz);
[DllImport("user32.dll", EntryPoint="DdeNameService", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeNameService(int idInst, IntPtr hsz1, IntPtr hsz2, int afCmd);
[DllImport("user32.dll", EntryPoint="DdePostAdvise", CharSet=CharSet.Ansi)]
public static extern bool DdePostAdvise(int idInst, IntPtr hszTopic, IntPtr hszItem);
[DllImport("user32.dll", EntryPoint="DdeQueryConvInfo", CharSet=CharSet.Ansi)]
public static extern int DdeQueryConvInfo(IntPtr hConv, int idTransaction, IntPtr pConvInfo);
[DllImport("user32.dll", EntryPoint="DdeQueryNextServer", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeQueryNextServer(IntPtr hConvList, IntPtr hConvPrev);
[DllImport("user32.dll", EntryPoint="DdeQueryString", CharSet=CharSet.Ansi)]
public static extern int DdeQueryString(int idInst, IntPtr hsz, StringBuilder psz, int cchMax, int iCodePage);
[DllImport("user32.dll", EntryPoint="DdeReconnect", CharSet=CharSet.Ansi)]
public static extern IntPtr DdeReconnect(IntPtr hConv);
[DllImport("user32.dll", EntryPoint="DdeSetUserHandle", CharSet=CharSet.Ansi)]
public static extern bool DdeSetUserHandle(IntPtr hConv, int id, IntPtr hUser);
[DllImport("user32.dll", EntryPoint="DdeUnaccessData", CharSet=CharSet.Ansi)]
public static extern bool DdeUnaccessData(IntPtr hData);
[DllImport("user32.dll", EntryPoint="DdeUninitialize", CharSet=CharSet.Ansi)]
public static extern bool DdeUninitialize(int idInst);
There is a help file.. dde-xltable-server-master.zip\dde-xltable-server-master\docs\NDde\Documentation\Documentation.chm.
And there are examples for visual basic. The source code is easy to read, so you can understand the architecture.
https://github.com/afedyanin/dde-xltable-server (https://github.com/afedyanin/dde-xltable-server)
https://github.com/afedyanin/dde-xltable-server/archive/master.zip (https://github.com/afedyanin/dde-xltable-server/archive/master.zip)
ADVANCED SERIAL DATA LOGGER - https://www.aggsoft.com/asdl-excel-read-via-dde.htm (https://www.aggsoft.com/asdl-excel-read-via-dde.htm)
Expose EPICS PVs via Windows Dynamic Data Exchange (DDE)
WinDDEDriver - an EPICS driver to share process variables via the Windows DDE protocol
This is an asyn based driver that you can use in two ways:
you can add DDE support to an existing IOC
you can create a standalone application to monitor process variables defined elsewhere and expose these via DDE
Only integer, double and string (plus char waveforms) data types are supported
Requires EPICS asyn 4-32 or higher - edit configure/RELEASE
The driver works by exposing asyn parameters as DDE items in a "getPV" topic. The name of the DDE service is specified via the WinDDEConfigure() command in st.cmd
The asyn parameters are created dynamically, there is no need to have these specified and compiled in the driver source code itself. Instead just reference the parameter name in the EPICS DB file you load from st.cmd and it will get created automatically at init time. The only caveat is that you need to make sure the asyn "address" parameter is specified correctly as this is used to decide on the data type for the parameter. So you would use:
field(OUT, "@asyn($(PORT),1,0)double1")
within e.g. an EPICS ao record to tie it to the "double1" DDE item, note the "1" passed as the asyn address in this case is to indicate a double data type. See WinDDETest.db for more explanation and an example. To see changes pushed via DDE from elsewhere you could also either create an ai record mapped to the double1 parameter scanning at "I/O Intr", or you could use the asyn:READBACK info record on the original ao record.
https://github.com/ISISComputingGroup/EPICS-WinDDE (https://github.com/ISISComputingGroup/EPICS-WinDDE)
Even drivers use this message model :azn:
Network DDE Agent
[Network DDE is no longer supported. Nddeapi.dll is present on Windows Vista, but all function calls return NDDE_NOT_IMPLEMENTED.]
The network DDE agent starts network DDE if it detects local network DDE activity. It does not detect a remote client trying to connect. Therefore, before any client can successfully connect, network DDE must be started on the server computer. Note that network DDE is not started by default. To start network DDE, run NETDDE.EXE. This file is located in your Windows directory.
The network DDE agent also starts the applications necessary for network DDE. After network DDE is started, DDE conversations are controlled through a network DDE window associated with one of the network DDE applications. This application acts as a proxy. It communicates with all local and remote DDE applications.
https://docs.microsoft.com/en-us/windows/win32/ipc/network-dde-agent (https://docs.microsoft.com/en-us/windows/win32/ipc/network-dde-agent) Will the service work on windows 10 ?!
Using NetDDE in Windows7 - https://social.technet.microsoft.com/Forums/ie/en-US/bc5fa20a-6ecb-4132-98d9-d0523ce2e454/using-netdde-in-windows7?forum=w7itprogeneral (https://social.technet.microsoft.com/Forums/ie/en-US/bc5fa20a-6ecb-4132-98d9-d0523ce2e454/using-netdde-in-windows7?forum=w7itprogeneral)
NetDDE can still be installed via old installs onto newer windows versions. But Microsoft only had a Netbios-only license acquired from Wonderware. Therefore TCP/IP never worked. To use NetDDE you need to install the Netbios protocol also, or contact Wonderware for an updated version of NetDDE.
Dynamic Data Exchange Management Library - https://docs.microsoft.com/en-us/windows/win32/dataxchg/dynamic-data-exchange-management-library (https://docs.microsoft.com/en-us/windows/win32/dataxchg/dynamic-data-exchange-management-library)
Quote from: Biterider on January 13, 2020, 01:01:39 AM
That did the trick, localization! :thumbsup:
Great :biggrin:
I suddenly remembered this issue. I have three Excel versions, two of them are English, one is Italian. By accident, EN and IT both use R1C1 - it's "riga" and "colonna" in Italian, so I didn't notice that writing has this issue.
The decision of Micros**t to use the R1C1 reference notation in macros has, apparently, driven many coders to consult a shrink. It's messy and buggy (https://stackoverflow.com/questions/20994441/using-r1c1-notation-in-different-languages), try googling excel "r1c1" "Z1S1" :cool:
Here is a dedicated page for translations (https://www.excel-function-translation.com/)