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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
|
Dim oConn As ADODB.Connection
Public Sub ConnectDB()
Set oConn = New ADODB.Connection
Dim str As String
str = "DSN=MysqlSIO;Uid=root;Pwd=;"
oConn.Open str
End Sub
Public Sub InsertData()
Application.ScreenUpdating = False
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
ConnectDB
Dim sql As String
'sql = "CALL rmpsql.Pieces_bonnes()"
sql = "Select typepiece, numserie , Datecontrole , LuminanceActive, LuminanceSTBYCRS , Luminanceoff ,LuminanceGrdCercle , Luminancedroit , LuminanceGauche,LuminanceOn "
sql = sql & "luminanceoff , luminancepetitcercle , Luminancestbynav ,caractereActive, caracterestbycrs ,caracterestbynav , caractereoff , caractereon from luminance "
sql = sql & "WHERE ( LuminanceActive between '1.5' And '4') AND (luminancestbycrs between '1.5' AND '4') AND (luminanceGrdCercle between '1.5' AND '4.0') AND (luminancegauche between '1.5' AND '4.0') AND "
sql = sql & "(Luminancedroit between '1.5' AND '4.0') AND (Luminancestbynav between '1.5' AND '4.0') and (luminanceON between '1.5' AND '4.0') AND "
sql = sql & "(LuminanceOFF between '1.5' AND '4.0') AND (luminancepetitcercle between '1.5' and '4.0')"
sql = sql & "Union "
sql = sql & "Select typepiece, numserie , Datecontrole , LuminanceActive, LuminanceSTBYCRS , Luminanceoff ,LuminanceGrdCercle , Luminancedroit , LuminanceGauche,LuminanceOn "
sql = sql & "luminanceoff , luminancepetitcercle , Luminancestbynav ,caractereActive, caracterestbycrs ,caracterestbynav , caractereoff , caractereon from reprise "
sql = sql & "where ( LuminanceActive between '1.5' And '4') "
sql = sql & "AND (luminancestbycrs between '1.5' AND '4') AND (luminanceGrdCercle between '1.5' AND '4.0') AND (luminancegauche between '1.5' AND '4.0') AND "
sql = sql & "(Luminancedroit between '1.5' AND '4.0') AND (Luminancestbynav between '1.5' AND '4.0') and (luminanceON between '1.5' AND '4.0') AND "
sql = sql & "(LuminanceOFF between '1.5' AND '4.0') AND (luminancepetitcercle between '1.5' and '4.0');"
rs.Open sql, oConn, adOpenDynamic, adLockOptimistic
Dim i As Integer
For i = 0 To rs.Fields.Count - 1
Sheets("Importation").Cells(5, i + 1) = rs.Fields(i).Name
Next
ActiveSheet.Cells.Clear
ActiveSheet.Cells.Interior.ColorIndex = xlColorIndexNone
ActiveSheet.Range("A6").CopyFromRecordset rs
rs.Close
oConn.Close
Set oConn = Nothing
Set rs = Nothing
Call MiseEnPage
Call Detection
Call Macro1
ActiveWindow.Zoom = 80
Sheets("importation").Range("A5").Select
Sheets("importation").Columns.AutoFit
Call Filtrage
With Worksheets("importation")
If .FilterMode = True Then .ShowAllData
End With
Sheets("importation").Select
Sheets("importation").Range("A5").Select
With Worksheets("importation")
If .FilterMode = True Then .ShowAllData
End With
Application.ScreenUpdating = True
End Sub |
Partager