Sub Ecars21()
Application.Calculation = xlManual
Dim valce1 As String
Dim valce2 As String
Dim utilisateur As String
valce1 = Excel.Range("RECAP!F3").Value
valce2 = CDate(Excel.Range("RECAP!H3").Value) + 1
valce1n = DateAdd("m", -12, valce1)
valce2n = DateAdd("m", -12, valce2)
'valce3 = Excel.Range("Informations!B5").Value
'a = Split(valce3, "|")
'id_societe = a(0)
'id_atelier = a(1)
'MsgBox (">>>>>" & valce1 & "<<<<>>>>" & valce2 & "<<<<>>>>" & valce1n & "<<<<" & valce2n & "<<<<")
chemin = ActiveWorkbook.Path
Set fso = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1, ForWriting = 2
Worksheets("magasin").Range("A2:N3000").ClearContents
Worksheets("vente-vo").Range("A2:N3000").ClearContents
Worksheets("vente-vn").Range("A2:N3000").ClearContents
Worksheets("rapport-activite").Range("A2:ao15000").ClearContents
Worksheets("nb-or").Range("A2:ao3000").ClearContents
Worksheets("apv-vente-mo").Range("A2:N3000").ClearContents
Worksheets("RESULT").Range("a3:aj500").ClearContents
'------------
Dim cnBat2 As ADODB.Connection
Set cnBat2 = New ADODB.Connection
Dim strConn2 As String
strConn2 = "PROVIDER=SQLOLEDB;"
strConn2 = strConn2 & "DATA SOURCE=;UID=usr-;PWD=;DATABASE=Cars108783"
cnBat2.Open strConn2
cnBat2.CommandTimeout = 380
valce3 = Format(Excel.Range("RECAP!F3").Value, "YYYYMMDD")
valce4 = Format(CDate(Excel.Range("RECAP!H3").Value) + 1, "YYYYMMDD")
valce3n = Format(DateAdd("m", -12, Excel.Range("RECAP!F3").Value), "YYYYMMDD")
valce4n = Format(DateAdd("m", -12, CDate(Excel.Range("RECAP!H3").Value) + 1), "YYYYMMDD")
'MsgBox (">>>>" & valce1 & ">>>>" & valce2 & ">>>>" & valce1n & ">>>" & valce2n)
Set f21 = fso.OpenTextFile(chemin & "\Sql\vente-pr-cars21.sql", ForReading)
une_variable21 = Replace(f21.ReadAll, "datedebut", valce3)
une_variable21 = Replace(une_variable21, "datefin", valce4)
une_variable21 = Replace(une_variable21, "dbn1", valce3n)
une_variable21 = Replace(une_variable21, "dfn1", valce4n)
' une_variable = Replace(une_variable, "societe", rsBat0("emp"))
' une_variable = Replace(une_variable, "atelier", rsBat0("taller"))
f21.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & "vente-pr-cars21.txt", 2, True)
' f1.write (une_variable21)
' f1.Close
' MsgBox (une_variable)
Dim rsBat21 As ADODB.Recordset
Set rsBat21 = New ADODB.Recordset
With rsBat21
.ActiveConnection = cnBat2
.Open une_variable21
DerniereLigne = Worksheets("magasin").Range("A3000").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("magasin").Range("A" & DerniereLigne).CopyFromRecordset rsBat21
.Close
End With
'---------
'MsgBox (">>>>" & valce1 & ">>>>" & valce2 & ">>>>" & valce1n & ">>>" & valce2n)
Set f22 = fso.OpenTextFile(chemin & "\Sql\vente-vn-cars21.sql", ForReading)
une_variable22 = Replace(f22.ReadAll, "datedebut", valce1)
une_variable22 = Replace(une_variable22, "datefin", valce2)
'une_variable21 = Replace(une_variable21, "dbn1", valce1n)
'une_variable21 = Replace(une_variable21, "dfn1", valce2n)
' une_variable = Replace(une_variable, "societe", rsBat0("emp"))
' une_variable = Replace(une_variable, "atelier", rsBat0("taller"))
f22.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & "vente-pr-cars21.txt", 2, True)
' f1.write (une_variable21)
' f1.Close
' MsgBox (une_variable)
Dim rsBat22 As ADODB.Recordset
Set rsBat22 = New ADODB.Recordset
With rsBat22
.ActiveConnection = cnBat2
.Open une_variable22
DerniereLigne = Worksheets("vente-vn").Range("A3000").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("vente-vn").Range("A" & DerniereLigne).CopyFromRecordset rsBat22
.Close
End With
'---------
'MsgBox (">>>>" & valce1 & ">>>>" & valce2 & ">>>>" & valce1n & ">>>" & valce2n)
Set f23 = fso.OpenTextFile(chemin & "\Sql\vente-vo-cars21.sql", ForReading)
une_variable23 = Replace(f23.ReadAll, "datedebut", valce1)
une_variable23 = Replace(une_variable23, "datefin", valce2)
'une_variable21 = Replace(une_variable21, "dbn1", valce1n)
'une_variable21 = Replace(une_variable21, "dfn1", valce2n)
' une_variable = Replace(une_variable, "societe", rsBat0("emp"))
' une_variable = Replace(une_variable, "atelier", rsBat0("taller"))
f23.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & "vente-pr-cars21.txt", 2, True)
' f1.write (une_variable21)
' f1.Close
' MsgBox (une_variable)
Dim rsBat23 As ADODB.Recordset
Set rsBat23 = New ADODB.Recordset
With rsBat23
.ActiveConnection = cnBat2
.Open une_variable23
DerniereLigne = Worksheets("vente-vo").Range("A3000").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("vente-vo").Range("A" & DerniereLigne).CopyFromRecordset rsBat23
.Close
End With
'-------------------------
'---------
'MsgBox (">>>>" & valce1 & ">>>>" & valce2 & ">>>>" & valce1n & ">>>" & valce2n)
Set f24 = fso.OpenTextFile(chemin & "\Sql\tps-cars21.sql", ForReading)
une_variable24 = Replace(f24.ReadAll, "datedebut", valce1)
une_variable24 = Replace(une_variable24, "datefin", valce2)
'une_variable21 = Replace(une_variable21, "dbn1", valce1n)
'une_variable21 = Replace(une_variable21, "dfn1", valce2n)
' une_variable = Replace(une_variable, "societe", rsBat0("emp"))
' une_variable = Replace(une_variable, "atelier", rsBat0("taller"))
f24.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & "vente-pr-cars21.txt", 2, True)
' f1.write (une_variable21)
' f1.Close
' MsgBox (une_variable)
Dim rsBat24 As ADODB.Recordset
Set rsBat24 = New ADODB.Recordset
With rsBat24
.ActiveConnection = cnBat2
.Open une_variable24
DerniereLigne = Worksheets("Rapport-activite").Range("A3000").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("Rapport-activite").Range("A" & DerniereLigne).CopyFromRecordset rsBat24
.Close
End With
'MsgBox (">>>>" & valce1 & ">>>>" & valce2 & ">>>>" & valce1n & ">>>" & valce2n)
Set f26 = fso.OpenTextFile(chemin & "\Sql\nb-or.sql", ForReading)
une_variable26 = Replace(f26.ReadAll, "datedebut", valce1)
une_variable26 = Replace(une_variable26, "datefin", valce2)
une_variable26 = Replace(une_variable26, "societe", "1")
f26.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & "vente-pr-cars21.txt", 2, True)
' f1.write (une_variable21)
' f1.Close
' MsgBox (une_variable)
Dim rsBat26 As ADODB.Recordset
Set rsBat26 = New ADODB.Recordset
With rsBat26
.ActiveConnection = cnBat2
.Open une_variable26
DerniereLigne = Worksheets("nb-or").Range("A30").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("nb-or").Range("A" & DerniereLigne).CopyFromRecordset rsBat26
.Close
End With
'-------------------------
'---------
'
'MsgBox (">>>>" & valce1 & ">>>>" & valce2 & ">>>>" & valce1n & ">>>" & valce2n)
Set f27 = fso.OpenTextFile(chemin & "\Sql\nb-or.sql", ForReading)
une_variable27 = Replace(f27.ReadAll, "datedebut", valce1)
une_variable27 = Replace(une_variable27, "datefin", valce2)
une_variable27 = Replace(une_variable27, "societe", "2")
f27.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & "vente-pr-cars21.txt", 2, True)
' f1.write (une_variable21)
' f1.Close
' MsgBox (une_variable)
Dim rsBat27 As ADODB.Recordset
Set rsBat27 = New ADODB.Recordset
With rsBat27
.ActiveConnection = cnBat2
.Open une_variable27
DerniereLigne = Worksheets("nb-or").Range("A30").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("nb-or").Range("A" & DerniereLigne).CopyFromRecordset rsBat27
.Close
End With
'-------------------------
'---------
'
'MsgBox (">>>>" & valce1 & ">>>>" & valce2 & ">>>>" & valce1n & ">>>" & valce2n)
Set f28 = fso.OpenTextFile(chemin & "\Sql\nb-or.sql", ForReading)
une_variable28 = Replace(f28.ReadAll, "datedebut", valce1)
une_variable28 = Replace(une_variable28, "datefin", valce2)
une_variable28 = Replace(une_variable28, "societe", "3")
f28.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & "vente-pr-cars21.txt", 2, True)
' f1.write (une_variable21)
' f1.Close
' MsgBox (une_variable)
Dim rsBat28 As ADODB.Recordset
Set rsBat28 = New ADODB.Recordset
With rsBat28
.ActiveConnection = cnBat2
.Open une_variable28
DerniereLigne = Worksheets("nb-or").Range("A30").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("nb-or").Range("A" & DerniereLigne).CopyFromRecordset rsBat28
.Close
End With
'-------------------------
cnBat2.Close
Set rsBat2 = Nothing
Set cnBat2 = Nothing
Application.Calculate
Application.Calculation = xlAutomatic
End Sub