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
| Sub maj()
If Len(Range("c1").Value) = 6 Then
Range("C4").Select
With Range("C4").QueryTable
.Connection = _
"ODBC;DSN=EFMAIN46;UID=sa;PWD=;APP=2007 Microsoft Office system;WSID=LBIDARD;DATABASE=EFMAIN46;LANGUAGE=Français;"
.CommandText = Array( _
"SELECT DISTINCT " & Chr(13) & "" & Chr(10) & " PROD_BATCHES.PR_BATCH_ID as [Lot], CUSTOMERS.CUST_NAME as [Client], PROD_FOLDERS.TITLE as [Référence Dossier], PROD_FOLDERS.cust_DELAY as [Délai Client], conver" _
, _
"t (int,SUM(PROD_FOLD_DET.ORDER_QTY) ) as [Qté]" & Chr(13) & "" & Chr(10) & " , PROD_FOLD_DET.FOLDER_ID as [Dossier]" & Chr(13) & "" & Chr(10) & "FROM PROD_BATCHES INNER JOIN" & Chr(13) & "" & Chr(10) & " PROD_FOLD_DET ON PROD_BATCHES" _
, _
".PR_BATCH_ID = PROD_FOLD_DET.PR_BATCH_ID INNER JOIN" & Chr(13) & "" & Chr(10) & " CUSTOMERS ON PROD_FOLD_DET.CUST_ID = CUSTOMERS.CUST_ID INNER JOIN" & Chr(13) & "" & Chr(10) & " PROD_FOLDERS ON PROD_FOLD_DET.FOLDER" _
, _
"_ID = PROD_FOLDERS.FOLDER_ID" & Chr(13) & "" & Chr(10) & "GROUP BY PROD_BATCHES.PR_BATCH_ID, CUSTOMERS.CUST_NAME, PROD_FOLDERS.TITLE, PROD_FOLDERS.cust_DELAY ,PROD_FOLD_DET.ART_GROUP_ID, " & Chr(13) & "" & Chr(10) & " PROD_FOLD_DET.FOL" _
, _
"DER_ID" & Chr(13) & "" & Chr(10) & "HAVING (PROD_FOLD_DET.ART_GROUP_ID = 1) AND (PROD_BATCHES.PR_BATCH_ID = " & Range("c1").Value & ")" & Chr(13) & "" & Chr(10) & "ORDER BY PROD_FOLD_DET.FOLDER_ID" _
)
.Refresh BackgroundQuery:=False
End With
With ActiveWorkbook.Connections("Lancer la requête à partir de EFMAIN461"). _
ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT BOOKING_1.REFERENCE, BOOKING_1.DESCRIPTION, SUPPLIES.RACK_1, SUM(BOOKING_1.NEED_QTY) , BOOKING_1.UNIT" _
, _
"" & Chr(13) & "" & Chr(10) & "FROM PROD_FOLDERS INNER JOIN" & Chr(13) & "" & Chr(10) & " BOOKING_1 ON PROD_FOLDERS.FOLDER_ID = BOOKING_1" _
, _
".FOLDER_ID INNER JOIN" & Chr(13) & "" & Chr(10) & " SUPPLIES ON BOOKING_1.ARTICLE_ID = SUPPLIES.ARTICLE_ID" & Chr(13) & "" & Chr(10) & "WHERE " _
, _
" (PROD_FOLDERS.PR_BATCH_ID = " & Range("c1").Value & ") AND (SUPPLIES.PROD_AREA = 'FD Spec')" & Chr(13) & "" & Chr(10) & "GROUP BY BOOKING_1.REFERENCE, BOOKING_1" _
, ".DESCRIPTION, SUPPLIES.RACK_1, BOOKING_1.UNIT")
.CommandType = xlCmdSql
.Connection = _
"ODBC;DSN=EFMAIN46;UID=sa;PWD=;APP=2007 Microsoft Office system;WSID=LBIDARD;DATABASE=EFMAIN46;LANGUAGE=Français;"
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Lancer la requête à partir de EFMAIN461")
.Name = "Lancer la requête à partir de EFMAIN461"
.Description = ""
End With
ActiveWorkbook.Connections("Lancer la requête à partir de EFMAIN461").Refresh
Else
MsgBox "Numéro de lot incomplet!"
End If
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Worksheets("Feuil-de-prep").PrintOut Copies:=1
End Sub |
