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:
Sheets("Sheet1").Cells(27,7).Formula = "=MyProgram|MyTopic!'MyItem'"
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:
Sheets("Sheet1").Cells(29,7).Formula = "=MyProgram|MyTopic!'MyItem'"
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.