XL 2010 VBA Excel Donnée en cache

[azertyyyy]

Bonjour à tous,


Je dispose d'un fichier Excel qui dispose de 4 macros:

1- Se connecter à une base SQLSERVER et récupéré les données et les insert dans une feuille excel (rapport):

2- Celle-ci execute plusieurs requête SQL dans la feuille Excel rapport et donne le resultat dans une autre feuille.

3- creation PDF
4-Envoi de mail.

J'ai un problème avec la macro N*2. Comme indiqué ci dessus la macro N*2 réalise des requête SQL sur la feuille rapport (qui à été réalisé par la 1er macro)

Le problème si je ré exécute la macro 1 (donc une 2ème fois) avec des résultats différents et que je réexecute la macro n*2 on dirait qu'il prend pas les (nouvelles) données présentent dans la feuille rapport mais des données en cache car le resultat sont les données présent lors de la 1ere éxecution.


J'ai fait un test, j'ai complétement vidé le contenu de la feuille "rapport" et j'ai ré-execute directement la macro N*2. Normalement cela devrait me retourner RIEN et bien non il me ressort les anciens résultat.

Comment faire pour "vider" se cache de données présent dans ce fichier excel.


merci d'avance pour votre aide et vos explications

azertyyy

 

[azertyyyy]

Voici la 1ere vba:

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

 

[azertyyyy]

Voici la 2ème macro:

Sub rapportactivite()
'----------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Departement, Worker, SUM(spenttime) as TOTAL FROM ( [Rapport-activite$]) "
texte_SQL = texte_SQL + " WHERE id_rubric  NOT IN ('25','61','63','72','73','76') and trav_inperiod='YES' "
texte_SQL = texte_SQL + " GROUP BY Departement, Worker"
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[E3].CopyFromRecordset Rst
'Entêtes de colonne
Dim liCount As Integer
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
'------------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Departement,Worker, SUM(invtime)as TOTAL ,SUM(net) as TOTAL2 FROM ( [Rapport-activite$]) "
texte_SQL = texte_SQL + " WHERE id_rubric IN ('??','01','02','04','05','06','09','27','31','32','33','34','38','41') and doc_inperiod='YES' "
texte_SQL = texte_SQL + " GROUP BY Departement, Worker"
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[A3].CopyFromRecordset Rst
'Entêtes de colonne
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
'---------------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Departement,SUM(cost) as TOTAL FROM ( [Rapport-activite$]) "
texte_SQL = texte_SQL + " WHERE trav_inperiod='YES' "
texte_SQL = texte_SQL + " GROUP BY Departement"
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[H3].CopyFromRecordset Rst
'Entêtes de colonne
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
'---------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Service, count(tt) as total FROM (SELECT DISTINCT dossier as tt, Service FROM ( [APV-Vente-MO$]) "
texte_SQL = texte_SQL + " WHERE type IN ('0 -Client','3 -Assurance')) as  requete1 "
texte_SQL = texte_SQL + " GROUP BY Service "
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[K3].CopyFromRecordset Rst
'Entêtes de colonne
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
'---------------------------------------------
'-----------------------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Departement, Worker, SUM(invtime) as TOTAL, Type FROM ( [Rapport-activite$]) "
texte_SQL = texte_SQL + " WHERE id_rubric IN ('??','01','02','04','05','06','09','27','31','32','33','34','38','41') and trav_inperiod='YES' "
texte_SQL = texte_SQL + " GROUP BY Departement, Worker, Type"
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[o3].CopyFromRecordset Rst
'Entêtes de colonne
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
'-------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Departement,Worker, SUM(spenttime)as TOTAL FROM ( [Rapport-activite$]) "
texte_SQL = texte_SQL + " WHERE trav_inperiod='YES' AND id_rubric NOT IN ('63') "
texte_SQL = texte_SQL + " GROUP BY Departement, Worker"
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[U3].CopyFromRecordset Rst
'Entêtes de colonne
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
'---------------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Departement,Worker, SUM(spenttime)as TOTAL ,SUM(net) as TOTAL2 FROM ( [Rapport-activite$]) "
texte_SQL = texte_SQL + " WHERE id_rubric IN ('??','01','02','04','05','06','09','27','31','32','33','34','38','41') and trav_inperiod='YES' "
texte_SQL = texte_SQL + " GROUP BY Departement, Worker"
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[AA3].CopyFromRecordset Rst
'Entêtes de colonne
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
'--------------------------------------
'Définit le classeur servant de base de données
Fichier = ActiveWorkbook.FullName
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
End With
'... la requête ...
texte_SQL = "SELECT Departement,type, SUM(invtime)as TOTAL ,SUM(net) as TOTAL2 FROM ( [Rapport-activite$]) "
texte_SQL = texte_SQL + " WHERE id_rubric IN ('??','01','02','04','05','06','09','27','31','32','33','34','38','41') and doc_inperiod='YES' "
texte_SQL = texte_SQL + " GROUP BY Departement, type"
Set Rst = Cn.Execute(texte_SQL)
'Copy des données
Sheets("RESULT").Select
Sheets("RESULT").[AF3].CopyFromRecordset Rst
'Entêtes de colonne
For liCount = 0 To Rst.Fields.Count - 1
    Sheets("RESULT").Cells(1, liCount + 1) = Rst.Fields(liCount).Name
Next
'--- Fermeture connexion ---
Rst.Close
Cn.Close
Set Cn = Nothing
End Sub