- Private Sub CommandButton1_Click()
-
- ' ***** DEMANDE L'ACTIVATION DU COMPOSANT MICROSOFT ACTIVEX DATA OBJECT 2.7 LIBRARY
- ' ***** DEPUIS MENU "OUTILS" - "RÉFÉRENCES" DE L'ÉDITEUR VISUAL BASIC
-
- Dim Cnx As New ADODB.Connection
- Dim Rst As New ADODB.Recordset
- Dim Année As String * 4
- Dim Mois As String * 2
- Dim Jour As String * 2
- Dim AMJ As String * 8
- Dim Req1 As String
- Dim Req2 As String
- Année = TextBox1
- Mois = TextBox2
- Jour = TextBox3
- AMJ = Année & Mois & Jour
-
- ' ***** INSTRUCTIONS DE SÉLECTION DES CHAMPS ET DE JOINTURE *****
- Req1 = "select d.inputdate, cu.inv_name, c.sit_name, c.sit_town, a.ct_name, a.ct_town, d.dwgbbsnum, "
- Req1 = Req1 & "d.esrc_file, d.rc_num, r.ps_code, r.fabweight, d.delivstart, r.cust_ref from dwgbbs as d "
- Req1 = Req1 & "join ref_ps as r on r.esrc_file = d.esrc_file and r.rc_num = d.rc_num and r.ps_title = d.dwgbbsnum "
- Req1 = Req1 & "join contract as c on c.esrc_file = d.esrc_file and c.rc_num = d.rc_num "
- Req1 = Req1 & "left join contradr as a on a.esrc_file = d.esrc_file and a.es_num = d.es_num and a.seq_num = r.addr_num "
- Req1 = Req1 & "join customer as cu on cu.cust_code = c.cust_code"
-
- ' ***** SÉLECTION SELON DATE SAISIE DANS LE FORMULAIRE *****
- Req2 = "where d.esrc_file = 'cht05' and d.rc_num <> 4 and d.inputdate = " & AMJ
- Req1 = Req1 & " " & Req2
-
- ' ***** OUVERTURE DE LA BASE *****
- Cnx.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Favre;Data Source=Serveur-corc"
-
- ' ***** RECHERCHE DE LA DERNIÈRE CELLULE NON VIDE *****
- Range("A10000").Select
- Selection.End(xlUp).Select
-
- ' ***** OUVERTURE DU RECORDSET *****
- Rst.Open Req1, Cnx, adOpenKeyset
-
- ' ***** COPIE DU RECORDSET DEPUIS LA LIGNE SUIVANTE *****
- ActiveCell.Offset(1, 0).CopyFromRecordset Rst
-
- ' ***** FERMETURE ET VIDAGE *****
- Rst.Close: Set Rst = Nothing
- Cnx.Close: Set Cnx = Nothing
- Unload UserForm1
- Application.ScreenUpdating = True
- End Sub
Private Sub CommandButton1_Click()
' ***** DEMANDE L'ACTIVATION DU COMPOSANT MICROSOFT ACTIVEX DATA OBJECT 2.7 LIBRARY
' ***** DEPUIS MENU "OUTILS" - "RÉFÉRENCES" DE L'ÉDITEUR VISUAL BASIC
Dim Cnx As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Dim Année As String * 4
Dim Mois As String * 2
Dim Jour As String * 2
Dim AMJ As String * 8
Dim Req1 As String
Dim Req2 As String
Année = TextBox1
Mois = TextBox2
Jour = TextBox3
AMJ = Année & Mois & Jour
' ***** INSTRUCTIONS DE SÉLECTION DES CHAMPS ET DE JOINTURE *****
Req1 = "select d.inputdate, cu.inv_name, c.sit_name, c.sit_town, a.ct_name, a.ct_town, d.dwgbbsnum, "
Req1 = Req1 & "d.esrc_file, d.rc_num, r.ps_code, r.fabweight, d.delivstart, r.cust_ref from dwgbbs as d "
Req1 = Req1 & "join ref_ps as r on r.esrc_file = d.esrc_file and r.rc_num = d.rc_num and r.ps_title = d.dwgbbsnum "
Req1 = Req1 & "join contract as c on c.esrc_file = d.esrc_file and c.rc_num = d.rc_num "
Req1 = Req1 & "left join contradr as a on a.esrc_file = d.esrc_file and a.es_num = d.es_num and a.seq_num = r.addr_num "
Req1 = Req1 & "join customer as cu on cu.cust_code = c.cust_code"
' ***** SÉLECTION SELON DATE SAISIE DANS LE FORMULAIRE *****
Req2 = "where d.esrc_file = 'cht05' and d.rc_num <> 4 and d.inputdate = " & AMJ
Req1 = Req1 & " " & Req2
' ***** OUVERTURE DE LA BASE *****
Cnx.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Favre;Data Source=Serveur-corc"
' ***** RECHERCHE DE LA DERNIÈRE CELLULE NON VIDE *****
Range("A10000").Select
Selection.End(xlUp).Select
' ***** OUVERTURE DU RECORDSET *****
Rst.Open Req1, Cnx, adOpenKeyset
' ***** COPIE DU RECORDSET DEPUIS LA LIGNE SUIVANTE *****
ActiveCell.Offset(1, 0).CopyFromRecordset Rst
' ***** FERMETURE ET VIDAGE *****
Rst.Close: Set Rst = Nothing
Cnx.Close: Set Cnx = Nothing
Unload UserForm1
Application.ScreenUpdating = True
End Sub