help capture the data

[jasonjo]

I have been working on a project where I have information coming from a PLC and ending up in excel. This much is done. My problem comes when I try to take the data from the cell it is in and make a running spreadsheet with the information. I need to shift information in every hour. Does anyone have an example of how I can shift the information into blank cells.

 

[CautionMP]

jasonjo,
PLC? Programable Logic Controller? How are you getting the info into Excel? Is the information pushed in (from the PLC), or are you pulling infromation (from Excel)?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jasonjo]

I am using a siemens programmable logic controller to track our process. With the information I get get from the machines via PLC. I have a program called PC access. This allows me to pull values from counters and timers in the plc and display them into excel. My problem comes when I have the information in a cell, I need to update this information once an hour and store the previous values in another cell. Not sure how I need to do this. I am just learning about vba's and how to use them.

 

[SkipVought]

Hi,

"...take the data from the cell it is in and make a running spreadsheet with the information."

What does that mean? We cannot read your mind.

Does the incoming data always come into the same cell?

Is it ONLY one cell or do the number of cells vary?

What triggers this input event?

Where does the data need to go after it inputs?



Skip,

_[red][/red]

 

[firefytr]

So you are 'pushing' this data into Excel? What is your PLC interface program? is that only the 'PC Access' program or are you using anything else?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[jasonjo]

The pc access program is the only interface program I am using. The data is always being monitored in excel as it is continuosly updated...There are constant cells that the data is displayed. The data is always going to be in the same cell.

There are about 4 cells of data that will need to be shifted into an array or a spreadsheet. Not sure what kind of spreeadshhet I will be using.

But I have to keep a running log of the data. The data will need to shift into a spreadsheet at the top of every hour. I will be using a clock to shift the data.

Hopefully something like =IF minute=00 then shift

I hope you understand what I am trying to do. Sorry if its not clear.

 

[firefytr]

So basically, when this information updates your Excel sheet, you want to (logically) run a check on the minute of a certain cell value, if it equals 0 (top of every hour), you want to move some information to another sheet, which will grow with every hours information.

Does that sound right? If so, do you always know the workbook and worksheet and cell(s) this data is going into? We can do this with a cell change event if this is the case.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[jasonjo]

That is exactly what I am trying to do. As far as the worksheet goes I dont have one created yet but it will always be the same workbook but will have to find the next empty cell to put the data in.

Jason

 

[firefytr]

Is that something you manually find? If not, what is the total process(es) taken by 'PC Access'? Is the data moved in one swift move or is it moved repeatedly (in each hours data move)?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[jasonjo]

Pc access continuously feeds data into excel. I am only going to capture thiss data every hour.

The data is always feeding into one cell. I just have to look at the cell and feed its contents into a worksheet.

 

[firefytr]

So if the data is continuously fed, is it pushed every few minutes? Seconds? Also, what is the range or dynamic range that you want to copy? And which cell contains the time value you wish to check?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[jasonjo]

The data is pushed every 100ms. The data is pushed into cell A1. The range is two digits if thats what you mean?

 

[firefytr]

Alright, so there is a time value pushed into A1 when the data is transfered. Now, what data do you want to move to another sheet (based on the value of A1)? Certain columns? Cells? Rows? Values with a criteria?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci