update other prog. while running VB-Excel macro

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?
question4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
jeffcameronCommented:
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.  
0
 
question4Author 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.
0
 
question4Author Commented:
see above
0
 
jeffcameronCommented:
' use this routine for the control
Dim PauseTime, Start, Finish, TotalTime

sub Run
      PauseTime = 5 * 60      ' Set duration.
      Start = Timer      ' Set start time.
      Do While Timer < Start + PauseTime
            DoEvents      ' Yield to other processes.
      Loop
      
        Worksheets("SheetName").Calculate
end sub

' use one of these events to get the value when it changes with ' the previoes code to pull the value when it changes

' these can be found in the sheet objects by double clicking on ' the sheet from the VBA project window

Private Sub Worksheet_Calculate()

' something like this
Range("B3").copy Range("B4")

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub

' if your other program is not delivering you the correct answer ' though then it will not work
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
question4Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.