?
Solved

update other prog. while running VB-Excel macro

Posted on 1998-08-17
5
Medium Priority
?
159 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?
0
Comment
Question by:question4
  • 3
  • 2
5 Comments
 
LVL 1

Expert Comment

by:jeffcameron
ID: 1429485
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
 

Author Comment

by:question4
ID: 1429486
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
 

Author Comment

by:question4
ID: 1429487
see above
0
 
LVL 1

Accepted Solution

by:
jeffcameron earned 300 total points
ID: 1429488
' 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
 

Author Comment

by:question4
ID: 1429489
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question