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
110
111
112
113
114
115
116
117
118
119
120
|
Dim fso,of1
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1, ForWriting = 2, ForAppending = 8
conn.Open "PROVIDER=SQLOLEDB;DATA ***********"
'msgbox "OK"
chemin= Replace(WScript.ScriptFullName, WScript.ScriptName, "")
datedujour= day(now)& "-" & month(now) & "-"& year(now)
'msgbox(datedujour)
Set f = fso.OpenTextFile("jato.sql", ForReading)
une_variable = f.ReadAll
une_variable = Replace(une_variable, "datedebut", datedujour)
une_variable = Replace(une_variable, "datefin", datedujour)
f.Close
rs.Open une_variable, conn
If rs.EOF Then
Else
'--------------------------
if fso.FileExists("jato.csv") then
Set of1 = fso.GetFile("jato.csv")
of1.Move chemin &"archives\jato-dc-" & Replace( Left(of1.DateCreated,10),"/", "-") & "-dm-" & Replace( Left(Date(),10),"/", "-") & "-" & Replace( Left(Time(),10),":", "-") & ".csv"
End if
'----------------------------------
Set o = fso.OpenTextFile("jato.csv", ForWriting,true)
Do While NOT rs.Eof
o.write( rs("vin") &";"& rs("marque") &";"& rs("modele")& ";"& rs("version")& ";"& rs("code_version")& ";"& rs("carrosserie")& ";"& rs("Carburant")& ";"& rs("Porte")& ";"& rs("Date_Commande")& ";"& rs("date_entree_physique")& ";"& rs("datefacture")& ";" & ";"& rs("Libelles_Options")& ";"& rs("code_option")& ";"& rs("typevente")& ";"& rs("Societe")& ";"& rs("Mt_financement")& ";"&rs("Mt_reprise")& ";"&rs("marque_veh_vo")& ";"&rs("modele_veh_vo")& ";"& rs("Prix_vente_depart")& ";"&rs("Option2")& ";"& rs("transfertdemargevo")& ";"&rs("totalfactureclient")& vbCrLf )
rs.MoveNext
Loop
o.Close
Const xlCVS = 6
srcxlsfile = chemin &"jato.csv"
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open (srcxlsfile,,,,,,,,,,,,,true)
objExcel.DisplayAlerts = FALSE
objExcel.Visible = TRUE
REM objExcel.Application.ScreenUpdating = False
REM objExcel.Sheets("jato").select
REM LastLig = objExcel.Cells(objExcel.Rows.Count, "A").End(3).Row
REM For i = LastLig To 2 Step -1
REM If objExcel.Range("A" & i) = objExcel.Range("A" & i - 1) Then
REM objExcel.Range("M" & i - 1).Value = objExcel.Range("M" & i - 1).Value & "| " & objExcel.Range("M" & i).Value
REM objExcel.Range("N" & i - 1).Value = objExcel.Range("N" & i - 1).Value & "| " & objExcel.Range("N" & i).Value
REM objExcel.Rows(i).Delete
REM End If
REM Next
objExcel.Application.ScreenUpdating = True
objExcel.ActiveWorkbook.SaveAs srcxlsfile, xlCVS,False,True
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
REM Dim oFso, f
REM dim test
REM Set oFso = CreateObject("Scripting.FileSystemObject")
REM Set f = oFso.OpenTextFile(chemin & "jato.csv", ForReading)
REM test= f.ReadAll
REM nb= f.Line
REM f.Close
REM test = replace(test, ",",";")
REM Set f2 = oFso.OpenTextFile(chemin & "jato.csv", ForWriting,true)
REM f2.write(test)
REM f2.Close
REM Dim shell
REM Set shell = CreateObject("WScript.Shell")
REM shell.Run "ftp -n -s:""" & chemin & "ftp.txt""", 0
End If
rs.Close
Set rs=nothing
REM Public Sub SendMail(Expediteur,Destinataire,Sujet,Message,Fichier,Smtp,Port)
REM Set Mail = CreateObject("CDO.Message")
REM With Mail
REM .From=Expediteur
REM .To=Destinataire
REM .Subject=Sujet
REM .CreateMHTMLBody "file://" & Message
REM .AddAttachment Fichier
REM .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
REM .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = Smtp
REM .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = Port
REM .Configuration.Fields.Update
REM .Send
REM End With
REM End Sub
REM Message = chemin & "message_html\message.html"
REM SendMail "No_reply@hhhhh","fvvvvv@ahhhh","Actualisation du fichier jato le " & Replace( Left(Date,10),"/", "-") & " à: " & Time,Message, chemin &"jato.csv","**********","25" |
