Scribd
Upload
22 views15 pages

Excel VBA Daily Energy Report

Uploaded by

Banu Chand
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
22 views15 pages

Excel VBA Daily Energy Report

Uploaded by

Banu Chand
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const pi As Double = 4 * Atn(1) Dim

rs As New [Link] Dim rs1 As New [Link] Dim cn As [Link] Dim Strsql As String Dim AppExcel As [Link] Dim WBook As [Link] Dim WSheet As [Link] Dim DSN,DRange,RowIndex,ColIndex,sProcess,datestring,ErrNo,Value,syn,i Dim lstbox1 As ListBoxCmdTarget Dim count As Integer Dim listitem() As String Dim items() As String Dim XlsDestFileName,XlsFileName,squery,Feeder_Name As String Dim days,Feeder_ID,k,j,Valw1,ReportTime1,ReportTime2 Dim ReportDate1,ReportDate2,FromDate,ToDate,Count_retr,ToTime,FromDate1,Freq,DT_ Name,DT_ID,MeterDate Dim recordcount As Long Dim mn As Long Dim METER_SNO,CONSUMER_NAME,METER_TYPE,q1,q2,q3,q4,h,arr,MONTH1,YEAR1 Dim UIDX,PCOUNT,TODPCOUNT,TODMDPCOUNT,PCOUNTX,HCOUNT,MIDPCOUNT,MIDPCOUNTX,MIDNCO UNT,RPTEXCELSAVEPATH,MTYPE,MINX,EVCOUNT,TRCOUNT,METER_FW, MKWHE_INDEX Dim MKWHI_INDEX,MKVAHE_INDEX,MKVAHI_INDEX,TODKWHE_INDEX,TODKWHI_INDEX,TODKVAHE_I NDEX,TODKVAHI_INDEX,LOCATEDAT,DIMD,TCOUNT,FCOUNT,ICOUNT Dim TODP(30),TODMDP(30),MAINP(30),MAINE(30,20),TODCON(30,15,20),MAINEC(30,20),TO DC(30,15,20),MD(30,20),BILLD(20),POWERON(20),POWERONCUM(20) Dim ROW,COL,CONT,IA1,IA2,IA3,VA1,VA2,RPTEXCELSAVEPATH1,METER_MAKE,METER_PT_RATIO ,METER_CT_RATIO,METER_FACTOR,MODEM_TIMESTAMP,TARIFFN Dim HMODEM_TIMESTAMP,MDRESET_COUNT,TAMPERCOUNT_N,P_TRCOUNT,H_TRCOUNT,MUSER,MPWD, RCOUNT Public Sub main() Dim vResult As Long On Error GoTo ErrLabel count = 1 Sleep(1000) Set cn = New [Link] Set AppExcel = GetObject(,"[Link]") If ErrNo <> 0 Then Set AppExcel = CreateObject("[Link]") End If Set WBook = [Link] Set WSheet = [Link] [Link] ("0802244587") [Link] = True [Link] = False CONT = 1 With WSheet ' UIDX = LOCAL_UID '[Link](0) ' MTYPE = LOCAL_TYPE '[Link](1) ' MONTH1 = LOCAL_FROMMM '[Link](2)

' YEAR1 = LOCAL_FROMYY '[Link](3) ' DSN = GetVariableValue("Miscellaneous:MDASDSN") MUSER = GetVariableValue("Miscellaneous:MDASUSER") MPWD = GetVariableValue("Miscellaneous:MDASPWD") UIDX = 1 MTYPE= "DT" MONTH1= "05" YEAR1="2011" DSN = "MDASDB" [Link] DSN,MUSER,MPWD .Range(Chr(65) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(66) + Trim(Str(1))).ColumnWidth = 35 .Range(Chr(67) + Trim(Str(1))).ColumnWidth = 35 .Range(Chr(68) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(69) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(70) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(71) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(72) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(73) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(74) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(75) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(76) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(77) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(78) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(79) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(80) + Trim(Str(1))).ColumnWidth = 5 .Range(Chr(81) + Trim(Str(1))).ColumnWidth = 16 .Range(Chr(82) + Trim(Str(1))).ColumnWidth = 16 'squery = "SELECT COUNT(*) FROM FEEDER WHERE SUBSTATIONLOCATIONID = '" & GetVariableValue("Miscellaneous:SS_ID") & "'" ' [Link] squery,cn 'RFEEDER = CInt([Link](0)) '[Link] 'NOCOL = (RFEEDER * 8) + 2

ColIndex = 65 RowIndex = 1 Value = "" DRange = Chr(ColIndex) + Trim(Str(RowIndex)) + ":" & Chr(ColIndex+16) + Trim(Str(RowIndex+7)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,5) Value = "DATE :" DRange = Chr(ColIndex) + Trim(Str(RowIndex+1)) + ":" & Chr(ColIndex) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,3) Value = "DAY" DRange = Chr(ColIndex) + Trim(Str(RowIndex+2)) + ":" & Chr(ColIndex) +

Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,3) Value = "DAILY ENERGY REPORT" DRange = Chr(ColIndex) + Trim(Str(RowIndex)) + ":" & Chr(ColIndex+16) + Trim(Str(RowIndex+3)) Call SetTextFormat(DRange,16,0,True,"",3,2,True,True,Value,2,6) ColIndex = 65 RowIndex = 5 Value = "NAME OT THE S/S (URBAN)" DRange = Chr(ColIndex) + Trim(Str(RowIndex)) + ":" & Chr(ColIndex+1) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0) Value = "DIVISION" DRange = Chr(ColIndex) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0) Value = "WEATHER CONDITION" DRange = Chr(ColIndex) + Trim(Str(RowIndex+2)) + ":" & Chr(ColIndex+1) + Trim(Str(RowIndex+2)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0) Value = "REMART" DRange = Chr(ColIndex) + Trim(Str(RowIndex+3)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0)

ColIndex = 65 RowIndex = 9 Value = "Time in Hrs." DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex) + Tri m(Str(RowIndex+2)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "33/11 KV Power Transformer No. 1 Capacity ( MVA) Oil/[Link].)" DRange = Chr(ColIndex+1) + Trim(Str(RowIndex))+":" & Chr(ColIndex+1) + Trim(Str(RowIndex+2)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "33/11 KV Power Transformer No. 2 Capacity ( MVA) Oil/[Link]." DRange = Chr(ColIndex+2) + Trim(Str(RowIndex))+":" & Chr(ColIndex+2) + Trim(Str(RowIndex+2)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "11 KV Incoming (Main) CB No.1" DRange = Chr(ColIndex+3) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+10) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) ' Value = "11 KV Incoming OEC No.2" 'DRange = Chr(ColIndex+7) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+10) + Trim(Str(RowIndex))

'Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "11 KV Outgoing No.1" DRange = Chr(ColIndex+11) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+15) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) '' Value = "11 KV Outgoing No.2" '' DRange = Chr(ColIndex+14) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+1 6) + Trim(Str(RowIndex)) '' Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) ColIndex = 68 RowIndex = 10 Value = "Load in Amps RYB" DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+2) + T rim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "Load in Amps R" DRange = Chr(ColIndex) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "Load in Amps Y" DRange = Chr(ColIndex+1) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "Load in Amps B" DRange = Chr(ColIndex+2) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) ColIndex = 71 RowIndex = 10 Value = "Bus Volt" DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+2) + Tr im(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "Load in Amps R" DRange = Chr(ColIndex) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "Load in Amps Y" DRange = Chr(ColIndex+1) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "Load in Amps B" DRange = Chr(ColIndex+2) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) ''Value = "KWH Meter" '' DRange = Chr(ColIndex+2) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+3) + Trim(Str(RowIndex)) '' Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) ' ' Value = "Unit" '' DRange = Chr(ColIndex+2) + Trim(Str(RowIndex+1))

''Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) ' 'Value = "M.D." '' DRange = Chr(ColIndex+3) + Trim(Str(RowIndex+1)) ''Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) ColIndex = 72 RowIndex = 10 ''Value = "Load in Amps RYB" ''DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex) + T rim(Str(RowIndex+1)) ''Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) ''Value = "Bus Volt" ''DRange = Chr(ColIndex+1) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+1) + Trim(Str(RowIndex+1)) ''Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "KWH Meter" DRange = Chr(ColIndex+2) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+3) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "Unit" DRange = Chr(ColIndex+2) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "M.D." DRange = Chr(ColIndex+3) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) ColIndex = 76 RowIndex = 10 Value = "Load in Amps RYB" DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+2) + T rim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) Value = "Load in Amps R" DRange = Chr(ColIndex) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "Load in Amps Y" DRange = Chr(ColIndex+1) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "Load in Amps B" DRange = Chr(ColIndex+2) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) ColIndex = 79 RowIndex = 10 Value = "KWH Meter" DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+1) + Trim( Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5)

Value = "Unit" DRange = Chr(ColIndex) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) Value = "M.D." DRange = Chr(ColIndex+1) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) ' squery = "SELECT FEEDER_LOCATIONNAME FROM FEEDER WHERE SUBSTATION_LOCATIONID = '" & GetVariableValue("Miscellaneous:SS_ID") & "'" '[Link] squery,cn '[Link] 'ColIndex = 68 ' For i=1 To RFEEDER 'RowIndex =17 'Value = CStr([Link]("FEEDER_LOCATIONNAME")) ' DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+7) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) 'RowIndex= RowIndex+1 'Value = "Load in Amps RYB" 'DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+2) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) ' Value = "Bus Volt" ' DRange = Chr(ColIndex+3) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+5) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) 'Value = "KWH Meter" 'DRange = Chr(ColIndex+6) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+7) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) 'RowIndex= RowIndex + 1 ' For j=1 To 2 'Value = " R" 'DRange = Chr(ColIndex) + Trim(Str(RowIndex)) ' Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) 'Value = " Y" 'DRange = Chr(ColIndex+1) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) 'Value = " B" 'DRange = Chr(ColIndex+2) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) 'ColIndex = ColIndex +3 'Next j ' Value = "Unit" 'DRange = Chr(ColIndex) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5)

' Value = "M.D." 'DRange = Chr(ColIndex+1) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,True,"",3,2,True,False,Value,24,5) 'ColIndex = ColIndex + 2 '[Link] ' Next i ColIndex = 72 RowIndex = 5 Value = " NAME OF OPERATOR: " DRange = Chr(ColIndex-1) + Trim(Str(RowIndex)) + ":" & Chr(ColIndex) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0) Value = "A SHIFT " DRange = Chr(ColIndex+1) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,0) Value = "B SHIFT " DRange = Chr(ColIndex+1) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,0) Value = "C SHIFT" DRange = Chr(ColIndex+1) + Trim(Str(RowIndex+2)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,0) Value = "DC VOLTS " DRange = Chr(ColIndex+6) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,0) Value = "MRG TO " DRange = Chr(ColIndex+6) + Trim(Str(RowIndex+2)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,0) Value = "FROM " DRange = Chr(ColIndex+6) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,0) Value = "DATE " DRange = Chr(ColIndex+8) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,0) ColIndex = 65 RowIndex = 12 Value = "1AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "2AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+1)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "3AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+2)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "4AM "

DRange = Chr(ColIndex) + Trim(Str(RowIndex+3)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "5AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+4)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "6AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+5)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "7AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+6)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "8AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+7)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "9AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+8)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "10AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+9)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "11AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+10)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "12AM " DRange = Chr(ColIndex) + Trim(Str(RowIndex+11)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "1PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+12)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "2PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+13)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "3PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+14)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "4PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+15)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "5PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+16)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "6PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+17)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "7PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+18)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "8PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+19)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "9PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+20)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "10PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+21)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "11PM" DRange = Chr(ColIndex) + Trim(Str(RowIndex+22)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) Value = "12PM"

DRange = Chr(ColIndex) + Trim(Str(RowIndex+23)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,False,Value,2,5) squery = "SELECT METER_LOC_NAME,FEEDER_NAME,SUBSTATION_NAME,SUBDIVISION _NAME,DIVISION_NAME,CIRCLE_NAME,REGION_NAME,DISCOM_NAME FROM METER_DETAILS WHERE UIDX = '" & UIDX & "'" [Link] squery,cn 'LOCATEDAT = CStr([Link]("METER_LOC_NAME")) ColIndex=66 RowIndex=5 Value = " :" &CStr([Link](2)) 'Value = ": " & CStr([Link]("SUBSTATION_NAME")) DRange = Chr(ColIndex) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + Tri m(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,0) Value = " :" &CStr([Link](4)) ' Value = ": " & CStr([Link]("DIVISION_NAME")) DRange = Chr(ColIndex) + Trim(Str(RowIndex+1))'+ ":" & Chr(ColIndex) + Tr im(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,0) Value = " " &CStr([Link](1)) ' Value = ": " & CStr([Link]("DIVISION_NAME")) DRange = Chr(ColIndex+2) + Trim(Str(RowIndex+4)) '+ ":" & Chr(ColIndex+9) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,0) Call SetTextFormat(DRange,10,0,True,"",3,2,True,True,Value,24,5) [Link] squery = "SELECT MODEM_TIMESTAMP FROM DT_INSTANT WHERE UIDX = '" & UIDX & "' AND POLLSTATUS = 'OK' AND MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & "-" & MONTH1,"YYYY-MM") & "%' order by MODEM_TIMESTAMP DESC" [Link] squery,cn [Link] ' MODEM_TIMESTAMP = CStr([Link]("MODEM_TIMESTAMP")) ' Value = ": " & Format(MODEM_TIMESTAMP,"MMM-YYYY") 'DRange = Chr(0) + Trim(Str(0)) + ":" & Chr(0) + Trim(Str(0)) ' Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0) [Link] days = Format(MODEM_TIMESTAMP,"DD") ColIndex=68 RowIndex=12 ColIndex = 68 RCOUNT = 0 squery = "SELECT COUNT(*) FROM DT_INSTANT WHERE UIDX = '" & UIDX & "' AND POLLSTATUS = 'OK' AND MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & "-" & MON TH1 & "-" & i,"YYYY-MM-DD") & "%'" [Link] squery,cn RCOUNT = CInt([Link](0)) [Link] If RCOUNT = 0 Then GoTo SKIPDAY

End If squery = "SELECT LINE_CURRENT_R,LINE_CURRENT_Y,LINE_CURRENT_B,VOLTAGE_R ,VOLTAGE_Y,VOLTAGE_B FROM DT_INSTANT WHERE UIDX = '" & UIDX & "' AND POLLSTATUS = 'OK' AND MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & "-" & MONTH1,"YYYY-MM") & " %' order by MODEM_TIMESTAMP ASC" [Link] squery,cn For i = 1 To RCOUNT Value = " " & CStr([Link]("LINE_CURRENT_R")) DRange = Chr(ColIndex) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + Tri m(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,5) 'ColIndex = ColIndex + 1 Value = " " &CStr([Link](0)) DRange = Chr(ColIndex) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + Tri m(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,5) 'ColIndex = ColIndex + 1 'Value = CStr([Link](1)) 'DRange = Chr(ColIndex) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,F alse,Value,2,5) ' ColIndex = ColIndex + 1 Value = " " &CStr([Link](1)) DRange = Chr(ColIndex+1) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + T rim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,5) 'ColIndex = ColIndex + 1 Value = " " &CStr([Link](2)) DRange = Chr(ColIndex+2) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + T rim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,5) 'ColIndex = ColIndex + 1 Value = " " &CStr([Link](3)) DRange = Chr(ColIndex+3) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + T rim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,5) 'ColIndex = ColIndex + 1 Value = " " &CStr([Link](4)) DRange = Chr(ColIndex+4) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + T rim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,5) 'ColIndex = ColIndex + 1 Value = " " &CStr([Link](5)) DRange = Chr(ColIndex+5) + Trim(Str(RowIndex)) '+ ":" & Chr(ColIndex) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"",2,2,True,False,Value,2,5) RowIndex = RowIndex + 1 ColIndex = 68 SKIPDAY:

[Link] Next i '[Link] 'squery = "SELECT MODEM_TIMESTAMP,METER_SNO,METER_PT_RATIO,METER_CT_RATIO FROM " & MTYPE & "_INSTANT WHERE UIDX = '" & UIDX & "' AND POLLSTATUS = 'OK' AN D MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & "-" & MONTH1,"YYYY-MM") & "%' order by MODEM_TIMESTAMP ASC" ' [Link] squery,cn '[Link] 'ColIndex=71 'RowIndex=9 '' MODEM_TIMESTAMP = CStr([Link]("MODEM_TIMESTAMP")) 'Value = ": " & MODEM_TIMESTAMP 'DRange = Chr(66) + Trim(Str(6)) + ":" & Chr(68) + Trim(Str(6)) 'Call SetTextFormat(DRange,10,0,False,"",2,2,True,True,Value,2,0) ' METER_SNO = CStr([Link]("METER_SNO")) ' Value = ": " & METER_SNO ' DRange = Chr(ColIndex) + Trim(Str(RowIndex)) + ":" & Chr(ColIndex+1) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"",2,2,True,True,Value,2,0) 'METER_PT_RATIO = CStr([Link]("METER_PT_RATIO")) 'Value = ": " & METER_PT_RATIO 'DRange = Chr(ColIndex) + Trim(Str(RowIndex+3)) + ":" & Chr(ColIndex+1) + Trim(Str(RowIndex+3)) 'Call SetTextFormat(DRange,10,0,False,"",2,2,True,True,Value,2,0) 'METER_CT_RATIO = CStr([Link]("METER_CT_RATIO")) 'Value = ": " & METER_CT_RATIO 'DRange = Chr(ColIndex) + Trim(Str(RowIndex+4)) + ":" & Chr(ColIndex+1) + Trim(Str(RowIndex+4)) 'Call SetTextFormat(DRange,10,0,False,"",2,2,True,True,Value,2,0) 'Value = ": " & LOCATEDAT 'DRange = Chr(ColIndex) + Trim(Str(RowIndex+6)) + ":" & Chr(ColIndex+1) + Trim(Str(RowIndex+6)) 'Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0) '[Link] squery = "SELECT MODEM_TIMESTAMP FROM DT_INSTANT WHERE UIDX = '" & UID X & "' AND POLLSTATUS = 'OK' AND MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & "-" & MONTH1,"YYYY-MM") & "%' order by MODEM_TIMESTAMP DESC" [Link] squery,cn [Link] MODEM_TIMESTAMP = CStr([Link]("MODEM_TIMESTAMP")) Value = ": " & Format(MODEM_TIMESTAMP,"MMM-YYYY-DD") DRange = Chr(81) + Trim(Str(5)) '+ ":" & Chr(68) + Trim(Str(6)) Call SetTextFormat(DRange,10,0,True,"",2,2,True,True,Value,2,0) [Link] days = Format(MODEM_TIMESTAMP,"DD")

' ColIndex = 80 ' RowIndex = 5

' squery = "SELECT AVG(POWER_KW),AVG(POWER_KVA),AVG(POWER_KVR),AVG(PF _R),AVG(PF_Y),AVG(PF_B),AVG(PF_AVG) FROM " & MTYPE & "_INSTANT WHERE UIDX = '" & UIDX & "' AND POLLSTATUS = 'OK' AND MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & " -" & MONTH1 & "-" & i,"YYYY-MM-DD") & "%'" '[Link] squery,cn 'Value= Format(YEAR1 & "-" & MONTH1 & "-" & i,"YYYY-MM-DD") ' DRange = Chr(ColIndex) + Trim(Str(RowIndex))+ ":" & Chr(ColIndex+1) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"YYYY-MM-DD",3,2,True,True,Val ue,2,5) '[Link] 'ColIndex =80 ' RowIndex = 6 'For i = 1 To days ' ColIndex = 80 ' RCOUNT = 0 'squery = "SELECT COUNT(*) FROM " & MTYPE & "_INSTANT WHERE UIDX = ' " & UIDX & "' AND POLLSTATUS = 'OK' AND MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & "-" & MONTH1 & "-" & i,"YYYY-MM-DD") & "%'" ' [Link] squery,cn ' RCOUNT = CInt([Link](0)) '[Link] 'If RCOUNT = 0 Then ' GoTo SKIPDAY 'End If 'squery = "SELECT AVG(POWER_KW),AVG(POWER_KVA),AVG(POWER_KVR),AVG(PF _R),AVG(PF_Y),AVG(PF_B),AVG(PF_AVG) FROM " & MTYPE & "_INSTANT WHERE UIDX = '" & UIDX & "' AND POLLSTATUS = 'OK' AND MODEM_TIMESTAMP LIKE '" & Format(YEAR1 & " -" & MONTH1 & "-" & i,"YYYY-MM-DD") & "%'" '[Link] squery,cn ' Value= Format(YEAR1 & "-" & MONTH1 & "-" & i,"YYYY-MM-DD") ' DRange = Chr(ColIndex) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"YYYY-MM-DD",3,2,True,False,Va lue,2,5) 'ColIndex = ColIndex + 1 'Value = CStr([Link](0)) ' DRange = Chr(ColIndex) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,False,Value,2 ,5) 'ColIndex = ColIndex + 1 'Value = CStr([Link](1)) 'DRange = Chr(ColIndex) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,F alse,Value,2,5) 'ColIndex = ColIndex + 1 ' Value = CStr([Link](2)) 'DRange = Chr(ColIndex) + Trim(Str(RowIndex)) ' Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,Fa lse,Value,2,5)

' ' alse,Value,2,5)

ColIndex = ColIndex + 1 Value = CStr([Link](3)) 'DRange = Chr(ColIndex) + Trim(Str(RowIndex)) 'Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,F 'ColIndex = ColIndex + 1

' ' ' lse,Value,2,5) ' ' ' '' lse,Value,2,5) ' ' ' ' lse,Value,2,5) ' ' 'SKIPDAY: ' Next i End With

Value = CStr([Link](4)) DRange = Chr(ColIndex) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,Fa ColIndex = ColIndex + 1 Value = CStr([Link](5)) DRange = Chr(ColIndex) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,Fa ColIndex = ColIndex + 1 Value = CStr([Link](6)) DRange = Chr(ColIndex) + Trim(Str(RowIndex)) Call SetTextFormat(DRange,10,0,False,"0.000",3,2,True,Fa ColIndex = ColIndex + 1 RowIndex = RowIndex + 1

[Link] = xlLandscape '[Link] = "A1:H3" [Link] = 0.5 [Link] = 0.1 [Link] = 0.5 [Link] = 0 [Link] = True [Link] = 75 [Link] = "Meter SNO.:" & METER_SNO [Link] = "Page &P Of &N" RPTEXCELSAVEPATH1 = "proDAS_" & METER_SNO & "_" & Format(Now,"YYYYMMDD_HHMMS S") & "_" & vResult & ".pdf" RPTEXCELSAVEPATH = GetProjPath & "REPORTS\" & RPTEXCELSAVEPATH1 [Link]([Link],RPTEXCELSAVEPATH,XlFi [Link],,) '[Link](RPTEXCELSAVEPATH,xlHtml,,,True,,,,) '[Link]("0802244587") RPTEXCELSAVEPATH = GetProjPath & "REPORTS\proDAS_" & METER_SNO & "_" & Form at(Now,"YYYYMMDD_HHMMSS") & "_" & vResult & ".xls" '[Link](RPTEXCELSAVEPATH) AppExcel = Nothing

ErrLabel: ErrNo=[Link] If Err Then [Link] Resume Next End If [Link] Sleep(1000) 'HideLongOperationDialog 'LOCAL_PROCESS = 0 Dim sFileName As String sFileName = "C:\Program Files\Internet Explorer\[Link]" ' LOCAL_LINK = "[Link] & RPTEXCELSAVEPATH1 ' ExecuteCommand("&lt;CommandType action='2' parameters='" sFileName " ht tp://localhost/REPORTS/" RPTEXCELSAVEPATH1 "' timeout='0'&gt;System&lt;/Comm andType&gt;") End Sub Public Function SetTextFormat(DataRange As String, FontSize As Integer, FontColo r As Integer, FontBold As Boolean, NumFormat As String, HorzAlign As Integer, Ve rtAlign As Integer, WrapTxt As Boolean, CellMerge As Boolean, CellValue As Strin g, CellColor As Integer, Borderx As Integer) With [Link](DataRange) .[Link] = "Tahoma" .[Link] = FontSize '.[Link] = xlUnderlineStyleNone .[Link] = FontColor .[Link] = FontBold If Borderx = 0 Then ElseIf Borderx = 1 Then .Borders(xlEdgeTop).LineStyle = xlContinuous ElseIf Borderx = 2 Then .Borders(xlEdgeBottom).LineStyle = xlContinuous ElseIf Borderx = 3 Then .Borders(xlEdgeLeft).LineStyle = xlContinuous ElseIf Borderx = 4 Then .Borders(xlEdgeRight).LineStyle = xlContinuous ElseIf Borderx = 5 Then .[Link] = xlContinuous ElseIf Borderx = 6 Then .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous End If .NumberFormat = NumFormat .HorizontalAlignment = HorzAlign .VerticalAlignment = VertAlign .WrapText = WrapTxt .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = CellMerge .Value = CellValue .[Link] = CellColor Value = ""

End With End Function Public Function GetProjPath() GetProjPath = Mid(GetProjectFileName , 1 , Len(GetProjectFileNam e)- Len(GetProjectTitle)) End Function

You might also like