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
121
122
123
124
125
126
127
128
129
130
131
| 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 SOURCE=10.11.7.73;UID=a*et;PWD=**t;DATABASE=ica*ms"
'msgbox "OK"
chemin= Replace(WScript.ScriptFullName, WScript.ScriptName, "")
datedujour= day(now)& "-" & month(now) & "-"& year(now)
'msgbox(datedujour)
Set f = fso.OpenTextFile("jatov2.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-"&datedujour & ".csv") then
Set of1 = fso.GetFile("jato-"&datedujour & ".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-"&datedujour & ".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("typevehicule")& ";"& rs("type_client")& ";"& rs("Mt_financement")& ";"&rs("Mt_reprise")& ";"&rs("marque_veh_vo")& ";"&rs("modele_veh_vo")& ";"& rs("Prix_vente_depart_HT")& ";"&rs("Option2_HT")& ";"& rs("Remise_HT")& "; "& rs("transfertdemargevo")& ";"&rs("totalfactureclient_HT")& vbCrLf )
rs.MoveNext
Loop
o.Close
Const xlCVS = 6
srcxlsfile = chemin & "jato-" & datedujour & ".csv"
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open (srcxlsfile,,,,,,,,,,,,,true)
REM objExcel.DisplayAlerts = FALSE
REM objExcel.Visible = TRUE
objExcel.Application.ScreenUpdating = False
objExcel.Sheets("jato-"&datedujour).select
LastLig = objExcel.Cells(objExcel.Rows.Count, "A").End(3).Row
For i = LastLig To 2 Step -1
If objExcel.Range("A" & i) = objExcel.Range("A" & i - 1) Then
objExcel.Range("M" & i - 1).Value = objExcel.Range("M" & i - 1).Value & "| " & objExcel.Range("M" & i).Value
objExcel.Range("L" & i - 1).Value = objExcel.Range("L" & i - 1).Value & "| " & objExcel.Range("L" & i).Value
objExcel.Rows(i).Delete
End If
Next
objExcel.Application.ScreenUpdating = True
objExcel.ActiveWorkbook.SaveAs srcxlsfile, xlCVS,False,True
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
Dim oFso, f
dim test
Set oFso = CreateObject("Scripting.FileSystemObject")
Set f = oFso.OpenTextFile(chemin & "jato-"&datedujour & ".csv", ForReading)
test= f.ReadAll
nb= f.Line
f.Close
test = replace(test, ",",";")
Set f2 = oFso.OpenTextFile(chemin & "jato-"&datedujour & ".csv", ForWriting,true)
f2.write(test)
f2.Close
'----------------------
REM Const ForWriting2 = 2
REM Dim c
REM Set c = CreateObject("Scripting.FileSystemObject")
REM Set cr = c.OpenTextFile(chemin & "ftp.txt", ForWriting2,true)
REM cr.write("open 1*****" & vbCrLf)
REM cr.write("USER fr.*** de***" & vbCrLf)
REM cr.write("CD D***r1" & vbCrLf)
REM cr.write("PUT jato-" &datedujour & ".csv" &vbCrLf)
REM cr.write("quit" & vbCrLf)
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
Public Sub SendMail(Expediteur,Destinataire,Sujet,Message,Fichier,Smtp,Port)
Set Mail = CreateObject("CDO.Message")
With Mail
.From=Expediteur
.To=Destinataire
.Subject=Sujet
.CreateMHTMLBody "file://" & Message
.AddAttachment Fichier
.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = Smtp
.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = Port
.Configuration.Fields.Update
.Send
End With
End Sub
Message = chemin & "message_html\message.html"
SendMail "No_reply@a","fot@a","Actualisation du fichier jato le " & Replace( Left(Date,10),"/", "-") & " à: " & Time,Message, chemin &"jato-"&datedujour & ".csv","smtp.fr.oleane.com","25" |
