DDERequest: Default Excel worksheet when none specified

[evangelmike]

Greetings! One of the blogs has the following:
"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â€

[Marcus Tettmar]

I believe it will always be the first sheet in the workbook if a sheet is not specified.

[evangelmike]

Greetings! I ran a DDEPoke test on my file BookIssuersChgd.xlsm. The first worksheet of BookIssuersChgd.xlsm has no data, and the next 22 worksheets have data. I ran the following macro both with the focus set to BookIssuersChgd.xlsm, and with the focus set to another workbook:

[code]
SetFocus>BookIssuersChgd.xlsm
DDEPoke>Excel,C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookIssuersChgd.xlsm,R1C10,HELLO!!!

SetFocus>BookDates.xlsm
DDEPoke>Excel,C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookIssuersChgd.xlsm,R1C10,HELLO!!!
[/code]

In both cases, row 1 column 10 of the last worksheet added to workbook BookIssuersChgd.xlsm was set to HELLO!!!. I also ran this test numerous times with the focus manually set to the first worksheet and various later added worksheets of BookIssuersChgd.xlsm always with the result that row 1 column 10 of the last worksheet added to workbook BookIssuersChgd.xlsm was set to HELLO!!!. Of course it is possible that under other scenarios a different worksheet will be "Poked".

[evangelmike]

Greetings! Further testing reveals that one may add a new worksheet anywhere within a collection of worksheets--i.e. at the beginning of a collection of worksheets, anywhere in the middle, or at the end of a collection of worksheets. (One adds a new worksheet by rightclicking on any of the worksheet tabs at the bottom of the Excel window and selecting Insert from the menu, or, in VBA, with the Sheets.Add instruction.) When DDEPoke is used to set a value in a workbook and the worksheet is not specified, DDEPoke stores its value in the last worksheet of the collection, not the last worksheet added. May you all have blessed days.