Excel spread sheet help 1

[LoggerHead]

I have excel getting data from my wonderware program. Every time my data changes in wonderware it is updated in excel. My problem is that I have a formula in one cell to gather this data. Each time the value changes in my wonderware program it changes the value in that cell. What I am trying to accomplish is to create a data base that samples that data at a regular interval and stores it in a vertical list in my spread sheet. In other words every five minutes I would like to write the new data in a cell directly below the previous cell without losing or changing the data in the cell above it. I'm not sure if this is the proper forum for this question but after looking at some of the topics it seems to be a good place to start. Any help or direction would be very helpful.
Thanks in advance,
Daymond

 

[TomBarsh]

I think the solution depends upon the formula that is in your one cell. How does it obtain the information from wonderware? More details would help.

It seems likely that you will need to write an Excel VBA ("Visual Basic for Applications") routine to get the information from the other software and then write it to the spreadsheet, in a new cell for each new reading of data. Since you wrote the original cell formula, doing the equivalent operation in VBA shouldn't be hard for you.

 

[melone]

Could you create a macro that goes into your wonderware software and copies out the data and pastes it into excel. I see 2 advantages to this scenario:

1) You have the actual data in Excel, not a link to the data (which can be broken if a file moves, gets corrupted, etc.).

2) You get to control EXACTLY when the data is sampled.

 

[pmover]

the best solution is to have wonderware accomplish the task, which is possible, instead of excel. wonderware has the capabilities to create historical data in a file that excel can import.

back in the mid-90's i wrote vba code to accomplish the same task you have stated. upon request, i can research and place the code in this forum. however, you should consider having wonderware accomplish the task instead of excel. wonderware does have this capability.

pls advise if you want the code . . .

good luck!
-pmover

 

[LoggerHead]

I'm trying to do this all through excel. We do have wonderware poking some infomation into excel but it is a lot of data and already slows down the system everytime it does this (every 5 minutes). The formula I have in excel is very simple.It is VIEW|TAGNAME!b_fce3_CoolingValveOutput I was thinking that I could write a macro that every 5 minutes would copy whatever data was in the cell with the formula to the cell directly below it and then insert a row between the cell I just copied to and the cell with the formula. I think I can figure that out but I'm not sure how to get it to do this every 5 minutes.

 

[pmover]

Loggerhead,

ok, i'll get the macro posted within the next few days - is at home and no isp at home. summarizing, the macro is included in workbook and activates upon a perscribed time period. the data is placed into another worksheet (solely containing data), but you can modify to suit your needs.

like you stated, the computer system gets "loaded" during the transfer of data from wonderware to excel. something to consider is to only extract the necessary raw data from wonderware and have any performance calcs accomplished by excel. perhaps even change the option to have excel conduct calcs manually instead of automatically. the macro can then update the calcs. having several tags imported into excel certainly does "load" computer system; hence, the recommendation to have wonderware accomplish the data logging instead of excel. your choice . . .

again, i'll get the macro posted soon as i do not have workbook with me - in personal archives. pls be patient . . .

goodl uck!
-pmover

 

[LoggerHead]

That sounds great. I will look into your advice on having wonderware create a historical data base.

 

[pmover]

Loggerhead,

the code below is "crude", but it does work. heck, i wrote it in '94.

basically, this code assumes the excel links to wonderware (intouch ?) are on a sheet named 'unit data' and in a single row with a range name 'HardData'. the performance calcs are done on another sheet named 'plant operation'.

so, when this workbook is opened, the autoopen macro is executed and will activate the 'storedata' macro at the current time + 15 seconds. every time the storedata macro is executed, it will activate again in 15 seconds.

i can only assume that you are familiar with writing vba code so you can modify this code to suit your needs.

hope this helps.

good luck!
-pmover

'
' StoreData Macro
' Macro recorded 8/9/94
'
' Keyboard Shortcut: Ctrl+s
'
' This macro takes the input & results data and stores
' the data on the Unit Data Spreadsheet. This data is
' in turn used in historically analyzing the
' engine & compressor performance or operation. The
' information is mainly used by Engine Analysis and
' Reliability Engineering.
'
Sub AutoOpen()
Application.ScreenUpdating = False
'Application.WindowState = xlMinimized ' Minimizes Excel
' Procedure to Open the Daily Report File
Application.OnTime Now + TimeValue("00:00:20"), "StoreData"
' Procedure to create & Save the Daily Report.
' Application.OnTime TimeValue("07:16:00"), "PrintSaveMaster"
' Application.OnTime TimeValue("07:17:00"), "Eddie"

End Sub
Sub StoreData()
Sheets("Unit Data").Select
Range("HardData").Copy
kount = Application.Sheets("El Paso Unit Data").Cells(3, 2)
If kount = 0 Then
Application.Goto Reference:="R7C1"
Selection.Offset(1, 0).Select
Else
Application.Goto Reference:="R7C1"
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
End If
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
Sheets("Plant Operation").Select
Application.OnTime Now + TimeValue("00:00:20"), "StoreData"
End Sub

 

[LoggerHead]

Thanks alot. I did get a macro wrote that would log the data on a time basis but I had not got the auto run working. This will be helpful and I am new to VB but I think I can fumble my way through making any changes that I need to make. Thanks again.
Daymond

 

[pmover]

loggerhead,

another feature for you to consider . . .

you might want to consider saving the "logged" data on a periodic basis. can easily be done via vba code. collecting data without saving it is worthless.

good luck!
-pmover

 

[hotdipper]

Here is a macro that I use to grab data from a PLC using DDE.
It will collect a set of data and move to the next row for the next set. It also includes a date/time stamp for each row. Changing the value in the for loop defines the number of rows of data to collect. I hope this helps.





'Row / Column positions
'
Static Temp1x
Static Temp1y
Static Temp2x
Static Temp2y
Static Temp3x
Static Temp3y
Static Temp4x
Static Temp4y

'Predefined Column offsets
'
Temp1x = 3
Temp2x = 6
Temp3x = 9
Temp4x = 12

'Find the next unused row by checking for cell that's non-zero
'When it gets to 100, it starts writing over the last cell
'
For i = 1 To 99
If Worksheets("Sheet1").Cells(i, 3).Value = 0 Then
Exit For
End If
Next i

'Display the row index
'
Worksheets("Sheet1").Cells(11, 1).Value = i

Temp1y = i
Temp2y = i
Temp3y = i
Temp4y = i

Channel = DDEInitiate("DSData", "DemoTopic")

Worksheets("Sheet1").Cells(Temp1y, Temp1x + 1).Value = DDERequest(Channel, "V7766:B")
Worksheets("Sheet1").Cells(Temp1y, Temp1x).Value = Now()

Worksheets("Sheet1").Cells(Temp2y, Temp2x + 1).Value = DDERequest(Channel, "V7747:B")
Worksheets("Sheet1").Cells(Temp2y, Temp2x).Value = Now()

Worksheets("Sheet1").Cells(Temp3y, Temp3x + 1).Value = DDERequest(Channel, "V2103:B")
Worksheets("Sheet1").Cells(Temp3y, Temp3x).Value = Now()

Worksheets("Sheet1").Cells(Temp4y, Temp4x + 1).Value = DDERequest(Channel, "V2104:B")
Worksheets("Sheet1").Cells(Temp4y, Temp4x).Value = Now()


DDETerminate Channel

'If cell (1, 2) does not contain a "1", then queue up the macro to run again
'
If Worksheets("Sheet1").Cells(1, 2).Value <> 1 Then
'TimeValue is specified as (Hours:Minutes:Seconds)
'
Application.OnTime Now + TimeValue("00:00:10"), "Scheduler"
End If

End Sub