We help IT Professionals succeed at work.

update other prog. while running VB-Excel macro

question4
question4 asked
on
Medium Priority
171 Views
Last Modified: 2010-04-30
In an Excel spread sheet I have a cell which pulls a value (changing number) from another program with the formula:

"=View|tagname!'x1'"

Now I want to copy the actual value into another cell every 5 minutes (everytime a new cell, because the value is changeing) so that I can see the result after some 3 hours. I wrote a macro in
Visual Basic, but with that macro I do have the same value in each cell, because the other programm is not delivering the new value. Or is Excel not looking for the new one?

Question: How can I copy a changing value from one cell into anther automatically?
Comment
Watch Question

use the calculate event of the worksheet and the copy method of the range object.  keep track of the next location in a global variable.  

Author

Commented:
I think I used the calculate method already. Here is my
VB programm:

Const spalte = 2

Sub test()

    For zeile = 5 To 10
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=View|tagname!'x1'"
    ActiveWorkbook.RefreshAll
    Selection.Copy
    ActiveCell.Clear
    Range(Cells(zeile, spalte), Cells(zeile, spalte)).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B4").Select
    Selection.Copy
    Next zeile
   
End Sub

So how would I use the calculate event according to your answer?
Please clearify. Thank you.

Author

Commented:
see above
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.