CX-Server 4.0, Point Data, and Populating an Excel Database

I am reading data from an Omron PLC using CX-Server into an Excel file. The data populates 4 different columns and that data is overwritten everytime the PLC updates. I am trying to figure out a way using Excel, VB and/or any other combo where I can save the values that are in those 4 columns and shift them down a row thereby allowing the new data to come in while saving the old data to populate a database of sorts. My 4 columns look like the following in excel:

=CDMDDE|'C:\Program Files\OMRON\CX-Server\C200HSexcel.cdm'!'\PrintNumber'

=CDMDDE|'C:\Program Files\OMRON\CX-Server\C200HSexcel.cdm'!\DataPoint1

=CDMDDE|'C:\Program Files\OMRON\CX-Server\C200HSexcel.cdm'!\DataPoint2

=NOW()

As it can be seen all of those columns change the second that values in their respective locations change. So I was wondering if anyone knew how to get those values and save or freeze them so they can be inserted below the new/updated values before they are overwritten.

Thanks again!!

reply to mrplc

you should post this to mrplc.com

there are some very knowledgeable omron folks that use this site.

Thanks Rock but I finally figured out my own question and here is the solution in case anyone else runs in to this problem.

I wrote macro for the exel spreadsheet I was working on that looks like this:

Sub Macro1()
If [A5].Value <> [A4].Value Then
Rows("5:5").Select
Selection.Insert Shift:=xlDown
[A5].Value = [A4].Value
[B5].Value = [B4].Value
[C5].Value = [C4].Value
[D5].Value = [D4].Value
Application.OnTime Now + TimeValue("00:00:05"), "Macro1"
End If
End Sub

Basically what it does is it looks on the cell below A4 and checks to see if those to values are the same if they aren't then we know that A4 has been updated and I insert a new row (A5) copy the updated values to the newly inserted row. Then I tell it to run this Macro every 5 seconds so it is constantly checking for updates (or at least faster than the PLC is updating in the Excel spreadsheet.

DefelRadar:
I looked all over for a solution to this issue when you asked for the function. I thought I had done it before but I couldn't lay my hands on the spreadsheet.
Two things come to mind:
1. Wouldn't it be better if you compared A4 >0. What if the value doesn't change in five seconds?
2. Why not just do an insert Shift=x|down every five seconds regardless of whether the value changes or not?
Now granted I'm no macro programmer or I would have come up with the solution already. Maybe I'm missing something.
I know this much. This one's going in my bag of tricks. Thanks!

Here is a nice little feature I've used for printing out and saving. Note when you print this workbook it is saved with the date and time as the file name. This way you can archive the data.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim cur_day, cur_month, cur_year, cur_hour, cur_minute As String
cur_day = Day(Date)
cur_month = Month(Date)
cur_year = Year(Date)
cur_hour = Hour(Time)
cur_minute = Minute(Time)
file_name = cur_month & cur_day & cur_year & cur_hour & cur_minute
Excel.ThisWorkbook.SaveAs (file_name)
End Sub

I don't mean to ressurect an old post, but this is an issue that I know I will have to deal with soon.

Once I get to the step where the PLC writes data into Excel, can I use this code to somehow just auto-save without the printing? Perhaps on a set time or every 8 hours? It's for a factory and it would help to have every 8-hour shift's production numbers logged.

Thanks,
Josh