zhudionis
Junior Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Код:
Dim ChanNum(), ChanName(), RtuNum(), RtuName(), Statuses(), StatName(), StatTMS(),_
StatDelta(), TU(), StatClass(), StatInv(), StatLog(), APS(), StatGrav(),TwoBits(),_
Analogs(), AnalogName(), AnalogTMS(), AnalogDelta(), Units()
fileName = Replace(Inputbox("Укажите путь к файлу Excel : "), """", "")
LineSumm = Inputbox("Укажите количество строк Excel : ")
set oExcel = CreateObject("Excel.Application")
oExcel.Visible = true
Set oBook = oExcel.Workbooks.Open(fileName)
For m = 2 to LineSumm
ReDim Preserve ChanNum(m), ChanName(m), RtuNum(m), RtuName(m), Statuses(m),_
StatName(m), StatTMS(m), StatDelta(m), TU(m), StatDelta(m), StatClass(m), StatInv(m), StatLog(m),_
APS(m), StatGrav(m),TwoBits(m), Analogs(m), AnalogName(m), AnalogTMS(m), AnalogDelta(m), Units(m)
ChanNum(m) = oBook.Worksheets(1).Cells(m,1)
ChanName(m) = oBook.Worksheets(1).Cells(m,2)
RtuNum(m) = oBook.Worksheets(1).Cells(m,3)
RtuName(m) = oBook.Worksheets(1).Cells(m,4)
Statuses(m) = oBook.Worksheets(1).Cells(m,5)
StatName(m) = oBook.Worksheets(1).Cells(m,6)
StatTMS(m) = oBook.Worksheets(1).Cells(m,7)
StatDelta(m) = oBook.Worksheets(1).Cells(m,8)
TU(m) = oBook.Worksheets(1).Cells(m,9)
StatClass(m) = oBook.Worksheets(1).Cells(m,10)
StatInv(m) = oBook.Worksheets(1).Cells(m,11)
StatLog(m) = oBook.Worksheets(1).Cells(m,12)
APS(m) = oBook.Worksheets(1).Cells(m,13)
StatGrav(m) = oBook.Worksheets(1).Cells(m,14)
TwoBits(m) = oBook.Worksheets(1).Cells(m,15)
Analogs(m) = oBook.Worksheets(1).Cells(m,16)
AnalogName(m) = oBook.Worksheets(1).Cells(m,17)
AnalogTMS(m) = oBook.Worksheets(1).Cells(m,18)
AnalogDelta(m) = oBook.Worksheets(1).Cells(m,19)
Units(m) = oBook.Worksheets(1).Cells(m,20)
Next
oBook.Close(false)
oExcel.Quit()
Chan = 1
Lampa = 0
TMS = ""
HW = ""
For m = 2 to LineSumm
If ChanNum(m) <> "" AND Chan = 1 Then
TMS = TMS&"<CHANNEL ChannelNum="&""""&ChanNum(m)&""""&" ChannelName="&""""&ChanName(m)&""""&">"
Chan = Chan + 1
Lampa = 1
Elseif ChanNum(m) <> "" Then
Select Case Lampa
Case 1
TMS = TMS&vbCrLf&"</CHANNEL>"&vbCrLf&"<CHANNEL ChannelNum="&""""&ChanNum(m)&""""&" ChannelName="&""""&ChanName(m)&""""&">"
Case 2
TMS = TMS&vbCrLf&" </RTU>"&vbCrLf&"</CHANNEL>"&vbCrLf&"<CHANNEL ChannelNum="&""""&ChanNum(m)&""""&" ChannelName="&""""&ChanName(m)&""""&">"
Case 3
TMS = TMS&vbCrLf&" </STATUSES>"&vbCrLf&" </RTU>"&vbCrLf&"</CHANNEL>"&vbCrLf&"<CHANNEL ChannelNum="&""""&ChanNum(m)&""""&" ChannelName="&""""&ChanName(m)&""""&">"
Case 4
TMS = TMS&vbCrLf&" </ANALOGS>"&vbCrLf&" </RTU>"&vbCrLf&"</CHANNEL>"&vbCrLf&"<CHANNEL ChannelNum="&""""&ChanNum(m)&""""&" ChannelName="&""""&ChanName(m)&""""&">"
End Select
Lampa = 1
End If
If RtuNum(m) <> "" Then
Select Case Lampa
Case 1
TMS = TMS&vbCrLf&" <RTU RTUName="&""""&RtuName(m)&""""&" RTUNum="&""""&RtuNum(m)&""""&">"
Case 2
TMS = TMS&vbCrLf&" </RTU>"&vbCrLf&" <RTU RTUName="&""""&RtuName(m)&""""&" RTUNum="&""""&RtuNum(m)&""""&">"
Case 3
TMS = TMS&vbCrLf&" </STATUSES>"&vbCrLf&" </RTU>"&vbCrLf&" <RTU RTUName="&""""&RtuName(m)&""""&" RTUNum="&""""&RtuNum(m)&""""&">"
Case 4
TMS = TMS&vbCrLf&" </ANALOGS>"&vbCrLf&" </RTU>"&vbCrLf&" <RTU RTUName="&""""&RtuName(m)&""""&" RTUNum="&""""&RtuNum(m)&""""&">"
End Select
Lampa = 2
End If
If Statuses(m) <> "" Then
Select Case Lampa
Case 2
TMS = TMS&vbCrLf&" <STATUSES StaDesc="&""""&Statuses(m)&""""&">"
Case 3
TMS = TMS&vbCrLf&" </STATUSES>"&vbCrLf&" <STATUSES StaDesc="&""""&Statuses(m)&""""&">"
Case 4
TMS = TMS&vbCrLf&" </ANALOGS>"&vbCrLf&" <STATUSES StaDesc="&""""&Statuses(m)&""""&">"
End Select
Lampa = 3
End If
If Analogs(m) <> "" Then
Select Case Lampa
Case 2
TMS = TMS&vbCrLf&" <ANALOGS AnaDesc="&""""&Analogs(m)&""""&">"
Case 3
TMS = TMS&vbCrLf&" </STATUSES>"&vbCrLf&" <ANALOGS AnaDesc="&""""&Analogs(m)&""""&">"
Case 4
TMS = TMS&vbCrLf&" </ANALOGS>"&vbCrLf&" <ANALOGS AnaDesc="&""""&Analogs(m)&""""&">"
End Select
Lampa = 4
End If
If StatTMS(m) <> "" Then
TMS = TMS&vbCrLf&" <STATUS StatusPoint="&""""&StatTMS(m)&""""&" StatusName="&""""&StatName(m)&""""
If StatClass(m) <> "" Then
TMS = TMS&" StatusClass="&""""&StatClass(m)&""""
End If
If StatInv(m) <> "" Then
TMS = TMS&" StatusInvert="&""""&"+ (да)"&""""
End If
If StatLog(m) <> "" Then
TMS = TMS&" StatusRetro="&""""&"- (нет)"&""""
End If
If APS(m) = "" Then
TMS = TMS&" StatusSignal="&""""&"+ (аварийно-предупредительный)"&""""
End If
Select Case StatGrav(m)
Case 1
TMS = TMS
Case 2
TMS = TMS&" StatusImp="&""""&"2 (сигнал)"&""""
Case 3
TMS = TMS&" StatusImp="&""""&"3 (сирена)"&""""
Case ""
TMS = TMS&" StatusImp="&""""&"0 (не записывать)"&""""
End Select
TMS = TMS&"/>"
End If
If AnalogTMS(m) <> "" Then
TMS = TMS&vbCrLf&" <ANALOG AnalogPoint="&""""&AnalogTMS(m)&""""&" AnalogName="&""""&AnalogName(m)&""""&" AnalogUnits="&""""&Units(m)&""""&"/>"
End If
Next
Select Case Lampa
Case 3
TMS = TMS&vbCrLf&" </STATUSES>"&vbCrLf&" </RTU>"&vbCrLf&"</CHANNEL>"
Case 4
TMS = TMS&vbCrLf&" </ANALOGS>"&vbCrLf&" </RTU>"&vbCrLf&"</CHANNEL>"
End Select
Chan = 1
Lampa = 0
For m = 2 to LineSumm
If ChanNum(m) <> "" Then Channel = ChanNum(m)
If RtuNum(m) <> "" Then Rtu = RtuNum(m)
If Statuses(m) <> "" AND Chan = 1 Then
HW = HW&" <iec101PrimStg iec101PStgName="&""""&Statuses(m)&""""&">"
Lampa = 1
Chan = Chan + 1
Elseif Statuses(m) <> "" Then
Select Case Lampa
Case 1
HW = HW&vbCrLf&" </iec101PrimStg>"&vbCrLf&" <iec101PrimStg iec101PStgName="&""""&Statuses(m)&""""&">"
Case 2
HW = HW&vbCrLf&" </iec101PrimAng>"&vbCrLf&" <iec101PrimStg iec101PStgName="&""""&Statuses(m)&""""&">"
End Select
Lampa = 1
End If
If Analogs(m) <> "" AND Chan = 1 Then
HW = HW&" <iec101PrimAng iec101PAngName="&""""&Analogs(m)&""""&">"
Lampa = 2
Chan = Chan + 1
Elseif Analogs(m) <> "" Then
Select Case Lampa
Case 1
HW = HW&vbCrLf&" </iec101PrimStg>"&vbCrLf&" <iec101PrimAng iec101PAngName="&""""&Analogs(m)&""""&">"
Case 2
HW = HW&vbCrLf&" </iec101PrimAng>"&vbCrLf&" <iec101PrimAng iec101PAngName="&""""&Analogs(m)&""""&">"
End Select
Lampa = 2
End If
If StatDelta(m) <> "" AND TwoBits(m) <> "" Then
HW = HW&vbCrLf&" <iec101PrimStb iec101PVStbAddr="&""""&StatDelta(m)&""""&" iec101PVStbTmsChn="&""""&Channel&""""&" iec101PVStbTmsRtu="&""""&Rtu&""""&" iec101PVStbTmsPt="&""""&StatTMS(m)&""""&"/>"
Elseif StatDelta(m) <> "" Then
HW = HW&vbCrLf&" <iec101PrimSt iec101PVStAddr="&""""&StatDelta(m)&""""&" iec101PVStTmsChn="&""""&Channel&""""&" iec101PVStTmsRtu="&""""&Rtu&""""&" iec101PVStTmsPt="&""""&StatTMS(m)&""""&"/>"
End if
If AnalogDelta(m) <> "" Then
HW = HW&vbCrLf&" <iec101PrimAnf iec101PVAnfAddr="&""""&AnalogDelta(m)&""""&" iec101PVAnfTmsChn="&""""&Channel&""""&" iec101PVAnfTmsRtu="&""""&Rtu&""""&" iec101PVAnfTmsPt="&""""&AnalogTMS(m)&""""&"/>"
End if
Next
Select case Lampa
Case 1
HW = HW&vbCrLf&" </iec101PrimStg>"
Case 2
HW = HW&vbCrLf&" </iec101PrimAng>"
End Select
Stat1 = "zzzzzzzzzzzzz"
Chan = 1
For m =2 to LineSumm
If ChanNum(m) <> "" Then Channel = ChanNum(m)
If RtuNum(m) <> "" Then Rtu = RtuNum(m)
If Statuses(m) <> "" Then Stat = Statuses(m)
If TU(m) <> "" AND Stat <> Stat1 AND Chan = 1 Then
HW = HW&vbCrLf&" <iec101PrimTcg iec101PTcgName="&""""&Stat&""""&">"
Chan = Chan + 1
Stat1 = Stat
End If
If TU(m) <> "" AND Stat <> Stat1 AND Chan <> 1 Then
HW = HW&vbCrLf&" </iec101PrimTcg>"&vbCrLf&_
" <iec101PrimTcg iec101PTcgName="&""""&Stat&""""&">"
Stat1 = Stat
End If
If TU(m) <> "" AND Stat = Stat1 Then
HW = HW&vbCrLf&" <iec101PrimTc iec101PVTcAddr="&""""&TU(m)&""""&" iec101PVTcTmsChn="&""""&Channel&""""&" iec101PVTcTmsRtu="&""""&Rtu&""""&" iec101PVTcTmsPt="&""""&StatTMS(m)&""""&"/>"
End If
Next
If Chan <> 1 Then
HW = HW&vbCrLf&" </iec101PrimTcg>"
End If
set WshShell = WScript.CreateObject("WScript.Shell")
MifFilePath = WshShell.ExpandEnvironmentStrings("%USERPROFILE%")
Set str= CreateObject("ADODB.Stream")
str.Type = 2
str.Charset = "UTF-8"
str.Open()
str.WriteText(TMS)
str.SaveToFile MifFilePath&"\Desktop\мой_TMS.cfg"
str.Close()
str.Open()
str.WriteText(HW)
str.SaveToFile MifFilePath&"\Desktop\мой_HW.cfg"
str.Close()
|
|
Всего записей: 116 | Зарегистр. 25-05-2009 | Отправлено: 07:14 23-11-2018 | Исправлено: zhudionis, 07:25 23-11-2018 |
|
