Marcus' Macro Scheduler Blog

DDE is an abbreviation for Dynamic Data Exchange and is an old protocol for interfacing with applications. It has largely been superseded by other methods such as COM but Microsoft Excel still supports it and it provides a simple way to access cells in Excel directly.

Use the DDERequest command to retrieve an item of data from Excel. Excel must be running and the spreadsheet must already be open. We could easily make our macro open the worksheet for us using the ExecuteFile command (or Run Program). We then use DDERequest something like this:

DDERequest>Excel,c:\\\documents\\\example.xls,R1C1,
      myVariable,60

The first parameter is the Server name, which is always just Excel for Microsoft Excel. The second parameter, the DDE “Topic”, should be set to the filename of the spreadsheet (which must be open). The third parameter specifies the cell to retrieve. This is constructed RnCn (Row n, Column n). Use numeric values for both Row and Column. So Cell A1 would be R1C1. Cell B1 would be R1C2. Cell D14 would be R14C4, etc. The next parameter is the variable name to store the result in. And finally we specify a timeout value in seconds. If the command fails to locate within that amount of time it will timeout and the result variable would contain “DDE_TIMEOUT”.

The second parameter can also contain a sheet name when you want to access data on a particular worksheet. The Sheet name is added after the filename, separated from the filename by a colon. So parameter two would become: Filename:Sheetname

One thing to note is that Excel often appears to append a CRLF sequence to the end of the data. Therefore we should remove it as follows:

StringReplace>myVariable,CRLF,,myVariable

Take a look at the “Extract From Excel” sample script which comes with Macro Scheduler for an example which uses the DDE method to retrieve data from Excel and paste it into Notepad.

To modify a cell value, or write a value to Excel we use the DDEPoke command as follows:

DDEPoke>Excel,c:\\\documents\\\example.xls,R2C3,
                                      56.75

Again, the spreadsheet must be open for this to work. The above puts the value 56.75 into cell C2 (row 2, column 3). As with DDERequest you can add a worksheet name in parameter two if needed (Filename:SheetName).

For another example see:
Exchange Data with Microsoft Excel The Easy Way

3) VBScript via COM

VBScript offers the most flexible and powerful method of interacting with Microsoft products. Not only can we modify data using this method we can also control almost any part of Excel. Anything that you can program or record in Excel can be converted into VBScript code and used inside a Macro Scheduler script.

Here is an example script with some functions you can use to retrieve and modify Excel cells:

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)
end sub

'Use this to close Excel later
Sub CloseExcel
   xlApp.quit
   Set xlApp = Nothing
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function
VBEND

//Do the business
VBRun>OpenExcelFile,%SCRIPT_DIR%\example.xls
VBEval>GetCell("Sheet1",5,4),theValue
MessageModal>Cell value: %thevalue%
VBEval>SetCell("Sheet1",28,2,998),nul
//VBRun>CloseExcel

This code is constructed in such a way to allow you to call the VBScript functions from regular MacroScript code and therefore retrieve the data to regular variables.

For another example see:
Automate Excel with VBScript

You can also run Excel macros:
Run Excel Macros and VBA Routines

And, as stated above, you can do a whole heap of other things. Search the forums for Excel to find other examples. Converting from Excel VBA to Macro Scheduler VBScript is a topic for another day but hopefully you can get an idea from these simple examples.

___
You may also be interested in: Accessing Databases