Sub db2xls_MN()
Dim t As ADODB.Recordset
Dim sql As String
Dim wb As Workbook
Dim c As Integer
Dim obj As String
Dim tabl1, tabl2, tabl3, tabl4, tabl5 As String
Set wb = ThisWorkbook
Set wst = wb.Worksheets("BDSikg")
wst.Range("E2:L500").ClearContents
wst.Range("E2:L500").Borders.LineStyle = xlLineStyleNone
If flagOfStart Then
flagOfStart = False
End If
obj = wst.Range("A3").Value
tabl1 = wst.Range("A4").Value '
tabl2 = wst.Range("A5").Value '
tabl3 = wst.Range("A6").Value '
tabl4 = wst.Range("A7").Value '
tabl5 = wst.Range("A8").Value '
dt_start = sqlFormatDate2(wst.Range("A1").Value)
dt_end = sqlFormatDate2(wst.Range("A2").Value)
If obj = "Все" Then
sql = "SELECT * FROM " & tabl1
sql = sql & " WHERE EndDate BETWEEN " & dt_start & " AND " & dt_end
sql = sql & " ORDER BY EndDate"
End If
If Not dbConnect Then
Set db = Nothing
Exit Sub
End If
Set t = createRecordset(sql)
c = 2 'начинаем с 2-ой строки
If t Is Nothing Then
Else
While Not t.EOF
wst.Range("E" & Format(c, "00")).Value = t.Fields("StartDate").Value
wst.Range("F" & Format(c, "00")).Value = t.Fields("EndDate").Value
wst.Range("G" & Format(c, "00")).Value = t.Fields("Region").Value
wst.Range("H" & Format(c, "00")).Value = t.Fields("Location").Value
wst.Range("I" & Format(c, "00")).Value = t.Fields("LineName").Value
wst.Range("J" & Format(c, "00")).Value = Round(t.Fields("P").Value, 2)
wst.Range("K" & Format(c, "00")).Value = Round(t.Fields("T").Value, 2)
wst.Range("L" & Format(c, "00")).Value = t.Fields("Q_su").Value
c = c + 1
t.MoveNext
Wend
t.Close
End If
If obj = "Все" Then
sql = "SELECT * FROM " & tabl2
sql = sql & " WHERE EndDate BETWEEN " & dt_start & " AND " & dt_end
sql = sql & " ORDER BY EndDate"
End If
Set t = createRecordset(sql)
c = 50 'начинаем с 50 строки
If t Is Nothing Then
Else
While Not t.EOF
wst.Range("E" & Format(c, "00")).Value = t.Fields("StartDate").Value
wst.Range("F" & Format(c, "00")).Value = t.Fields("EndDate").Value
wst.Range("G" & Format(c, "00")).Value = t.Fields("Region").Value
wst.Range("H" & Format(c, "00")).Value = t.Fields("Location").Value
wst.Range("I" & Format(c, "00")).Value = t.Fields("LineName").Value
wst.Range("J" & Format(c, "00")).Value = Round(t.Fields("P").Value, 2)
wst.Range("K" & Format(c, "00")).Value = Round(t.Fields("T").Value, 2)
wst.Range("L" & Format(c, "00")).Value = t.Fields("Q_su").Value
c = c + 1
t.MoveNext
Wend
t.Close
End If
If obj = "Все" Then
sql = "SELECT * FROM " & tabl3
sql = sql & " WHERE EndDate BETWEEN " & dt_start & " AND " & dt_end
sql = sql & " ORDER BY EndDate"
End If
Set t = createRecordset(sql)
c = 120 'начинаем с 120-й строки
If t Is Nothing Then
Else
While Not t.EOF
wst.Range("E" & Format(c, "00")).Value = t.Fields("StartDate").Value
wst.Range("F" & Format(c, "00")).Value = t.Fields("EndDate").Value
wst.Range("G" & Format(c, "00")).Value = t.Fields("Region").Value
wst.Range("H" & Format(c, "00")).Value = t.Fields("Location").Value
wst.Range("I" & Format(c, "00")).Value = t.Fields("LineName").Value
wst.Range("J" & Format(c, "00")).Value = Round(t.Fields("P").Value, 2)
wst.Range("K" & Format(c, "00")).Value = Round(t.Fields("T").Value, 2)
wst.Range("L" & Format(c, "00")).Value = t.Fields("Q_su").Value
c = c + 1
t.MoveNext
Wend
t.Close
End If
If obj = "Все" Then
sql = "SELECT * FROM " & tabl4
sql = sql & " WHERE EndDate BETWEEN " & dt_start & " AND " & dt_end
sql = sql & " ORDER BY EndDate"
End If
Set t = createRecordset(sql)
c = 210 'начинаем с 210 строки
If t Is Nothing Then
Else
While Not t.EOF
wst.Range("E" & Format(c, "00")).Value = t.Fields("StartDate").Value
wst.Range("F" & Format(c, "00")).Value = t.Fields("EndDate").Value
wst.Range("G" & Format(c, "00")).Value = t.Fields("Region").Value
wst.Range("H" & Format(c, "00")).Value = t.Fields("Location").Value
wst.Range("I" & Format(c, "00")).Value = t.Fields("LineName").Value
wst.Range("J" & Format(c, "00")).Value = Round(t.Fields("P").Value, 2)
wst.Range("K" & Format(c, "00")).Value = Round(t.Fields("T").Value, 2)
wst.Range("L" & Format(c, "00")).Value = t.Fields("Q_su").Value
c = c + 1
t.MoveNext
Wend
t.Close
End If
If obj = "Все" Then
sql = "SELECT * FROM " & tabl5
sql = sql & " WHERE EndDate BETWEEN " & dt_start & " AND " & dt_end
sql = sql & " ORDER BY EndDate"
End If
Set t = createRecordset(sql)
c = 260 'начинаем с 260-й строки
If t Is Nothing Then
Else
While Not t.EOF
wst.Range("E" & Format(c, "00")).Value = t.Fields("StartDate").Value
wst.Range("F" & Format(c, "00")).Value = t.Fields("EndDate").Value
wst.Range("G" & Format(c, "00")).Value = t.Fields("Region").Value
wst.Range("H" & Format(c, "00")).Value = t.Fields("Location").Value
wst.Range("I" & Format(c, "00")).Value = t.Fields("LineName").Value
wst.Range("J" & Format(c, "00")).Value = Round(t.Fields("P").Value, 2)
wst.Range("K" & Format(c, "00")).Value = Round(t.Fields("T").Value, 2)
wst.Range("L" & Format(c, "00")).Value = t.Fields("Q_su").Value
c = c + 1
t.MoveNext
Wend
t.Close
End If
db.Close
Set db = Nothing
Set wb = Nothing
End Sub
|