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 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
| Public Function GetSAPData() As Integer
Dim ctlLogon As Object
Dim funcControl As Object
Dim ctlTableFactory As Object
Dim objConnection As Object
Dim booReturn As Boolean
Dim RFC_READ_TABLE As Object
Dim strExport1 As Object
Dim strExport2 As Object
Dim tblOptions As Object
Dim tblData As Object
Dim tblFields As Object
Dim intRow As Integer
Dim iStr_Split As Variant
Dim iStr_SQL As String
Dim iRec As Recordset
Dim iBln_Prem As Boolean
Dim iBln_Data As Boolean
ecritLog "Connection à SAP."
Set ctlLogon = CreateObject("SAP.LogonControl.1")
Set funcControl = CreateObject("SAP.Functions")
Set ctlTableFactory = CreateObject("SAP.TableFactory.1")
Set objConnection = ctlLogon.NewConnection
objConnection.ApplicationServer = fngGetParamString("SAPLogin", "ApplicationServer")
objConnection.System = fngGetParamString("SAPLogin", "System") '"DE3"
objConnection.SystemNumber = fngGetParamString("SAPLogin", "SystemNumber") '"0"
objConnection.Client = fngGetParamString("SAPLogin", "Client") '"100"
objConnection.Language = fngGetParamString("SAPLogin", "Language") '"FR"
objConnection.User = fngGetParamString("SAPLogin", "User") ' Le User SAP
objConnection.Password = fngGetParamMdP("SAPLogin", "Password") 'Le pass SAP
booReturn = objConnection.Logon(0, True)
If booReturn <> True Then
ecritLog "Impossible de se logger à SAP."
GoTo Erreur
Else
ecritLog "Connection à SAP OK."
funcControl.Connection = objConnection
Set RFC_READ_TABLE = funcControl.Add("RFC_READ_TABLE")
Set tblOptions = RFC_READ_TABLE.Tables("OPTIONS")
Set tblData = RFC_READ_TABLE.Tables("DATA")
Set strExport1 = RFC_READ_TABLE.Exports("QUERY_TABLE")
Set strExport2 = RFC_READ_TABLE.Exports("DELIMITER")
strExport1.Value = "VBKD"
strExport2.Value = "|"
iStr_SQL = "SELECT DocVente, DocVentePoste " & _
"FROM Commandes " & _
"WHERE not(InfoEnvoyee) "
Set iRec = gObj_DataBase.OpenRecordset(iStr_SQL)
iBln_Prem = True
intRow = 2
Set tblFields = RFC_READ_TABLE.Tables("FIELDS")
tblFields.AppendRow
tblFields(1, "FIELDNAME") = "VBELN"
tblFields.AppendRow
tblFields(2, "FIELDNAME") = "POSNR"
tblFields.AppendRow
tblFields(3, "FIELDNAME") = "BSTKD"
tblFields.AppendRow
tblFields(4, "FIELDNAME") = "BSTKD_E"
If Not iRec.EOF Then
tblOptions.AppendRow
tblOptions.AppendRow
End If
ecritLog "Récupération des données (Numéros de commande) dans SAP."
While Not iRec.EOF
tblOptions(1, "TEXT") = "VBELN EQ '" & iRec.Fields(0).Value & "'"
tblOptions(2, "TEXT") = " AND POSNR EQ '" & Left("000000", 6 - Len(iRec.Fields(1).Value)) & iRec.Fields(1).Value & "'"
If RFC_READ_TABLE.Call = True Then
Else
ecritLog "Erreur lors de la récupération des données : " & RFC_READ_TABLE.exception
objConnection.Logoff
GoTo Erreur
End If
iRec.MoveNext
Wend
If tblData.RowCount > 0 Then
For intRow = 1 To tblData.RowCount
iStr_Split = Split(tblData(intRow, "WA"), "|")
iStr_SQL = "Update COMMANDES " & _
"set BSTKD = '" & Left(Trim(iStr_Split(2)), Len(Trim(iStr_Split(2))) - 3) & "', " & _
"BSTKD_E = '" & Trim(iStr_Split(3)) & "' " & _
"WHERE DocVente = '" & iStr_Split(0) & "' " & _
"AND DocVentePoste = " & iStr_Split(1)
gObj_DataBase.Execute iStr_SQL, dbFailOnError
ecritLog "Récupération des données OK pour la commande " & iStr_Split(0) & " - " & iStr_Split(1)
Next
Else
ecritLog "Pas de données récupérées." ' pour la commande " ' & iStr_Split(0) & " - " & iStr_Split(1)
End If
iRec.Close
objConnection.Logoff
Set objConnection = Nothing
Set ctlLogon = Nothing
Set funcControl = Nothing
Set ctlTableFactory = Nothing
Set RFC_READ_TABLE = Nothing
Set strExport1 = Nothing
Set strExport2 = Nothing
Set tblOptions = Nothing
Set tblData = Nothing
Set tblFields = Nothing |
Partager