Posts by Pathis7

The idea is right, in that adding a delay reduces the number of clicks to 2. Now two clicks will retrieve the correct data, but the wait is truly long for the purpose.

EDIT: I was wrong, hasty clicking makes it three. The wait actually did not really do anything. Any idea why it takes calling the macro twice completely, to get the correct values? It seems there is a buffer somewhere in Excel that is storing the values I get from MyProgram. The buffer starts out empty, so the first time I click (run the macro) I get N/A, but then the values Excel receives are immediately put in this buffer. So the next time I run the macro, the values from the buffer (the previous values) are put in place and the next set of values are taken from MyProgram and put in the buffer.

Sorry for the crude analysis, but this is just what appears to be happening.

I tried adding the same instructions twice in the same macro, but there were no differences. Any ideas?

yes. a command button assigned to the before-said macro. It usually takes only one click and now the copy-special paste enhanced macro ensures 2-3 clicks, sometimes displaying intermittent values (the previous item value). But always on the third click it is the correct value.

Derk, your first suggestion did not work as specified, however your second suggestion, which I guess is akin to Barrie's solution, worked. In any case, I am very pleased you both responded with such accurate solutions so quickly.

It works now. I can update the cells individually.

But I am curious now. Is there anyway to have this done in just one click of the button? Currently, I have to click twice, sometimes even 3 times to get the proper update (never more than 3 times though).

Thanks again!

I am having a problem with creating an Excel spreadsheet that uses DDE to put information from another program into specific cells in Excel.

I am creating buttons and assigning the macro to it, so when the button is clicked, the macro is executed.
The macro is along the lines of this:

Sub GetMyItem()
Sheets("Sheet1").Cells(27,7).Formula = "=MyProgram|MyTopic!'MyItem'"
End Sub

This works fine, in that I will execute the macro and the desired item information will go directly into cell 27,7. But the item in MyProgram will change, and I want to put each item in the SAME Excel spreadsheet. So then I create other macros with the cell different, but the rest the same since it is the same Item:

Sub GetMyItem2()
Sheets("Sheet1").Cells(29,7).Formula = "=MyProgram|MyTopic!'MyItem'"
End Sub

and on and on...

But since this basically puts a formula in the cell, which is updated any time I press the button, ALL of the Items are updated to the Current Item in MyProgram. That is, if The item in MyProgram was 12, and I call GetMyItem, it puts 12 in cell 27,7. But then the item changes to 23, and when I call GetMyItem2, BOTH cell 27,7 AND 29,7 will change to 23. I believe this is normal for Excel, but not what I want.

I think what I need is for each macro to retrieve the value from MyProgram|MyTopic!'MyItem' and store it in a variable X. Then put X in the prescribed cell. How do I do this?? Urgent help is needed.