A question regarding DDE in Excel

[greenbambootea]

I had an problem. I am doing a small project in Excel. Say that in a cell, I put something like "cqgpc|F.CLEK07!bid", the data from cqgpc is going to show in that cell and keeps updating. however, if I DONOT this (means there is no cell in the worksheet has this "cqgpc|F.CLEK07!bid"), and I use the following code try to show the data in a cell, I always get error 2015.

Sub Initiate_DDE()
Dim channelNumber As Long
myData
channelNumber = Excel.DDEInitiate("cqgpc", "F'.CLEK07")
myData = Excel.DDERequest(channelNumber, "Bid")
Sheets("sheet1").Select
Cells(1, 1).Value = myData
End Sub


However, if I keep "cqgpc|F.CLEK07!bid" in another cell, my code works and the real data do show up. Can anyone please tell me what the problem is? Thanks a lot.

 

[HughLerwill]

1. The line of code 'myData' should be changed to 'Dim myData'
2. The topic name "F'.CLEK07" you are using in your code is not the same as the one used in the cell "F.CLEK07"

Otherwise your code is working for me without error or needing a cell having a DDE formula on the sheet. While it works I would change the word Excel in your code to Application in both cases.

Your topic name bothers me a little in that it contains a period but if it works in a formula on the sheet I guess it should work in code too. My tests were done with a topic of "MyForm" this being a DDE serving Form in a VB6 app. My item was a label on that form called LabNumber.

 

[greenbambootea]

Thanks a lot. I just tried again and it works. I do not know why I made such a stupid mistake and spend hours on it. I guess that I do not have that ' at first. Then, somehow the code did not work. Then I read somewhere says that special symbols need to be inside ' ' and then I tried it again. After that, I somehow deleted the right ' but forgot the left '. What a waste of time. I am so glad it is solved now. Thanks a lot.

 

[greenbambootea]

I do have an additional question if someone can kindly answer as well. In my code, I roughly need to read more than 300 DDE data (1 program, 100+ topics and 300+ symbols). Since I need to do some computation and generate a table based on these data and these data keeps changing almost every second, I need to run my code as loops and keep running all the time (about 6-7 hours). At the beginning of each loop, I am going to read the DDE data. In this case, the best way for me to do is to

step 1. DDEInitiate
step 2. DDERequest
step 3. DDETerminate

for each individual topic in each run or just do DDEInitiate before the first run, and do DDERequest in each run and do DDETerminate after the final run? Which way is better? Thanks.

 

[HughLerwill]

1) There is no need to Initiate and Terminate before/ after each Request as long as the Topic remains constant. Doing them will almost cetainly be slower, so as long as everything is reliable faster is better.

2) If your source app uses control arrays (e.g. it is a vb6 app) you may be able to use code like that below as an example.


Sub mymac()

Dim channelNumber As Long
Dim i As Integer

channelNumber = Application.DDEInitiate("AppName", "TopicName")
For i = 1 To 18
Cells(i, 1) = Application.DDERequest(channelNumber, "ControlArrayName(" & i & ")")
Next
Sheets("sheet1").Select
Application.DDETerminate (channelNumber)

End Sub

 

[HughLerwill]

I strikes me if you require constant update of your cells formula could still be your best solution, you may consider using your code to set them up once at the begining of the run and then just let Excel and DDE do it.

as in...

Sub mymac2()

Cells(1, 2).Formula = "= AppName|Topic!ControlName1"
Cells(2, 2).Formula = "= AppName|Topic!ControlName2"
'etc...

Sheets("sheet1").Select

End Sub

However at present I can not get this to work with Control Arrays as in;

Sub mymac3()

Dim i As Integer

For i = 1 To 18
Cells(i, 2).Formula = "= AppName|Topic!ControlName(" & i & ")"
Next
Sheets("sheet1").Select

End Sub

Can anyone else suggest a way in which Control Arrays can be accomodated in this?

 

[taupirho]

Try something like this:-

Sub mymac3()

Dim i As Integer

For i = 1 To 18
Cells(i, 2).Formula = "equals AppName|Topic!ControlName(" & i & ")"
Next

Columns("B1:B18").Select
Selection.Replace What:="equals", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("sheet1").Select

End Sub

 

[HughLerwill]

taupirho,

That is not working for me, albiet I changed your Columns to Range.

 

[greenbambootea]

The problem is that when I run my code, if I assign a DDE as a formula to a cell and then keep reading the cell, the data does not update. I do not know if this is just for me or is there a magic switch somewhere I can play with. Thanks

 

[greenbambootea]

Forgot to mention that the DDE is dynamic in my case, which means the channel IDs keep changing. That is the reason why I initialize the close the DDE in evevy run. I donot know if there are other ways to get around this problem.

 

[HughLerwill]

If you set "= AppName|Topic!ControlName1" as a formula in a cell and AppName is running and stays running AND you only have a single instance of AppName running AND AppName is DDE compliant the cell should update automatically to show the contents of AppName|Topic!ControlName1. You may set the formula manually or programatically as shown above.

In Excel it appears to me that you cannot have a cell automatically update in this manner except by applying a formula to it.

Alternatively you can update the cell manually/ 'dynamically' via code using stuff like;

Sub mymac()

Dim channelNumber As Long
channelNumber = Application.DDEInitiate("AppName", "TopicName")
Cells(1, 1) = Application.DDERequest(channelNumber, "ControlName")
Sheets("sheet1").Select
Application.DDETerminate (channelNumber)

End Sub
This updates the cell just once and does not create an auto-updated link.

channelNumber = Application.DDEInitiate("AppName", "TopicName")
is only required before the first;
Cells(1, 1) = Application.DDERequest(channelNumber, "ControlName")
and;
Application.DDETerminate (channelNumber)
is only require after the last.

>>DDE is dynamic in my case
Why and how? Once you have done channelNumber = Application.DDEInitiate("AppName", "TopicName") that channel number should stay valid for communications until you do an Application.DDETerminate (channelNumber).

 

[greenbambootea]

The channelNumber keeps changing. For a certain channelNumber, I need to get values from more than one controlname. However, I tried and I found that the channelNumber keeps changing. I contacted the software provider and they confirm this. However, they did not tell me why they want to keep the channelNumber changing.

 

[HughLerwill]

Initially I suspected that the ChannelNumber returned by;
channelNumber = Application.DDEInitiate("AppName", "TopicName")
was going to be a complicated number like a Windows handle number and unpredictable but it is not, if you test its value using;
MsgBox "Channel = " & channelNumber
you should see that it is simply a count(-1) of the number of times you have used;
channelNumber = Application.DDEInitiate("AppName", "TopicName")
without a corresponding;
Application.DDETerminate (channelNumber)
i.e. it is a simple count of the number of DDE channels your Excel.Application object has open and is not determined by the app you are communicating with.

I put it to you that channelNumber is NOT changing for a given channel and that you are percieving a change because you are opening a new channel (unnecessarily) instead of continuing with the one you already have open.

You should post the exact code you are using.