Excel Macro Problem - Formula Replacement

    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.

    That makes sense because it's the formula you are using references the same thing. What about changing your code to paste values? Something like:

    Code
    Sub GetMyItem()
Sheets("Sheet1").Cells(27, 7).Formula = "=MyProgram|MyTopic!'MyItem'"
Sheets("Sheet1").Cells(27, 7).Copy
Sheets("Sheet1").Cells(27, 7).PasteSpecial xlPasteValues
End Sub
Sub GetMyItem2()
Sheets("Sheet1").Cells(29, 7).Formula = "=MyProgram|MyTopic!'MyItem'"
Sheets("Sheet1").Cells(29, 7).Copy
Sheets("Sheet1").Cells(29, 7).PasteSpecial xlPasteValues
End Sub

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

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


    or whatever form MyProgram|MyTopic!'MyItem' should be in to actually get the value back to your macro.


    Or, if that is not obvious what to change then the following should work.

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


    Ooops, I see barrie gave a variation of the above.

    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!

    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.

    It may be that the value of the cell is being fixed before the called data has a chance to be returned. Try pausing the macro for a bit and see if that cures the problem. The following should give a 2 second pause for the data to be retrieved. It may take more or less; try experimenting.

    Code
    Sub GetMyItem()
    With Sheets("Sheet1").Cells(27, 7)
        .Formula = "=MyProgram|MyTopic!'MyItem'"
        Application.Wait Now + 2 / (24 * 60 * 60#) 'wait 2 seconds
        .Value = .Value
    End With
End Sub

    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?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login