WOW, Thank you.
I have a problem thou... I have a workbook with a macro running that grabs the data every 10 seconds or so from the HMI's. I have this running in a loop reading several IP addresses and logging the tag values based every time I receive a different value. The problem started when i added the 12th machine IP address to the macro. Im not sure if its feasible to open and close a helping workbook every 10 seconds. Here is the code that's running. I originally posted the simple code that was only grabing data when requested.
Sub Repeat_1Min()
Call readFromBirtha
Call readFromLCB
Call readFromLHCell1
Call readFromLHCell2
Call readFromLHCell3
Call readFromRHCell1
Call readFromRHCell2
Call readFromRHCell3
Call readFromGertrude
Call readFromG05BeamAssy
Call readFromG05TowLug
'(The 12 address will stop the macro, Commented it out for now)
'Call readFromG01BeamAssy
Call Report
Call Minute
End Sub
Sub readFromBirtha()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHA IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromLCB()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LCBDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("LCBDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LowerCB").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("LCBDownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LCBDownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromLHCell1()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1DownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1DownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromLHCell2()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2DownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2DownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromLHCell3()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3DownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3DownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromRHCell1()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1DownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1DownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromRHCell2()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2DownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2DownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromRHCell3()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3DownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3DownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3DownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromGertrude()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("GertrudeDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("GertrudeDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("GertrudeDownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("GertrudeDownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromG05BeamAssy()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssyDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssyDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssyDownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssyDownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromG05TowLug()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 5).Value
lRow = nRow - 1
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLugDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLugDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLugDownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLugDownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub readFromG01BeamAssy()
'DECLARE THE VARIABLES USED IN THE LOGIC BELOW
Dim TagAdress1 As String, TagAdress2 As String, TagAdress3 As String, TagAdress4 As String, TagAdress5 As String, TagAdress6 As String, iRow As Integer, nRow As Integer, lRow As Integer
Dim ipaddr As String, plc_slot As Integer
Dim EipCommClient As New EipExcelComm
ipaddr = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(2, 9).Value
plc_slot = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 9).Value
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 5).Value
lRow = nRow - 1
On Error Resume Next
EipCommClient.setPlcIPAddress (ipaddr)
EipCommClient.setPlcSlot (plc_slot)
'LIST OF THE PLC TAGS AND WHERE TO PLACE THE DATA
TagAdress1 = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 3).Value
TagAdress2 = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(4, 3).Value
TagAdress3 = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(5, 3).Value
TagAdress4 = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(6, 3).Value
TagAdress5 = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(7, 3).Value
TagAdress6 = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(8, 3).Value
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 4).Value = EipCommClient.GetData(TagAdress1)
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(4, 4).Value = EipCommClient.GetData(TagAdress2)
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(5, 4).Value = EipCommClient.GetData(TagAdress3)
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(6, 4).Value = EipCommClient.GetData(TagAdress4)
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(7, 4).Value = EipCommClient.GetData(TagAdress5)
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(8, 4).Value = EipCommClient.GetData(TagAdress6)
'LOOKING IF THE PLC IS SIGNALING A DOWNTIME EVENT, (DOWNTIME EVENT IS AUTO MODE ALARMS PRESENT WHEN MACHINE IS PULLED OUT OF AUTO, OR CYCLE TIMER LAPSES, BERTHE IS SET TO 60 SECONDS IN THE PLC)
If Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(8, 4).Value = True Then GoTo 1 Else GoTo 2
1
'LOOK TO SEE IF DATA HAS BEEN CAPTURED YET(IF TRUE THEN CAPTURE IF FALSE THEN GOTO END
If Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(4, 6).Value = True Then GoTo 3 Else GoTo 4
3
'ADD 1 TO THE RECORD NUMBER FOR NEXT CYCLE
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 5).Value + 1
'SET THE DATA RECORDED SO NEXT SCAN DOESNT ADD REPEAT DATA TO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(4, 6).Value = False
'MOVE THE COMBINED ALARM STRINGS AND TIME STAMP INTO THE LOG
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssyDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(10, 6).Value
'GOTO THE END
GoTo 4
2 'Move True Into The Data Recorded Memory Box Cell (E,4)(TRUE = READY FOR NEXT RECORD, ALARMS CLEARED)(FALSE = DATA CAPTURED)
nRow = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 5).Value
If Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(4, 6).Value = True Then GoTo 4 Else GoTo 5
'MOVE STRING DATA RUNNING WITH NO ALARMS AT XX:XX.XX TIME STAMP
5 Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(4, 6).Value = True
'ADJUST RECORD NUMBER VARIABLE TO ADD THE NEXT STRING TO RECORD RUNNING WITH NO ALARMS TIME STAMP TO THE RECORD
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssyDownTimeLog").Cells(nRow, 3).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(9, 6).Value
'ADD 1 TO THE NEXT VARIABLE RECORD LOCATION
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 5).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy").Cells(3, 5).Value + 1
'Move Time Stamp Intolog for how long machinewas down
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssyDownTimeLog").Cells(lRow, 2).Value = Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssyDownTimeLog").Cells(5, 6).Value
4
EipCommClient.Close
End Sub
Sub Report()
If VBA.Format(VBA.Now - 1, "MMM-dd-yyyy") <> Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(19, 2).Value Then GoTo 1 Else GoTo 2
1
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(18, 3).Value = False
Call Report_Out
2
If Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(19, 5).Value <> Hour(Time) Then GoTo 3 Else GoTo 4
3 Workbooks("AlarmLogs.xlsm").Save
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(19, 4).Value = Time
4
End Sub
Sub Minute()
'THIS TIME SETS HOW OFTEN TO AUTOMATICALLY REFRESH,(PULLING DATA FROM THE PLC EVERY 10 SECONDS)
Application.OnTime Now + TimeValue("00:00:08"), "Repeat_1Min"
End Sub
Sub Report_Out()
If Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(18, 3).Value = False Then GoTo 1 Else GoTo 2
1
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("Birtha List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("LCB List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Workbooks("AlarmLogs.xlsm").Worksheets("LCB List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("LCB List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("LCB List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("LCB List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1 List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1 List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1 List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1 List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1 List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2 List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2 List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2 List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2 List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2 List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3 List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3 List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3 List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3 List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3 List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1 List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1 List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1 List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1 List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1 List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2 List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2 List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2 List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2 List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2 List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3 List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3 List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3 List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3 List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3 List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy List").Cells(1, 4).Value = Workbooks("AlarmLogs.xlsm").Worksheets("BirthaDownTimeLog").Cells(8, 6).Value
Range("A:Q").Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy List").Sort.SortFields.Clear
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy List").Sort.SortFields.Add Key:=Range( _
"D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy List").Sort
.SetRange Range("A:Q")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy List").Range("A1:Q3000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlFilterValues
Call Save_YesterDays_File
2
End Sub
Sub Save_YesterDays_File()
Dim myCSVFileName As String
Dim myWB As Workbook
Dim tempWB As Workbook
Dim rngToSave As Range
Application.DisplayAlerts = False
On Error GoTo err
Set myWB = ThisWorkbook
myCSVFileName = "W:\WGA-Engineering\DownTimeTracking" & "\" & "DownTime-" & VBA.Format(VBA.Now - 1, "MMM-dd-yyyy") & ".xlsx"
Workbooks.Open Filename:="W:\WGA-Engineering\DownTimeTracking\DownTime-.xlsx"
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha List").Range("A1:E100").Copy
Workbooks("DownTime-.xlsx").Worksheets("Bertha").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("Bertha").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("Bertha").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("LCB List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("LCB").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("LCB").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("LCB").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("LCB List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1 List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("LHCell1").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("LHCell1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("LHCell1").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell1 List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2 List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("LHCell2").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("LHCell2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("LHCell2").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell2 List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3 List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("LHCell3").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("LHCell3").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("LHCell3").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("LHCell3 List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1 List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("RHCell1").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("RHCell1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("RHCell1").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell1 List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2 List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("RHCell2").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("RHCell2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("RHCell2").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell2 List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3 List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("RHCell3").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("RHCell3").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("RHCell3").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("RHCell3 List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("Gertrude").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("Gertrude").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("Gertrude").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("Gertrude List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("G05BeamAssy").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("G05BeamAssy").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("G05BeamAssy").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("G05BeamAssy List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("G05TowLug").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("G05TowLug").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("G05TowLug").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("G05TowLug List").AutoFilterMode = False
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy List").Range("A1:E40").Copy
Workbooks("DownTime-.xlsx").Worksheets("G01BeamAssy").Range("A1").PasteSpecial xlPasteFormats
Workbooks("DownTime-.xlsx").Worksheets("G01BeamAssy").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("G01BeamAssy").Range("A:E").EntireColumn.AutoFit
Workbooks("AlarmLogs.xlsm").Worksheets("G01BeamAssy List").AutoFilterMode = False
Workbooks("DownTime-.xlsx").SaveAs Filename:=myCSVFileName, FileFormat:=51, CreateBackup:=False
'Close myCSVFileName
Set tempWB = ActiveWorkbook
With tempWB
.Close
End With
Workbooks("AlarmLogs.xlsm").SaveAs Filename:="W:\WGA-Engineering\DownTimeTracking\AlarmLogs.xlsm", FileFormat:=52, CreateBackup:=False
err:
Application.DisplayAlerts = True
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(20, 2).Value = myCSVFileName
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(18, 3).Value = True
Workbooks("AlarmLogs.xlsm").Worksheets("Birtha").Cells(19, 2).Value = VBA.Format(VBA.Now - 1, "MMM-dd-yyyy")
End Sub