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
| Sub test()
Dim Connex As ADODB.Connection
Dim Record As ADODB.Recordset
Set Connex = New ADODB.Connection
Set Record = New ADODB.Recordset
Dim requete As String
Dim Repertnom As String
Repertnom = "E:\TacheAnnexe\outilASCOM\Prototype\base"
Connex.ConnectionString = "ODBC;DBQ=" & Repertnom & ";DefaultDir=E:\; " & "Driver={Microsoft Text Driver (*.txt; *.csv)}; " & "DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferS"
Connex.Open
sel1 = " SELECT [Lib mode de dépot SOLL] as mode, [Identifiant SOLL] as Numero, [Nom Acteur Traitant SOLL] &' '& [Prenom Acteur Traitant SOLL] as Nom, [Nom Resp Acteur Traitant SOLL] as RDV,[Date de création SOLL] as dateCrea, [Nom Resp Acteur Traitant SOLL] as marche, 'soll traitées' as requete"
fro1 = " FROM SOLL_traites.csv as traite"
sel2 = " SELECT [Lib mode de dépot SOLL] as mode, [Identifiant SOLL] as Numero, [Nom et prénom Act Créa SOLL] as Nom, [Nom Resp Act Créa SOLL] as RDV,[Date de création SOLL] as dateCrea, [Nom Resp Act Créa SOLL] as marche, 'soll créée' as requete"
fro2 = " FROM SOLL_créées.csv as cree"
sel3 = " SELECT [Lib mode de dépot RECLA] as mode, [Identifiant RECLA] as Numero, [Nom et prénom Act Créa RECLA] as Nom, [Nom Resp Act Créa RECLA] as RDV,[Date de création RECLA] as dateCrea, [Nom Resp Act Créa RECLA] as marche, 'ASCOM reclamation' as requete"
fro3 = " FROM ASCOM_reclamations.csv as traite"
'sel4 = " SELECT [Libellé mode de depot] as mode, [Num] as Numero, [Nom Act Creat] &' '& [Prenom Act Creat] as Nom, [Nom Resp Act Creat] as RDV,[Date de création] as dateCrea, [Nom Resp Act Creat] as marche, 'ASCOM commande' as requete"
'fro4 = " FROM ASCOM_commandes.csv as traite"
'sel4 = " SELECT [Libellé mode de depot] as mode, [Num. de commande] as Numero, [Nom Act Creat] &' '& [Prenom Act Creat] as Nom, [Nom Resp Act Creat] as RDV,[Date de création] as dateCrea, [Nom Resp Act Creat] as marche, 'ASCOM commande' as requete"
'fro4 = " FROM ASCOM_commandes.csv as commandes"
requete = sel1 & fro1 & " UNION " & sel2 & fro2 & " UNION " & sel3 & fro3
Record.Open requete, Connex
Set f = Sheets("Données")
Record.MoveFirst
i = 2
Do While Not Record.EOF
f.Cells(i, 1) = Record("mode")
f.Cells(i, 2) = Record("Numero")
f.Cells(i, 3) = Record("Nom")
f.Cells(i, 4) = Record("RDV")
f.Cells(i, 5) = Record("marche")
f.Cells(i, 6) = Record("dateCrea")
f.Cells(i, 7) = Record("requete")
i = i + 1
Record.MoveNext
Loop
Record.Close
requete = "SELECT * FROM ASCOM_commandes.csv as commandes"
Record.Open requete, Connex
Do While Not Record.EOF
f.Cells(i, 1) = Record(2).Value
f.Cells(i, 2) = Record(0).Value
f.Cells(i, 3) = Record(7).Value & " " & Record(8).Value
f.Cells(i, 4) = Record(14).Value
f.Cells(i, 5) = Record(14).Value
f.Cells(i, 6) = Record(4).Value
f.Cells(i, 7) = "ASCOM commande"
i = i + 1
Record.MoveNext
Loop
Connex.Close
End Sub |
Partager