Hi there guys!
First and foremost, forgive my ignorance. The last bit of programming I did that was in the same family as VBA was just some VB programming I had to do when I was in college.
My dilemma:
I have been tasked with building a maintenance call/downtime (ANDON) tracking system. This system must track maintenance calls, downtime, maintenance response time, maintenance repair time, and the active fault when this downtime occurs. I have no problem building this system, building the PLC program, or getting the data from the PLC to EXCEL using an OPC server. That much is cake for me. My problem is what I have to do with it now.
I am planning on a workbook with a spreadsheet for each machine (just for ease of organization), and then an Active Alarms sheet, and a Downtime Log sheet.
I am hoping someone will have some experience setting up something like this using VBA with excel.
Active Alarms
On this sheet I need to have a range of columns shown from the individual sheets dedicated to each machine, depending on whether or not a maintenance call bit is true in the plc. Now, the difficulty for me is not being able to show this data in excel conditionally, as I will have a cell for each machine that will show TRUE if there is a maintenance call, and FALSE if there is not. The difficult part becomes when I have to have this list of machines show all machines that have active maintenance calls, and show them in descending order, with the most recent active maintenance call at the top.
Downtime Log
My next issue then becomes the fact that my superiors also want to track/log this data for future consumption. So any time the most current maintenance call changes (from nothing to any machine, or from any one machine to another machine) I need to log that in a list, automatically, in the Downtime Log sheet.
It would also be FANTASTIC if there was some way to add time stamps to show the date/time the maintenance call occurred, which would also make sorting/filtering the list in the downtime log sheet much easier.
I know this is a LOT to be asking help for, but I am pretty much at a standstill at the moment, so I am desperate and figured I would go to the experts.
First and foremost, forgive my ignorance. The last bit of programming I did that was in the same family as VBA was just some VB programming I had to do when I was in college.
My dilemma:
I have been tasked with building a maintenance call/downtime (ANDON) tracking system. This system must track maintenance calls, downtime, maintenance response time, maintenance repair time, and the active fault when this downtime occurs. I have no problem building this system, building the PLC program, or getting the data from the PLC to EXCEL using an OPC server. That much is cake for me. My problem is what I have to do with it now.
I am planning on a workbook with a spreadsheet for each machine (just for ease of organization), and then an Active Alarms sheet, and a Downtime Log sheet.
I am hoping someone will have some experience setting up something like this using VBA with excel.
Active Alarms
On this sheet I need to have a range of columns shown from the individual sheets dedicated to each machine, depending on whether or not a maintenance call bit is true in the plc. Now, the difficulty for me is not being able to show this data in excel conditionally, as I will have a cell for each machine that will show TRUE if there is a maintenance call, and FALSE if there is not. The difficult part becomes when I have to have this list of machines show all machines that have active maintenance calls, and show them in descending order, with the most recent active maintenance call at the top.
Downtime Log
My next issue then becomes the fact that my superiors also want to track/log this data for future consumption. So any time the most current maintenance call changes (from nothing to any machine, or from any one machine to another machine) I need to log that in a list, automatically, in the Downtime Log sheet.
It would also be FANTASTIC if there was some way to add time stamps to show the date/time the maintenance call occurred, which would also make sorting/filtering the list in the downtime log sheet much easier.
I know this is a LOT to be asking help for, but I am pretty much at a standstill at the moment, so I am desperate and figured I would go to the experts.
