VBA - lier 2 fichiers

Bonjour, je suis un peu novice sur Excel et surtout sur VBA.

Je travaille actuellement sur la mise en place de l'automatisation de la collecte d'informations provenant de différents fichiers Excel pour faire un push-mail quotidien.

J'ai entendu dire sur plusieurs forums qu'il était possible de réaliser cela grâce au VBA.

Donc imaginons que j'ai 2 fichiers Excel. Un premier fichier contiendrait un tableau de tous les tickets incidents traités la veille. L'autre fichier est celui que je vais utiliser pour faire le push-mail. Donc je voudrais alors compter le nombre de tickets traités, en comptant le nombre de lignes non vides par exemple

Et donc est-il possible de créer un bouton qui lorsque j'appuierais sur celui-ci écrit dans une cellule du 2eme fichier, le nombre de lignes non vide du 1er fichier ? J'ai oublié de préciser que ce 1er fichier provient d'un export d'une application. Tous les matins, j'exporte ce fichier Excel pour compter le nombre de ligne mais le nom du fichier ne change jamais. Est-ce un probleme que ce fichier ne soit pas le même tous les jours pour le code VBA ? Ou a t-on besoin seulement que le nom de fichier ne change pas ?

Merci d'avance pour toute aide.

bonjour

joins tes 2 fichiers ou des exemples

je pense que SOMMEPROD suffira

Bonsoir,

Le bouton n'est qu'un bouton ! Lui ne fait rien ! Il te permet seulement de déclencher la procédure que tu lui auras affectée ! Après que tu lui en aies affectée une, naturellement !

Une procédure pour compter des lignes dans une feuille d'un classeur, et placer le résultat dans un autre classeur, ça ne mérite même pas qu'on s'y arrête ! Yaka l'écrire ! On sait comment compter, de quantité de façons !

Seulement, pour écrire quelque chose qui fonctionne, il faut répondre à des questions de type quoi, quand, où... des noms, des références, des adresses... dont on n'a pas la première indication

Alors...

Oui, désolé mon 1er post était bien trop imprécis, j'aurais du mettre un exemple de tableau

Donc en fait, je cherche à compléter tous les jours un fichier du type Commande.xlsx et donc pour remplir la case "Nombre de commandes vendues", je m'aide d'un fichier du type Tableau journalier.xlsx. Donc nombre de commandes vendues = derniere ligne -3

Et c'est pour cette raison que je cherche à automatiser cette action avec du VBA.

Je ne peux pas utiliser le même fichier car le fichier Tableau journaliser.xlsx est un fichier que j'export tous les jours d'une applications. Donc en fait, est-ce que le fait que ce fichier soit exporté tous les jours mais porte le même nom soit un probleme pour un futur code VBA ?

S'il porte toujours le même nom, on utilisera le nom!

Je suppose que tu le reçois, tu l'ouvres, et on traite à partir de ce moment, donc fichier ouvert.

A confirmer, et confirmer également si on le ferme après usage.

La date à porter sur Commandes est celle qui figure sur le Tableau journalier ?

Si c'est le cas est-elle toujours au même emplacement sous la même forme.

Est-ce que le -3 est garanti ? Pas de risque de lignes vides intermédiaire ? Pas besoin de compter individuellement ?

Cordialement.

bonjour

salut MFerrand

en mettant une colonne de date, tu fais une saisie des commandes les unes sous les autres durant des années

et ensuite un simple TCD (je l'ai mis sur le même onglet, i faudra le déplacer)

aucun calcul, aucune formule, aucun VBA.

Salut jmd !

Dans ce cas, on est en amont de ce que tu dis... Xionman veut juste que la saisie se fasse toute seule en appuyant sur un bouton...

La macro est écrite (plus vite écrit que d'en parler), elle ne fera que ça ! La suite c'est une autre histoire...

J'attends juste confirmation des quelques points d'ajustements pour s'assurer que l'on est bien dans les conditions décrites...

Cordialement.

Bonjour à vous deux,

Merci d'abord pour vos réponses,

@jmd

Le fichier Tableau journalier, je ne peux pas y toucher. C'est un fichier que je télécharge tous les jours et le seul invariant, c'est le nom du fichier. Et je dois compter uniquement compter le nombre de commandes

@MFerrand

En fait, je viens de remarquer que le fichier ne porte pas le même nom : il est constitué de la date du jour puis d'une heure qui différent chaque jour. Mais alors si tous les jours, je télécharge le fichier et que je l'enregistre sous un nom prédéfini au début, cela ne va pas poser probleme non?

Oui, on ouvre le fichier puis on le ferme après usage.

Concernant le -3, oui j'ai vérifié, il n'y a pas de lignes vides intermédiaires.

Concernant la date, c'est la même qui figure sur Commandes et tableau journalier et elle est toujours au même emplacement

Bonjour,

Voilà une macro :

Sub CommandesJour()
    Dim dcm(1)
    With Workbooks("Tableau journalier.xlsx")
        With .Worksheets(1)
            dcm(0) = CLng(DateValue(Right(.Range("D1"), 10)))
            dcm(1) = .Range("A" & .Rows.Count).End(xlUp).Row - 3
        End With
        .Close False
    End With
    With [Tableau2]
        .Cells(.Rows.Count + 1, 1).Resize(, 2).Value = dcm
    End With
End Sub

Elle considère que le fichier Tableau journalier est ouvert, il me semble que tu confirmes... Je n'ai pas bien compris si tu modifies le nom ou pas : dans le code il est sous le nom "Tableau journalier.xlsx", si c'est le nom que tu lui donnes, pas de problème, sinon on met en place une détection de l'autre classeur ouvert (quel que soit son nom...).

On prélève la date en D1 (selon tes indications) et on la convertit en nombre par sécurité. On récupère le nombre de commandes. Le tout sous forme de tableau qui pourra être affecté en une fois.

On ferme le fichier dont on n'a plus besoin.

Tu l'ajoutes à un tableau Excel. Le code en tient compte et utilise deux caractéristiques de ces tableaux (il s'agit donc que cette configuration soit pérenne.

Excel nomme automatiquement ce type de tableau (nom dans le gestionnaire de noms), ici Tableau2. Ce nom couvre la partie données du tableau (en-têtes exclues), et est utilisable en VBA dans les mêmes conditions qu'un nom de plage dans le classeur.

Lorsqu'on ajoute une ligne sous le tableau, elle est automatiquement intégrée au tableau.

Donc, dans le tableau (ce qui évite de faire référence au classeur ou à la feuille, on réfère directement au tableau qui est parfaitement identifiable), à la ligne qui suit le tableau, sur deux colonnes, on affecte notre tableau de données récupérées.

Et rien d'autre à faire. La date est affectée sous forme numérique, mais Excel aligne d'office le format de cellule sur la colonne du tableau, on n'a donc pas à le faire.

Cordialement.

Waouh, je n'aurais jamais eu l'idée de stocker ces données dans un tableau à 2 colonnes ... Merci pour cette macro qui marche merveilleusement bien !

En fait, le fichier Tableau journaliser ne s'appelle pas réellement ainsi. Il s'appelle plutôt "Tableau journalier ********* " où ces ** correspondent à la date du jour. Du coup, on peut faire une détection du fichier commençant par Tableau journalier parmi les fichiers ouverts ?

Bonjour,

Oui on peut !

Sub CommandesJour()
    Dim dcm(1), wb As Workbook
    For Each wb In Workbooks
        If wb.Name Like "Tableau journalier*" Then Exit For
    Next wb
    With wb.Worksheets(1)
        dcm(0) = CLng(DateValue(Right(.Range("D1"), 10)))
        dcm(1) = .Range("A" & .Rows.Count).End(xlUp).Row - 3
    End With
    With [Tableau2]
        .Cells(.Rows.Count + 1, 1).Resize(, 2).Value = dcm
    End With
    wb.Close False
End Sub

Cordialement.

Bonjour MFerrand,

J'ai une petite question concernant le code de la macro :

Cette condition " If wb.Name Like "Tableau journalier*" Then Exit For " ne signifie t-elle pas qu'on quitte le fichier excel lorsque le nom de celui-ci commence par Tableau journalier ?

Non, on sort de la boucle ! Et sortant alors que la variable de boucle wb est le classeur, on peut alors l'utiliser dans le reste du code (affectation indirecte, sans utiliser Set...)

Ah oui effectivement, j'avais mal compris le code ...

Et au fait, j'aimerais rajouter une derniere colonne (qui corresponderait à la 1ere colonne du tableau2). Du coup j'aimerais utiliser la fonction NO.SEMAINE. Comment faire référence à la cellule où l'on écrit la date ?

Cela te ferait un tableau à 3 colonnes : Num.Semaine, Date, Commandes ?

Oui voilà

Function NSem(d) As Integer
    Dim dref
    dref = DateSerial(Year(d + (8 - Weekday(d)) Mod 7 - 3), 1, 3)
    dref = dref - Weekday(dref) + 2
    NSem = (d - dref) \ 7 + 1
End Function

Sub CommandesJour()
    Dim dcm(2), wb As Workbook
    For Each wb In Workbooks
        If wb.Name Like "Tableau journalier*" Then Exit For
    Next wb
    With wb.Worksheets(1)
        dcm(1) = CLng(DateValue(Right(.Range("D1"), 10)))
        dcm(0) = NSem(dcm(1))
        dcm(2) = .Range("A" & .Rows.Count).End(xlUp).Row - 3
    End With
    With [Tableau2]
        .Cells(.Rows.Count + 1, 1).Resize(, 2).Value = dcm
    End With
    wb.Close False
End Sub

J'ai rajouté une fonction de calcul du numéro de semaine. La fonction de feuille de calcul NO.SEMAINE ne donne pas le numéro à la norme européenne jusqu'à la version 2007. Pour Excel 2010, il semble qu'on puisse l'utiliser avec le paramètre 21, bien que l'Aide soit confuse à cet égard et n'indique pas une définition correspondant à la norme [ NO.SEMAINE(date;21) ]. Pour les versions ultérieures, il y a une fonctions spécifiques donnant le numéro de semaine ISO. Côté VBA, la fonction DatePart qui devrait le donner, renvoie un numéro erroné au moins une fois tous les 28 ans... Une fonction est donc la solution la plus sûre.

Cordialement.

Wow ca marche vraiment bien ! Il y a juste une petite erreur dans le code "Resize(, 3)" ^^

Et en fait, juste par curiosité, comment as-tu connu cette fonction pour transformer le nombre de semaines en numéro de semaine ?

Excuses pour le recadrage de la plage, ça m'a échappé !

Le numéro de semaine, c'est une longue histoire...

Pour la fonction, le principe est assez simple, une fois établi , si le 4 janvier n'est pas un lundi on cherche le lundi qui précède, qui est le premier jour de la semaine 1.

Trouver le lundi à partir d'une date, ça se fait par un décalage selon le jour de cette date en utilisant JOURSEM (WeekDay). Par commodité, on procède à partir du 3 (si le 4 est un lundi, le 3 sera un dimanche), ce qui permet un décalage homogène avec JOURSEM renvoyant un nombre de 1 à 7 du dimanche au samedi (en retranchant JOURSEM de la date du 3 et en ajoutant 2, on tombe sur le bon lundi).

Ensuite le calcul est la différence de la date cherchée avec ce premier lundi, dont on fait le quotient entier par 7 et on ajoute 1 (certains prennent le lundi qui précède et n'ajoutent pas 1, le résultat est le même).

Reste une petite difficulté, si la date cherchée se trouve dans les jours qui baladent entre la dernière semaine d'une année et la première de la suivante, l'année de la date peut ne pas correspondre à l'année dans laquelle se trouvera la semaine cherchée).

La première ligne de calcul de la fonction vise donc à déterminer la date du 3 janvier de l'année qui convient (principe de calcul emprunté à Laurent Longre, qui le réalise de façon très compacte, en une ligne...)

Voilà pour ton information.

Bonne continuation.

Oh, c'est très ingénieux de sa part ! Merci pour cette explication très claire !

Et en fait, j'aurais un petit problème qui concerne le même fichier mais ce n'est plus du code VBA. Cela concerne plutot la construction du graphe, et notamment les étiquettes de données. Pourrais-je la poser ici ?