Sub pData()
Dim aData As ADODB.Recordset
Dim aConn As ADODB.Connection
Dim xSQL As String
Dim xA As String, xL As String

Dim aV(27)
aV(1) = "LEAVDATE"
aV(2) = "LEAVTIME"
aV(3) = "TRUFWGHT"
aV(4) = "TRAFWGHT"
aV(5) = "TRUSWGHT"
aV(6) = "TRASWGHT"
aV(7) = "NETWGHT"
aV(8) = "LOADNUMB"
aV(9) = "REGNUMBR"
aV(10) = "FIRMA"
aV(11) = "FIRMANAM"
aV(12) = "CUSTOMCO"
aV(13) = "CUSTCONA"
aV(14) = "PRODCODE"
aV(15) = "PRODCONA"
aV(16) = "CARRCODE"
aV(17) = "CARRCONA"
aV(18) = "JOBSITE"
aV(19) = "JOBSITNA"
aV(20) = "ADDINFO"
aV(21) = "EXTRA1"
aV(22) = "CUBICM"
aV(23) = "HUMIDITY"
aV(24) = "DRYCALOR"
aV(25) = "ASH"
aV(26) = "ISWBYHND"
aV(27) = "TEXTTAG"

xA = Format(vAA.StartDate, "yyyy-mm-dd 00:00:00.0")
xL = Format(vAA.EndDate + 1, "yyyy-mm-dd 00:00:00.0")

Set aConn = New ADODB.Connection
Set aData = New ADODB.Recordset

Set oL = Sheets("AA")
Rem *** Päis ***
With oL.Range(oL.Cells(1, 1), oL.Cells(1, 30)).Interior
  .ColorIndex = 6
  .Pattern = xlSolid
End With
oL.Cells(1, 1).FormulaR1C1 = "KAAL"
oL.Cells(1, 2).FormulaR1C1 = "KELL"
For xV = 1 To 27
  oL.Cells(1, 2 + xV).FormulaR1C1 = aV(xV)
Next

aConn.Open "DRIVER={AspenTech SQLplus};HOST=topidb;PORT=10014;"

Rem *** Vanade andmete kirjutamine kahte esimesse ritta ***
For xR = 1 To 2
For xV = 3 To 27
  xSQL = "select name, cast(ip_trend_time as char FORMAT 'YYYY-MM-DD HH:MI:SS') as aeg, ip_trend_value from ip_" + fT(xV) + "def " + _
         "where name like '10SCALES0" + IIf(xR = 1, "02", "52") + aV(xV) + "' and ip_trend_time<=cast(timestamp'" + xA + "' as timestamp) order by aeg desc"
  DoEvents
  aData.Open xSQL, aConn, , , -1
  DoEvents

  If Not aData.EOF Then
    oL.Cells(xR + 1, 1).Value = Left(aData.fields(0).Value, 11)
    oL.Cells(xR + 1, 2).Value = aData.fields(1).Value
    oL.Cells(xR + 1, xV + 2).Value = aData.fields(2).Value
    oL.Cells(xR + 1, xV + 2).Interior.ColorIndex = 7
  End If

  DoEvents
  aData.Close
  DoEvents
Next
Next

xSQL = "select name, cast(ip_trend_time as char FORMAT 'YYYY-MM-DD HH:MI:SS') as aeg, ip_trend_value, ip_trend_qstatus from ip_analogdef " & _
       "where name like '10SCALES%' and ip_trend_time>cast(timestamp'" + xA + "' as timestamp) and ip_trend_time<cast(timestamp'" + xL + "' as timestamp) " & _
       "union all " & _
       "select name, cast(ip_trend_time as char FORMAT 'YYYY-MM-DD HH:MI:SS') as aeg, ip_trend_value, ip_trend_qstatus from ip_textdef " & _
       "where name like '10SCALES%' and ip_trend_time>cast(timestamp'" + xA + "' as timestamp) and ip_trend_time<cast(timestamp'" + xL + "' as timestamp) order by aeg, name"
DoEvents
aData.Open xSQL, aConn, , , -1
DoEvents

xAeg = "": xK = ""
Do While (Not aData.EOF)

Rem Uus rida
If xAeg <> aData.fields(1).Value Or xK <> Left(aData.fields(0).Value, 11) Then
  xAeg = aData.fields(1).Value
  xK = Left(aData.fields(0).Value, 11)
  xR = xR + 1
  oL.Cells(xR, 1).Value = Left(aData.fields(0).Value, 11)
  oL.Cells(xR, 2).Value = aData.fields(1).Value
End If

For xV = 1 To 26
  If Mid(aData.fields(0).Value, 12, 20) = aV(xV) Then
    If Not IsNull(aData.fields(2).Value) Then oL.Cells(xR, xV + 2).Value = aData.fields(2).Value
    Exit For
  End If
Next
oL.Cells(xR, xV + 2).Interior.ColorIndex = 7

aData.MoveNext
Loop
DoEvents
aData.Close
DoEvents

Rem *** Viimasesse veergu üleveokaalu andmete kirjutamine ***
xSQL = "select name, cast(ip_trend_time as char FORMAT 'YYYY-MM-DD HH:MI:SS') as aeg, ip_trend_value, ip_trend_qstatus from ip_analogdef " & _
       "where name like '10ECA62CF102' and ip_trend_time>cast(timestamp'" + xA + "' as timestamp) and ip_trend_time<cast(timestamp'" + xL + "' as timestamp) order by aeg"

DoEvents
aData.Open xSQL, aConn, , , -1
DoEvents

oL.Cells(1, 30).Value = "Lindi kaal"
If Not aData.EOF Then
xR = 3: xAeg = CDate(aData.fields(1).Value)
Do While oL.Cells(xR, 1).Value <> Empty
  xR = xR + 1: xMaht = 0

  Rem Uus rida
  Do While CDate(aData.fields(1).Value) < oL.Cells(xR, 2).Value + fKaaluNihe(oL.Cells(xR, 2).Value)
    xMaht = xMaht + aData.fields(2).Value * (CDate(aData.fields(1).Value) - xAeg)
    xAeg = CDate(aData.fields(1).Value)
    aData.MoveNext
    If aData.EOF Then Exit Do
  Loop
  If aData.EOF Then Exit Do
  oL.Cells(xR, 30).Value = xMaht * 24
Loop
End If

DoEvents
aData.Close
DoEvents
DoEvents
aConn.Close
DoEvents

Set aData = Nothing
Set aConn = Nothing
End Sub