IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Somme des champs de plusieurs autres classeurs


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé Avatar de Dennis Nedry
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    78
    Détails du profil
    Informations personnelles :
    Localisation : France, Nord (Nord Pas de Calais)

    Informations forums :
    Inscription : Septembre 2007
    Messages : 78
    Par défaut
    Bonjour,

    J'ai actuellement un fichier excel qui est généré chaque jour. Je souhaite avoir à présent un récapitulatif de toutes les actions effectuées, en effectuant la somme mensuelle du CA quotidien.

    Mon fichier est de la forme:
    C:\Dossier\fichier du 20080515.xls
    avec l'année, le mois, et le jour de création du fichier.

    Supposons que le CA est dans le champ A1 de chaque feuille 1.

    Récupérer le CA du jour sera donc de la forme:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ='C:\Dossier\[fichier du 20080515.XLS]Feuille 1'!$A$1
    jusque là, tout va bien. mais c'est maintenant que ça se complique.

    j'ai créé un champ année et mois dans ma table de destination. (respectivement B1 et B2)

    La formule pour récupérer la valeur du champ devrait donc être:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ='CONCATENER("C:\Dossier\[fichier du ";B1;B2;"30.XLS]Feuille 1")'!$A$1
    Mais ça ne fonctionne pas...

    De la même manière, le calcul mensuel pourrait donc s'assimiler à un calcul du type:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME('CONCATENER("C:\Dossier\[fichier du ";B1;B2;"*.xls]Feuille 1")'!$A$1)
    Mais là, je sèche complètement...

    Le problême vient de la concaténation, qui me retourne le nom du champ comme une valeur...

    Pour faire simple, il me suffirait d'une commande appelant la valeur d'un champ et le définissant comme un nom de champ lui-même.

    Exemple: la cellule B2 contient comme valeur A4.
    La cellule A4 a comme valeur toto

    je souhaiterais donc pouvoir trouver une formule permetant de m'appeler la valeur du champ défini dans le champ que j'appelle, comme:
    Comme ça, il voit que B2 vaut "A4", donc il va voir dans la cellule A4 et affiche "toto"

    Un peu barbare, mais ça devrait marcher.

  2. #2
    Expert confirmé

    Homme Profil pro
    Inscrit en
    Août 2005
    Messages
    3 317
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Secteur : Industrie

    Informations forums :
    Inscription : Août 2005
    Messages : 3 317
    Par défaut
    bonsoir

    Tu devrais utiliser la fonction INDIRECT pour concaténer le chemin complet du classeur. malheureusement cette solution nécessite que le classeur source soit ouvert.

    une solution paliative par VBA :
    Comment utiliser la fonction Indirect pour lire une cellule dans un classeur fermé ?



    bonne soirée
    michel

  3. #3
    Membre confirmé Avatar de Dennis Nedry
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    78
    Détails du profil
    Informations personnelles :
    Localisation : France, Nord (Nord Pas de Calais)

    Informations forums :
    Inscription : Septembre 2007
    Messages : 78
    Par défaut
    okay, merci du conseil. Mais je pense que je ferais mieux d'utiliser directement du VBA...

    L'idée qu'on m'a conseillé serait de venir récupérer toutes les valeurs au lancement d'Access, pour venir les remplir dans un tableau (qui sera en lecture seule, puisque je viendrais remplir les champs). j'ai commencé le soft, mais certains détails restent encore à effectuer:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Private Sub Workbook_Open()
     
     
     
        Application.DisplayAlerts = False
        myFile = ActiveWorkbook.Name
        myPath = "C:\Dossier\Fiche du "
        'nom du repertoire modifié (secret professionnel oblige ^^)
        'le nom du fichier est de la forme "fiche du YYYYMMDD.xls"
        '56 caracteres pour le chemin du fichier avant d'arriver aux infos Year et Month.
        '57-60:year
        '61-62:month
     
        Application.StatusBar = True
     
          workfile = Dir(myPath & "*.xls")
    'il faudra ordonner par nom. (reste à faire)
     
     
    'definition des valeurs min et max de la date. la valeur min est celle du premier enregistrement, la max celle du dernier.
     
    minyear = Mid(workfile, 57, 4)
    minmonth = Mid(workfile, 61, 2)
            Do While workfile <> ""
                Application.StatusBar = "Traitement de " & workfile
                maxyear = Mid(workfile, 57, 4)
                maxmonth = Mid(workfile, 61, 2)
                workfile = Dir()
            Loop
     
     
     
    'premiere annee: le mois de depart est le mois min.
    'derniere annee: on vient modifier le mois max
    jan = minmonth
    dec = 12
    For cyear = minyear To maxyear
        If cyear = maxyear Then
        dec = maxmonth
        End If
     
        Var1 = 0
        Var2 = 0
        Var3 = 0
     
        For cmonth = jan To dec
     
            'on ajoute un zéro à l'identifiant si le mois est < à 10 (pour avoir l'identifiant Mois sur 2 chiffres)
            If cmonth < 10 Then
                workfile = Dir(myPath & cyear & "0" & cmonth & "*.xls")
            Else
                workfile = Dir(myPath & cyear & cmonth & "*.xls")
            End If
     
     
            Do While workfile <> ""
                Application.StatusBar = "Traitement de " & workfile
                MsgBox ("cyear = " & cyear & ", cmonth = " & cmonth)
    '            Var1 = Var1 + Fichier.champ
    '            Var2 = Var2 + Fichier.champ
    '            Var3 = Var3 + Fichier.champ
                workfile = Dir()
            Loop
     
     
     
     
        'affichage des donnees dans le tableau
    Champ1=Val1
    Champ2=Val2
    Champ3=Val3
       'on viendra incrémenter par la suite les champs, pour pouvoir faire un beau tableau. A1 > B1, A2>B2, etc.
     
        Next cmonth
     
            'on supprimme la notion de minmonth en mettant le mois min à 1 
        jan = 1
     
    Next cyear
     
    'fermeture de la progress bar
    Application.StatusBar = False
     
    End Sub
    (bourré de fautes, et pas fini)

    Le principe est simple: on balaye une première fois l'ensemble du répertoire pour récupérer les valeurs extrêmes: première année premier mois, dernière année dernier mois.

    Ensuite, on génère deux boucles imbriquées, pour balayer mois par mois et année par année. son but est de personnaliser la boucle de balayage des fichiers, pour venir récupérer les données au mois.

    Je sais que mon code n'est pas fini, est bourré de fautes, etc, mais le but n'est pas d'avoir une correction automatique faite par un membre, juste de savoir si l'architecture globale de la fonction est correcte et peut aboutir à un résultat à la hauteur de mes espérances.


    [EDIT]
    Au final, voici ce que je souhaiterais obtenir:



    tous les champs (excepté la première colonne) sont automatiquement remplis par la récupération des données. chaque mois génère une colonne qui lui est propre.

  4. #4
    Membre confirmé Avatar de Dennis Nedry
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    78
    Détails du profil
    Informations personnelles :
    Localisation : France, Nord (Nord Pas de Calais)

    Informations forums :
    Inscription : Septembre 2007
    Messages : 78
    Par défaut
    j'y suis presque!

    il me faut simplement trouver un moyen (qui fonctionne, de préférence) pour récupérer un champ dans un classeur fermé et tout sera parfait!


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Private Sub Workbook_Open()
     
     
        Application.DisplayAlerts = False
        myPath = "C:\Dossier\"
        myFile = ActiveWorkbook.Name
        'le nom du fichier est de la forme "fiche du YYYYMMDD"
        '27 caracteres pour le chemin du fichier avant d'arriver aux infos Year et Month.
        '28-31:year
        '32-33:month
     
     
        Application.StatusBar = True
     
     
                    'definition des valeurs min et max de la date.
                    'la valeur min est celle du premier enregistrement, la max celle du dernier.
                        workfile = Dir(mypath & "*.xls")
                        minyear = Mid(workfile, 28, 4)
                        minmonth = Mid(workfile, 32, 2)
                        Do While workfile <> ""
                            Application.StatusBar = "Traitement de " & workfile
                            maxyear = Mid(workfile, 28, 4)
                            maxmonth = Mid(workfile, 32, 2)
                            workfile = Dir()
                        Loop
     
    'on détermine la première colonne à partir de laquelle il faudra remplir le tableau.
    xpos = 2
     
     
    'premiere annee: le mois de depart est le mois min.
    'derniere annee: on vient modifier le mois max
    jan = minmonth
    dec = 12
    For cyear = minyear To maxyear
        If cyear Like maxyear Then
        dec = maxmonth
        End If
        For cmonth = jan To dec
     
    'RAZ des valeurs mensuelles
        var1 = 0
        var2 = 0
        var3 = 0
     
            'on ajoute un zéro à l'identifiant si le mois est < à 10 (pour avoir l'identifiant Mois sur 2 chiffres)
            If cmonth < 10 Then
                workfile = Dir(mypath & "Fiche du " & cyear & "0" & cmonth & "*.xls")
            Else
                workfile = Dir(mypath & "Fiche du " & cyear & cmonth & "*.xls")
            End If
            MsgBox (workfile)
                'MsgBox (mypath & workfile & " - cyear = " & cyear & ", cmonth = " & cmonth)
     
     
            Do While workfile <> ""
                Application.StatusBar = "Traitement de " & workfile
     
     
                var1 = var1 + Extraction("$F$14")
                var2 = var2 + Extraction("$D$14")
                var3 = var3 + Extraction("$E$14")
     
                workfile = Dir()
            Loop
     
     
            '############ Enregistrement des valeurs dans la feuille Excel
                Cells(1, xpos) = cyear
                Cells(2, xpos) = cmonth
                Cells(3, xpos) = var1
                Cells(4, xpos) = var2
                Cells(5, xpos) = var3
                xpos = xpos + 1
     
        Next cmonth
       '(suppression des effets de minmonth (mois de départ première année)
       jan = 1
    Next cyear
     
    'fermeture de la progress bar
    Application.StatusBar = False
    End Sub
     
     
     
    Function Extraction(Champ As Variant) As Variant
     
        Application.Volatile
     
        Dim Source As ADODB.Connection
        Dim Rst As ADODB.Recordset
        Dim ADOCommand As ADODB.Command
        Dim Fichier As String, Cellule As String, Feuille As String
     
        'Adresse de la cellule contenant la donnée à récupérer
        'Cellule = "B4:B4"
          'Pour une plage de cellules, utilisez:
          'Cellule = "A4:C10"
     
        Feuille = "Feuil1$" 'n'oubliez pas d'ajouter $ au nom de la feuille.
        'Chemin complet du classeur fermé
        Fichier = mypath & workfile
     
        Set Source = New ADODB.Connection
        Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Fichier & ";Extended Properties=""Excel 8.0;HDR=No;"";"
     
        Set ADOCommand = New ADODB.Command
        With ADOCommand
            .ActiveConnection = Source
            .CommandText = "SELECT * FROM [" & Feuille & Cellule & "]"
        End With
     
        Set Rst = New ADODB.Recordset
        Rst.Open ADOCommand, , adOpenKeyset, adLockOptimistic
     
        Set Rst = Source.Execute("[" & Feuille & Cellule & "]")
     
        Extraction = Rst(0).Value
     
     
        Rst.Close
        Source.Close
        Set Source = Nothing
        Set Rst = Nothing
        Set ADOCommand = Nothing
    End Function
    Je vous l'accorde, c'est long, mais il ne reste plus qu'un seul bug!

    lorsque je lance mon programme, il me met un message:
    Citation Envoyé par Microsoft Visual Basic
    Erreur d'exécution '-2147467259 (80004005)':

    Erreur Automation
    Erreur non spécifiée
    J'ai donc mis une msgbox à chaque ligne de la fonction pour vérifier l'endroit où cela planterait. la réponse est sans équivoque:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
        Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Fichier & ";Extended Properties=""Excel 8.0;HDR=No;"";"
    Donc, il devrait y avoir un problême au niveau des références? j'ai pourtant bien activé les librairies Microsoft ActiveX Data Objects 2.X Library, mais rien n'a changé...

    J'avoue être un peu perdu. J'avais déjà tenté d'utiliser la méthode suggérée ci-dessus, mais sans succès...



    [EDIT]

    J'ai avancé, et j'ai su résoudre le premier problême (variable pas correctement déclarée).

    Maintenant, c'est là qu'il bloque:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
      Set Rst = New ADODB.Recordset
        Rst.Open ADOCommand, , adOpenKeyset, adLockOptimistic
     
        Set Rst = Source.Execute("[" & Feuille & Cellule & "]")
        Extraction = Rst(0).Value
    plus précisément à la deuxième ligne. le rst.open me génère un Erreur Automation.

  5. #5
    Expert confirmé

    Homme Profil pro
    Inscrit en
    Août 2005
    Messages
    3 317
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Secteur : Industrie

    Informations forums :
    Inscription : Août 2005
    Messages : 3 317
    Par défaut
    bonjour

    Comment as tu définit la variable ADOcommand ?


    Un exemple qui récupère le contenu d'une cellule dans un classeur fermé :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Sub extractionValeurCelluleClasseurFerme()
        Dim Source As ADODB.Connection
        Dim Rst As ADODB.Recordset
        Dim ADOCommand As ADODB.Command
        Dim Fichier As String, Cellule As String, Feuille As String
     
        'Adresse de la cellule contenant la donnée à récupérer
        Cellule = "B4:B4"
          'Pour une plage de cellules, utilisez:
          'Cellule = "A4:C10"
     
        Feuille = "Feuil1$" 'n'oubliez pas d'ajouter $ au nom de la feuille.
        'Chemin complet du classeur fermé
        Fichier = "C:\Base.xls"
     
        Set Source = New ADODB.Connection
        Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Fichier & ";Extended Properties=""Excel 8.0;HDR=No;"";"
     
        Set ADOCommand = New ADODB.Command
        With ADOCommand
            .ActiveConnection = Source
            .CommandText = "SELECT * FROM [" & Feuille & Cellule & "]"
        End With
     
        Set Rst = New ADODB.Recordset
        Rst.Open ADOCommand, , adOpenKeyset, adLockOptimistic
     
        Set Rst = Source.Execute("[" & Feuille & Cellule & "]")
     
        Range("A2").CopyFromRecordset Rst
     
        Rst.Close
        Source.Close
        Set Source = Nothing
        Set Rst = Nothing
        Set ADOCommand = Nothing
    End Sub


    bonne journée
    michel

  6. #6
    Membre confirmé Avatar de Dennis Nedry
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    78
    Détails du profil
    Informations personnelles :
    Localisation : France, Nord (Nord Pas de Calais)

    Informations forums :
    Inscription : Septembre 2007
    Messages : 78
    Par défaut
    Bon, finalement j'ai pu contourner le problême autrement. j'ouvre le classeur, je récupère la valeur, je referme le classeur. c'est un peu moins joli, vu que l'écran se met à clignoter le temps qu'il récupère tout, mais ça fonctionne. Accessoirement, ça peut aussi provoquer des crises d'éplilepsie...

    Sinon, merci quand même pour tes suggestions. bien que n'ayant pas réussi à les mettre en oeuvre, je vais tout de même me pencher dessus pour une éventuelle version 2 de mon classeur...

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-2003] Formule Somme.si allant chercher des données dans un autre classeur
    Par spoutnikk dans le forum Excel
    Réponses: 4
    Dernier message: 24/04/2010, 18h56
  2. rave report (somme des champs par pages)
    Par mozcity dans le forum Composants VCL
    Réponses: 3
    Dernier message: 17/03/2006, 21h51
  3. somme des champs null
    Par s.rais dans le forum Access
    Réponses: 4
    Dernier message: 09/02/2006, 09h05
  4. Réponses: 2
    Dernier message: 09/01/2006, 16h10
  5. Somme des champs ? existe t il une fonction ...
    Par dark_vidor dans le forum Langage SQL
    Réponses: 6
    Dernier message: 02/01/2006, 11h57

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo