1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
|
private sub command1_click()
Dim Ex As New Excel.Application
Set Ex = New Excel.Application
Ex.Workbooks.Close
Ex.Workbooks.Add "F:\SIPVB\excel\Devis.xlsx"
Set Rsmatutil = New ADODB.Recordset
req = "select Concerner.ref_mat,Concerner.quant_util,Concerner.prixtot,Materiel.design_mat,Materiel.PU from Concerner inner join Materiel on Concerner.ref_mat=Materiel.ref_mat where Concerner.ref_devis='" & refdev.Text & "' and Concerner.categorie='EQUIPEMENTS INTERVENTION' "
Rsmatutil.Open req, cnx, adOpenDynamic, adLockOptimistic
'cnx.Execute req
e = 0
'Rsmatutil.MoveFirst
If Rsmatutil.EOF = True Then
DoEvents
Else
Sum = 0
f = Range("EI").Row
While Not Rsmatutil.EOF
e = e + 1
Sum = Sum + Val(Rsmatutil("quant_util")) * Val(Rsmatutil("PU"))
Rsmatutil.MoveNext
Wend
For s = 1 To e - 1 Step 1
Application.ScreenUpdating = False
'On Error Resume Next
With Ex.Rows(f)
.Insert
.Copy
.Offset(-1, 0).PasteSpecial
End With
Application.CutCopyMode = False
Next s
Application.ScreenUpdating = True
'Ex.Rows(f).Delete
Rsmatutil.MoveFirst
For j = 1 To e Step 1
Ex.Cells((f - 1) + j, 1) = Rsmatutil("ref_mat")
Ex.Cells((f - 1) + j, 2) = Val(Rsmatutil("quant_util"))
Ex.Cells((f - 1) + j, 3) = Rsmatutil("design_mat")
Ex.Cells((f - 1) + j, 9) = Val(Rsmatutil("PU"))
Rsmatutil.MoveNext
Next j
Ex.Cells((f - 1) + j, 10) = Sum
End If
'********************************************************************************
Ex.Visible = True
end sub |
Partager