How to use DDE from Visual Basic to Excel Sheet 1

[asimasm]

I have a VB Application that communicates with Excel using DDE to pass data to excel. I use the code below and it works fine and outputs the data to Sheet1.
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|test.xls"
Text1.LinkItem = "R1C2"
Text1.LinkMode = 1
Now i want to out this to Sheet2 of Excel instead of Sheet1. Can any one tell me how that can be done. I had success with something like this:

Text1.LinkTopic = "Excel|Sheet2"

but this creates a problem that if any excel file is open it outputs the data to its Sheet2.

I Also tried
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|test.xls"
Text1.LinkItem = "Sheet1!R1C2"
Text1.LinkMode = 1
But it gives me an error message:
"Foreign application won't perform DDE method or operation"

Text1.LinkMode = 0
Text1.LinkTopic = "Excel|test.xls Sheet1"
Text1.LinkItem = "R1C2"
Text1.LinkMode = 1

Similarly i tried and it gives the same error.
Can anyone help me in this problem.

 

[dtqbterror]

Why are you using DDE instead of COM/OLE?

http://www.chiefscoalition.com

 

[asimasm]

Hi,
I am dumping data in Excel Sheet which is then used by another application (Also using DDE). OLE Automations locks the Sheet for some time which causes both applications to hangup. My DDE Solution is working fine only that now new requirements from client need nformation to be placed on a seperate a seperate sheet.

 

[HughLerwill]

I think the DDE code you are using will write data to the current workbook.

So you could/should possibly use DDE to command Excel to make the correct workbook current first.
The article 'DDE from Visual Basic for Windows to Excel for Windows' Q75089 may help. Although I don't think this (circa 1995) is still listed in the online MSKB. It is listed when you search vb help for dde.
Exploration of Excel's system topic may reveal some clues to you.
Decent help with regards to DDE is pretty hard to find...

I would be tempted to use Automation to load/make the correct workbook current and then only use DDE if you must.

HTH Hugh,

 

[HughLerwill]

Ok we're getting somewhere;

.LinkTopic = "Excel|C:\[MyBook.xls]MySheet"
.LinkItem = "R1C1"

This code is using the correct sheet for me when more than one sheet is loaded into Excel.

HTH Hugh

 

[HughLerwill]

And something like this should load the required Workbook into Excel if it not already (Excel must be running already);

With Text1
'load require workbook
.LinkTopic = "Excel|System"
.LinkMode = vbLinkManual
.LinkExecute "[OPEN(" & Chr$(34) & "C:\MyBook.xls" & Chr$(34) & ")]"

'link to cell
.LinkTopic = "Excel|C:\[MyBook.xls]MySheet"
.LinkItem = "R1C1"
.LinkMode = vbLinkAutomatic

End With

Useful ref.

http://www.bigbold.com/snippets/posts/show/3112

HTH Hugh,

 

[asimasm]

Thanks HughLerwill. That was really helpfull. It solved my problem. Actually i did try this earlier but the i was putting a blank space between the Workbook and sheet name. Many Thanks once again.

 

[HughLerwill]

Good

For a full list of the Excel v4.0 macro commands available in Excel (v4.0 and later) via DDE, download Macrofun.exe/hlp from

http://support.microsoft.com/kb/128185

regards Hugh,