Author Topic: Excel & DDE  (Read 4451 times)

Biterider

  • Member
  • ****
  • Posts: 750
  • ObjAsm Developer
    • ObjAsm
Re: Excel & DDE
« Reply #15 on: January 12, 2020, 05:40:41 PM »
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

jj2007

  • Moderator
  • Member
  • *****
  • Posts: 11588
  • Assembler is fun ;-)
    • MasmBasic
Re: Excel & DDE
« Reply #16 on: January 12, 2020, 09:17:28 PM »
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

Biterider

  • Member
  • ****
  • Posts: 750
  • ObjAsm Developer
    • ObjAsm
Re: Excel & DDE
« Reply #17 on: January 12, 2020, 09:22:58 PM »
Hi jj
I'm able to modify the content of the I2 cell or any other in the spreadsheet. No issue there.
Biterider

LiaoMi

  • Member
  • ****
  • Posts: 925
Re: Excel & DDE
« Reply #18 on: January 12, 2020, 11:23:06 PM »
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.

jj2007

  • Moderator
  • Member
  • *****
  • Posts: 11588
  • Assembler is fun ;-)
    • MasmBasic
Re: Excel & DDE
« Reply #19 on: January 13, 2020, 12:30:42 AM »
Thanks to both of you :thup:

Biterider, can you try using Z9S2 instead of R9C2 under the Write button?

Quote
The 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

Biterider

  • Member
  • ****
  • Posts: 750
  • ObjAsm Developer
    • ObjAsm
Re: Excel & DDE
« Reply #20 on: January 13, 2020, 01:01:39 AM »
Hi JJ
That did the trick, localization!  :thumbsup:
Biterider

LiaoMi

  • Member
  • ****
  • Posts: 925
Re: Excel & DDE
« Reply #21 on: January 13, 2020, 01:14:49 AM »
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

Code: [Select]
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/archive/master.zip

ADVANCED SERIAL DATA LOGGER - 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

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 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
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

jj2007

  • Moderator
  • Member
  • *****
  • Posts: 11588
  • Assembler is fun ;-)
    • MasmBasic
Re: Excel & DDE
« Reply #22 on: January 13, 2020, 01:22:50 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, try googling excel "r1c1" "Z1S1" :cool:

Here is a dedicated page for translations