Acquiring data from MS Excel using DDE

I fully agree that Dynamic Data Exchange (DDE) is not the technology of choice any more these days. But I had to provide a data input functionality today where my brain declined to come up with a more recent solution.

Imagine a 3rd party Lotus Notes application where some data has to be entered. The data is currently stored within a Microsoft Excel spreadsheet. Unfortunately the design of the 3rd party application is far to complex to use the Lotus Notes client provided import functionality, especially not because the whole system is trying to be kind of relational which would make an automated import far to risky. And we are speaking about an on-off import, nothing that will happen on a regular basis. On top of this I was unable to use Lotusscript functionality.

While I pondered all options possible I remembered my early days in software development where connectivity between two applications had been made using DDE.

The solution workaround (do not want to name this piece of code a solution) is basically based on the Windows clipboard and copy and paste. The information in the Excel worksheet is copied to the clipboard and successively pasted to the appropriate field in the Lotus Notes form. All the selection and navigation is actioned from a tool bar button in the Lotus Notes client, which code looks a bit like this:

ConvID:=@DDEInitiate(“EXCEL”;”Names.xls”);
@If (@IsError(ConvID); @Do(@Prompt([Ok];”Error”;”Unable to initiate conversation”);@Return(“”));
@Do(
@Prompt([Ok];”No Error”;”Let’s start”);
@DDEExecute (ConvID;”[RUN(\”GetFirstColumn\”)]”);
@Command([EditGotoField];”Title”);
@Command([EditPaste]);
@DDEExecute(ConvID;”[RUN(\”GetNextColumn\”)]”);
@Command([EditGotoField];”FirstName”);
@Command([EditPaste]);
@DDEExecute(ConvID;”[RUN(\”GetNextColumn\”)]”);
@Command([EditGotoField];”LastName”);
@Command([EditPaste]);
@DDEExecute(ConvID;”[RUN(\”GetNextColumn\”)]”);
@Command([EditGotoField];”Role”);
@Command([EditPaste]);

repeat above lines for additional other fields/columns

@DDETerminate(ConvID)))

As you can see the function does nothing else then calling two background macros in the current Excel document. GetFirstColumn moves the current selection to the first cell in the selected row and GetNextColumn iterates to all the subsequent cells in the table. The field mapping happens with the @Command field selection. The GetFirstColumn and GetNextColumn do nothing else then copying the text value of the first (or next) cell into the clipboard. Which will then be pasted to the assigned field on the form afterwards.

Sub GetFirstColumn()
Dim doClip As DataObject
Dim sText As String
Dim sAddress As String
Dim iSplit As Integer
Dim iLen As Integer
Dim sNewCell As String
sAddress = ActiveCell.Address
iSplit = InStrRev(sAddress, “$”)
iLen = Len(sAddress)
sNewCell = “A” + Mid(sAddress, iSplit + 1, iLen – iSplit)
Set doClip = New DataObject
sText = Range(sNew).Text
Range(sNewCell).Select
doClip.SetText sText
doClip.PutInClipboard
Set doClip = Nothing
End Sub

Thanks to the Daily Dose of Excel where I found the required tip to add only text to the clipboard. My initial attempt to just copy the current range to the clipboard failed, ending up with an error message in Lotus that only text can be pasted to the selected field.

Leave a Reply

Your email address will not be published. Required fields are marked *