Merci beaucoup pour ta reponse,
desole si j'ai pas mis le code de fct_fill_excel.
Private Sub fct_fill_excel(ByVal lines() As String, ByVal sqltype As String)
Dim line_0() As String
Try
Dim line_1() As String
Dim line_2() As String
Dim line_3() As String
Dim line_x() As String
Dim i As Integer
Dim strsql As String
Dim qty As Decimal
Dim S1, ErrStr As String
Dim po_xl As New Excel.Application
Dim po_book As Excel.Workbook
Dim po_sheet As Excel.Worksheet
'po_xl.DisplayAlerts = False
If sqltype = "PO" Or sqltype = "MA" Then
'lines(0)
line_0 = Split(Replace(Replace(lines(0), Chr(34), ""), "'", " "), ";")
'lines(1)
line_1 = Split(Replace(Replace(lines(1), Chr(34), ""), "'", " "), ";")
'lines(2)
line_2 = Split(Replace(Replace(lines(2), Chr(34), ""), "'", " "), ";")
'lines(3)
line_3 = Split(Replace(Replace(lines(3), Chr(34), ""), "'", " "), ";")
'Création des settings pour la gestion du po dans excel
'po_xl = New Excel.Application
If line_1(1) = "E" Then
po_book = po_xl.Workbooks.Open(Filename:=layout_e)
'po_xl.Workbooks.Open(Filename:=layout_e)
Else
po_book = po_xl.Workbooks.Open(Filename:=layout_f)
'po_xl.Workbooks.Open(Filename:=layout_f)
End If
po_sheet = po_book.ActiveSheet
po_xl.Visible = False
'Création de l'entête du PO
'to
If line_3(1) <> "" And line_3(2) <> "" Then
po_sheet.Cells(2, 7) = line_2(0).ToUpper
po_sheet.Cells(3, 7) = line_3(0).ToUpper
po_sheet.Cells(4, 7) = line_3(1).ToUpper
po_sheet.Cells(5, 7) = line_3(2).ToUpper
po_sheet.Cells(6, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper
po_sheet.Cells(7, 7) = line_3(6).ToUpper
ElseIf line_3(1) = "" And line_3(2) = "" Then
po_sheet.Cells(2, 7) = line_2(0).ToUpper
po_sheet.Cells(3, 7) = line_3(0).ToUpper
po_sheet.Cells(4, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper
po_sheet.Cells(7, 7) = line_3(6).ToUpper
ElseIf line_3(1) <> "" And line_3(2) = "" Then
po_sheet.Cells(2, 7) = line_2(0).ToUpper
po_sheet.Cells(3, 7) = line_3(0).ToUpper
po_sheet.Cells(4, 7) = line_3(1).ToUpper
po_sheet.Cells(5, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper
po_sheet.Cells(7, 7) = line_3(6).ToUpper
ElseIf line_3(1) = "" And line_3(2) <> "" Then
po_sheet.Cells(2, 7) = line_2(0).ToUpper
po_sheet.Cells(3, 7) = line_3(0).ToUpper
po_sheet.Cells(4, 7) = line_3(2).ToUpper
po_sheet.Cells(5, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper
po_sheet.Cells(7, 7) = line_3(6).ToUpper
End If
'ship to
If line_2(6) <> "" And line_2(7) <> "" Then
po_sheet.Cells(2, 15) = line_2(21).ToUpper
po_sheet.Cells(3, 15) = line_2(14).ToUpper
po_sheet.Cells(4, 15) = line_2(6).ToUpper
po_sheet.Cells(5, 15) = line_2(7).ToUpper
po_sheet.Cells(6, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper
po_sheet.Cells(7, 15) = line_2(12).ToUpper
ElseIf line_2(6) = "" And line_2(7) = "" Then
po_sheet.Cells(2, 15) = line_2(21).ToUpper
po_sheet.Cells(3, 15) = line_2(14).ToUpper
po_sheet.Cells(4, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper
po_sheet.Cells(7, 15) = line_2(12).ToUpper
ElseIf line_2(6) <> "" And line_2(7) = "" Then
po_sheet.Cells(2, 15) = line_2(21).ToUpper
po_sheet.Cells(3, 15) = line_2(14).ToUpper
po_sheet.Cells(4, 15) = line_2(6).ToUpper
po_sheet.Cells(5, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper
po_sheet.Cells(7, 15) = line_2(12).ToUpper
ElseIf line_2(6) = "" And line_2(7) <> "" Then
po_sheet.Cells(2, 15) = line_2(21).ToUpper
po_sheet.Cells(3, 15) = line_2(14).ToUpper
po_sheet.Cells(4, 15) = line_2(7).ToUpper
po_sheet.Cells(5, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper
po_sheet.Cells(7, 15) = line_2(12).ToUpper
End If
'# cmd
po_sheet.Cells(10, 1) = line_2(1).ToUpper
'Date cmd
If line_1(1) = "E" Then
po_sheet.Cells(10, 5) = line_2(2).Substring(3, 2) & "/" & line_2(2).Substring(6, 2) & "/" & line_2(2).Substring(0, 2)
Else
po_sheet.Cells(10, 5) = line_2(2).Substring(6, 2) & "/" & line_2(2).Substring(3, 2) & "/" & line_2(2).Substring(0, 2)
End If
'Date livrais
If line_1(1) = "E" Then
po_sheet.Cells(10, 6) = line_2(3).Substring(3, 2) & "/" & line_2(3).Substring(6, 2) & "/" & line_2(3).Substring(0, 2)
Else
po_sheet.Cells(10, 6) = line_2(3).Substring(6, 2) & "/" & line_2(3).Substring(3, 2) & "/" & line_2(3).Substring(0, 2)
End If
'Ship by
po_sheet.Cells(10, 7) = line_2(17).ToUpper
'FOB
po_sheet.Cells(10, 10) = line_2(13).ToUpper
'tx
po_sheet.Cells(9, 16) = line_2(19).ToUpper
po_sheet.Cells(10, 16) = line_2(20).ToUpper
'# fact
po_sheet.Cells(12, 1) = line_2(4).ToUpper
'Buyer
po_sheet.Cells(12, 5) = line_2(12).ToUpper
'Terms
po_sheet.Cells(12, 8) = line_2(5).ToUpper
'Freight
po_sheet.Cells(12, 11) = line_2(11).ToUpper
'Création des lignes de PO
For i = 4 To lines.GetUpperBound(0) - 1
line_x = Split(Replace(Replace(lines(i), Chr(34), ""), "'", " "), ";")
qty = qty + line_x(1)
'line
po_sheet.Cells(12 + i, 1) = line_x(0).ToUpper
'qte
po_sheet.Cells(12 + i, 2) = line_x(1).ToUpper
'vendor-item
If line_x(2) = "" Then
po_sheet.Cells(12 + i, 4) = line_x(4).ToUpper
Else
po_sheet.Cells(12 + i, 4) = line_x(2).ToUpper
End If
'desc
po_sheet.Cells(12 + i, 6) = line_x(3).ToUpper
'item-number
po_sheet.Cells(12 + i, 11) = "#PHV : " & line_x(4).ToUpper
'udm
po_sheet.Cells(12 + i, 15) = line_x(5).ToUpper
'prix
po_sheet.Cells(12 + i, 16) = CDec(line_x(6))
'amount
po_sheet.Cells(12 + i, 17) = CDec(line_x(7))
Next
po_sheet.Cells(12 + i + 1, 4) = line_x(10).ToUpper & line_x(11).ToUpper & line_x(12).ToUpper & line_x(13).ToUpper & line_x(14).ToUpper & line_x(15).ToUpper & line_x(16).ToUpper & line_x(17).ToUpper & line_x(18).ToUpper & line_x(19).ToUpper & line_x(20).ToUpper & line_x(21).ToUpper & line_x(22).ToUpper & line_x(23).ToUpper
po_sheet.Cells(12 + i + 3, 2) = "________"
po_sheet.Cells(12 + i + 3, 17) = "___________"
If line_1(1) = "E" Then
If line_2(15) = "USD" Then
po_sheet.Cells(12 + i + 3, 5) = "****** AMOUNTS SPECIFIED IN U.S.A CURRENCY ******"
Else
po_sheet.Cells(12 + i + 3, 5) = "******* AMOUNTS SPECIFIED IN CDN CURRENCY *******"
End If
Else
If line_2(15) = "USD" Then
po_sheet.Cells(12 + i + 3, 5) = "****** MONTANTS SPÉCIFIÉS EN DEVISE CDN ******"
Else
po_sheet.Cells(12 + i + 3, 5) = "****** MONTANTS SPÉCIFIÉS EN DEVISE USA ******"
End If
End If
po_sheet.Cells(12 + i + 4, 2) = qty
po_sheet.Cells(12 + i + 4, 16) = "TOTAL : "
po_sheet.Cells(12 + i + 4, 17) = CDec(line_2(16))
po_xl.DisplayAlerts = False
If line_0(0) = "@" Then
If File.Exists(f_out & "ma_" & line_0(1) & ".xls") Then
File.Delete(f_out & "ma_" & line_0(1) & ".xls")
End If
po_sheet.SaveAs(f_out & "ma_" & line_0(1) & ".xls")
Else
If File.Exists(f_out & "po_" & line_0(1) & ".xls") Then
File.Delete(f_out & "po_" & line_0(1) & ".xls")
End If
po_sheet.SaveAs(f_out & "po_" & line_0(1) & ".xls")
End If
po_xl.Quit()
po_sheet = Nothing
po_book = Nothing
po_xl = Nothing
'Kill du process excel
Dim Processes As Process() = Nothing
Processes = Process.GetProcessesByName("EXCEL")
' Load ID Processes in Array
Dim intProcesses(Processes.GetUpperBound(0)) As Int16
Dim j As Int16
For j = 0 To Processes.GetUpperBound(0)
Process.GetProcessById(CInt(Processes(j).Id.ToString)).Kill()
Next
'Envoi du email si tel est le cas
If line_0(0) = "@" Then
'fct_wait_sec(5000)
fct_send_mail(envoyeur, line_1(0), "Purchase Order/Commande d'Achat # " & line_0(1), 1, "<font face=arial size=2 color='black'>Purchase Order/Commande d'Achat # " & line_0(1) & "</font><br><br><img src='" & signature & "'><br><br><b><font face=arial size=2 color='#aaaaaa'>DISCLAIMER: </b><BR>This communication is for use by the intended recipient and contains information that may be privileged, confidential or copyrighted under applicable law. If you are not the intended recipient, you are hereby formally notified that any use, copying or distribution of this e-mail, in whole or in part, is strictly prohibited. Please notify the sender by return e-mail from your system.</font>", "ma_" & line_0(1))
End If
End If
Catch ex As Exception
fct_send_mail(envoyeur, warning, "ERREUR PO VIA MAIL " & line_0(1), 0, ex.ToString, "none")
'# erreur no 8
fct_exec_sql("UPDATE pur_order SET err=8 WHERE pur_order.po_number='" & line_0(1) & "'")
je suis desole si c'est long, mais je suis bloque depuis 3 jours :(
Merci beaucoup pour ton aide
Merci beaucoup pour ta reponse,
desole si j'ai pas mis le code de fct_fill_excel.
Private Sub fct_fill_excel(ByVal lines() As String, ByVal sqltype As String) Dim line_0() As String Try Dim line_1() As String Dim line_2() As String Dim line_3() As String Dim line_x() As String Dim i As Integer Dim strsql As String Dim qty As Decimal Dim S1, ErrStr As String Dim po_xl As New Excel.Application Dim po_book As Excel.Workbook Dim po_sheet As Excel.Worksheet 'po_xl.DisplayAlerts = False If sqltype = "PO" Or sqltype = "MA" Then 'lines(0) line_0 = Split(Replace(Replace(lines(0), Chr(34), ""), "'", " "), ";") 'lines(1) line_1 = Split(Replace(Replace(lines(1), Chr(34), ""), "'", " "), ";") 'lines(2) line_2 = Split(Replace(Replace(lines(2), Chr(34), ""), "'", " "), ";") 'lines(3) line_3 = Split(Replace(Replace(lines(3), Chr(34), ""), "'", " "), ";") 'Création des settings pour la gestion du po dans excel 'po_xl = New Excel.Application If line_1(1) = "E" Then po_book = po_xl.Workbooks.Open(Filename:=layout_e) 'po_xl.Workbooks.Open(Filename:=layout_e) Else po_book = po_xl.Workbooks.Open(Filename:=layout_f) 'po_xl.Workbooks.Open(Filename:=layout_f) End If po_sheet = po_book.ActiveSheet po_xl.Visible = False 'Création de l'entête du PO 'to If line_3(1) <> "" And line_3(2) <> "" Then po_sheet.Cells(2, 7) = line_2(0).ToUpper po_sheet.Cells(3, 7) = line_3(0).ToUpper po_sheet.Cells(4, 7) = line_3(1).ToUpper po_sheet.Cells(5, 7) = line_3(2).ToUpper po_sheet.Cells(6, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper po_sheet.Cells(7, 7) = line_3(6).ToUpper ElseIf line_3(1) = "" And line_3(2) = "" Then po_sheet.Cells(2, 7) = line_2(0).ToUpper po_sheet.Cells(3, 7) = line_3(0).ToUpper po_sheet.Cells(4, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper po_sheet.Cells(7, 7) = line_3(6).ToUpper ElseIf line_3(1) <> "" And line_3(2) = "" Then po_sheet.Cells(2, 7) = line_2(0).ToUpper po_sheet.Cells(3, 7) = line_3(0).ToUpper po_sheet.Cells(4, 7) = line_3(1).ToUpper po_sheet.Cells(5, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper po_sheet.Cells(7, 7) = line_3(6).ToUpper ElseIf line_3(1) = "" And line_3(2) <> "" Then po_sheet.Cells(2, 7) = line_2(0).ToUpper po_sheet.Cells(3, 7) = line_3(0).ToUpper po_sheet.Cells(4, 7) = line_3(2).ToUpper po_sheet.Cells(5, 7) = line_3(3).ToUpper & ", " & line_3(4).ToUpper & " " & line_3(5).ToUpper po_sheet.Cells(7, 7) = line_3(6).ToUpper End If 'ship to If line_2(6) <> "" And line_2(7) <> "" Then po_sheet.Cells(2, 15) = line_2(21).ToUpper po_sheet.Cells(3, 15) = line_2(14).ToUpper po_sheet.Cells(4, 15) = line_2(6).ToUpper po_sheet.Cells(5, 15) = line_2(7).ToUpper po_sheet.Cells(6, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper po_sheet.Cells(7, 15) = line_2(12).ToUpper ElseIf line_2(6) = "" And line_2(7) = "" Then po_sheet.Cells(2, 15) = line_2(21).ToUpper po_sheet.Cells(3, 15) = line_2(14).ToUpper po_sheet.Cells(4, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper po_sheet.Cells(7, 15) = line_2(12).ToUpper ElseIf line_2(6) <> "" And line_2(7) = "" Then po_sheet.Cells(2, 15) = line_2(21).ToUpper po_sheet.Cells(3, 15) = line_2(14).ToUpper po_sheet.Cells(4, 15) = line_2(6).ToUpper po_sheet.Cells(5, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper po_sheet.Cells(7, 15) = line_2(12).ToUpper ElseIf line_2(6) = "" And line_2(7) <> "" Then po_sheet.Cells(2, 15) = line_2(21).ToUpper po_sheet.Cells(3, 15) = line_2(14).ToUpper po_sheet.Cells(4, 15) = line_2(7).ToUpper po_sheet.Cells(5, 15) = line_2(8).ToUpper & ", " & line_2(9).ToUpper & " " & line_2(10).ToUpper po_sheet.Cells(7, 15) = line_2(12).ToUpper End If '# cmd po_sheet.Cells(10, 1) = line_2(1).ToUpper 'Date cmd If line_1(1) = "E" Then po_sheet.Cells(10, 5) = line_2(2).Substring(3, 2) & "/" & line_2(2).Substring(6, 2) & "/" & line_2(2).Substring(0, 2) Else po_sheet.Cells(10, 5) = line_2(2).Substring(6, 2) & "/" & line_2(2).Substring(3, 2) & "/" & line_2(2).Substring(0, 2) End If 'Date livrais If line_1(1) = "E" Then po_sheet.Cells(10, 6) = line_2(3).Substring(3, 2) & "/" & line_2(3).Substring(6, 2) & "/" & line_2(3).Substring(0, 2) Else po_sheet.Cells(10, 6) = line_2(3).Substring(6, 2) & "/" & line_2(3).Substring(3, 2) & "/" & line_2(3).Substring(0, 2) End If 'Ship by po_sheet.Cells(10, 7) = line_2(17).ToUpper 'FOB po_sheet.Cells(10, 10) = line_2(13).ToUpper 'tx po_sheet.Cells(9, 16) = line_2(19).ToUpper po_sheet.Cells(10, 16) = line_2(20).ToUpper '# fact po_sheet.Cells(12, 1) = line_2(4).ToUpper 'Buyer po_sheet.Cells(12, 5) = line_2(12).ToUpper 'Terms po_sheet.Cells(12, 8) = line_2(5).ToUpper 'Freight po_sheet.Cells(12, 11) = line_2(11).ToUpper 'Création des lignes de PO For i = 4 To lines.GetUpperBound(0) - 1 line_x = Split(Replace(Replace(lines(i), Chr(34), ""), "'", " "), ";") qty = qty + line_x(1) 'line po_sheet.Cells(12 + i, 1) = line_x(0).ToUpper 'qte po_sheet.Cells(12 + i, 2) = line_x(1).ToUpper 'vendor-item If line_x(2) = "" Then po_sheet.Cells(12 + i, 4) = line_x(4).ToUpper Else po_sheet.Cells(12 + i, 4) = line_x(2).ToUpper End If 'desc po_sheet.Cells(12 + i, 6) = line_x(3).ToUpper 'item-number po_sheet.Cells(12 + i, 11) = "#PHV : " & line_x(4).ToUpper 'udm po_sheet.Cells(12 + i, 15) = line_x(5).ToUpper 'prix po_sheet.Cells(12 + i, 16) = CDec(line_x(6)) 'amount po_sheet.Cells(12 + i, 17) = CDec(line_x(7)) Next po_sheet.Cells(12 + i + 1, 4) = line_x(10).ToUpper & line_x(11).ToUpper & line_x(12).ToUpper & line_x(13).ToUpper & line_x(14).ToUpper & line_x(15).ToUpper & line_x(16).ToUpper & line_x(17).ToUpper & line_x(18).ToUpper & line_x(19).ToUpper & line_x(20).ToUpper & line_x(21).ToUpper & line_x(22).ToUpper & line_x(23).ToUpper po_sheet.Cells(12 + i + 3, 2) = "________" po_sheet.Cells(12 + i + 3, 17) = "___________" If line_1(1) = "E" Then If line_2(15) = "USD" Then po_sheet.Cells(12 + i + 3, 5) = "****** AMOUNTS SPECIFIED IN U.S.A CURRENCY ******" Else po_sheet.Cells(12 + i + 3, 5) = "******* AMOUNTS SPECIFIED IN CDN CURRENCY *******" End If Else If line_2(15) = "USD" Then po_sheet.Cells(12 + i + 3, 5) = "****** MONTANTS SPÉCIFIÉS EN DEVISE CDN ******" Else po_sheet.Cells(12 + i + 3, 5) = "****** MONTANTS SPÉCIFIÉS EN DEVISE USA ******" End If End If po_sheet.Cells(12 + i + 4, 2) = qty po_sheet.Cells(12 + i + 4, 16) = "TOTAL : " po_sheet.Cells(12 + i + 4, 17) = CDec(line_2(16)) po_xl.DisplayAlerts = False If line_0(0) = "@" Then If File.Exists(f_out & "ma_" & line_0(1) & ".xls") Then File.Delete(f_out & "ma_" & line_0(1) & ".xls") End If po_sheet.SaveAs(f_out & "ma_" & line_0(1) & ".xls") Else If File.Exists(f_out & "po_" & line_0(1) & ".xls") Then File.Delete(f_out & "po_" & line_0(1) & ".xls") End If po_sheet.SaveAs(f_out & "po_" & line_0(1) & ".xls") End If po_xl.Quit() po_sheet = Nothing po_book = Nothing po_xl = Nothing 'Kill du process excel Dim Processes As Process() = Nothing Processes = Process.GetProcessesByName("EXCEL") ' Load ID Processes in Array Dim intProcesses(Processes.GetUpperBound(0)) As Int16 Dim j As Int16 For j = 0 To Processes.GetUpperBound(0) Process.GetProcessById(CInt(Processes(j).Id.ToString)).Kill() Next 'Envoi du email si tel est le cas If line_0(0) = "@" Then 'fct_wait_sec(5000) fct_send_mail(envoyeur, line_1(0), "Purchase Order/Commande d'Achat # " & line_0(1), 1, "<font face=arial size=2 color='black'>Purchase Order/Commande d'Achat # " & line_0(1) & "</font><br><br><img src='" & signature & "'><br><br><b><font face=arial size=2 color='#aaaaaa'>DISCLAIMER: </b><BR>This communication is for use by the intended recipient and contains information that may be privileged, confidential or copyrighted under applicable law. If you are not the intended recipient, you are hereby formally notified that any use, copying or distribution of this e-mail, in whole or in part, is strictly prohibited. Please notify the sender by return e-mail from your system.</font>", "ma_" & line_0(1)) End If End If Catch ex As Exception fct_send_mail(envoyeur, warning, "ERREUR PO VIA MAIL " & line_0(1), 0, ex.ToString, "none") '# erreur no 8 fct_exec_sql("UPDATE pur_order SET err=8 WHERE pur_order.po_number='" & line_0(1) & "'")je suis desole si c'est long, mais je suis bloque depuis 3 jours :(
Merci beaucoup pour ton aide