Code VBA afin de créer un TCD

Bonsoir le Forum,

J’ai enregistré une macro (mon dieu qu’elle est laide) à l’aide du développeur afin de créer un tableau croisé dynamique sur la base d’un modèle de fichier.

Ca fonctionne passablement bien, mais j’ai en tout cas un gros problème : comme je sélectionne automatiquement des colonnes entières, un champ « vide » est alors inclus à mon tableau.

Pourriez-vous aussi m’aider à ne sélectionner que les lignes comportant des données, le nombre de lignes étant bien évidement à chaque fois différent ?

Toute autre amélioration de ce code serait également la bienvenue.

A vous relire.

bonjour,

tu n'as probablement pas besoin d'enregistrer un macro qui créece TCD

1) Avec Insertion/Nom/Définir tu définis une zone dynamique (nous l'appellerons "mazone")

ça va te donner quelque chose comme ça :

=DECALER(Base!$A$1;;;NBVAL(Base!$A:$A);5)

2) Tu définis une fois pour toute ton TCD avec comme plage source

=mazone

Et dans ton bouton tu mets seulement un "refresh"

Fais l'expérience avec ma démo :

rajoute ou supprime quelques lignes et appuie sur le bouton.

Elle est pas belle la vie ?

A+

Nota : Si tu as absolument besoin de créer " a chaque fois un nouveau TCD, le raisonnement est le même tu enregistre ta macro en entrant ta zone dynamique (=mazone) dans le TextBox "source des données"

Nota2 :

La démo "ne fonctionne pas" dans le navigateur (IE) : il faut la télécharger.

Bonsoir Galopin et merci beaucoup pour ta proposition.

En fait ce n’est pas tout à fait ce que je désirais mais je n’avais pas tout expliqué car je ne pensais pas que tu partais dans une telle direction.

Mon fichier de base – mis à disposition des utilisateurs en lecture seule - ne comporte pour l’instant que les titres de la ligne 1 et un bouton permettant de lancer un code afin de créer des sous-totaux (après avoir effacé des lignes inutiles importées par obligation). L’utilisateur doit y introduire manuellement – dès la ligne 2 - des données provenant d’un autre programme et lancer la macro.

Il serait parfois intéressant d’avoir un tableau croisé dynamique en lieu et place des sous-totaux, raison pour laquelle j’avais pensé mettre à disposition un deuxième bouton lançant – à choix - une autre macro que celle déjà existante.

Comme tu le sous-entends, il faudrait alors absolument créer (si désiré à la place des sous-totaux) un nouveau TDC à chaque fois. Tu m’indiques bien que dans un tel cas « le raisonnement est le même tu enregistre ta macro en entrant ta zone dynamique (=mazone) dans le TextBox source des données » mais là je suis un peu largué.

N’est-ce alors pas possible de modifier simplement mon code, tel que demandé dans mon premier message ?

A vous relire. Bonne soirée.

ménon, je ne sous-entend rien. Je suis juste parti dans cette direction parce que c'est ce qui me semblait le plus vraisemblable...

Ya juste à nommer ta zone comme cité précédement. Yapadkoi en faire une maladie : tu vas dans l'onglet

Formule / Gestionnaire de nom / Nouveau et dedans tu tapes le nouveau nom et l'adresse dynamique indiquée. C'est indispensable si tu veux supprimer les vides. Avoir un truc propre quoi...

Et puis c'est tout. Tu récupères le code que je t'ai pondu sur cette nouvelle démo. Au passage tu peux même regarder dans le gestionnaire de nom comment j'ai nommé la base...

Bon tu peux nommer le TCD comme tu veux, le plus court sera le mieux : moi j'ai trouvé que "TCD" c'était aussi bien que "Tableau croisé dynamique2"

Tu raccourcis, tu nettoies, enfin tu regardes comment j'ai fait... Et puis YAPA de problème.

Tu peux faire l'expérience en supprimant ou en rajoutant des lignes... C'est du gateau !

A+

Salut Galopin et merci beaucoup pour cette nouvelle réponse.

Ca va, j'ai maintenant compris comment ça marche. C'est ainsi parfait.

J'indique provisoirement ce fil comme "Résolu", mais il est possible que je doive le rouvrir après que j'ai fait des essais sur mon ordinateur au travail, mais aussi des essais afin de combiner ces deux macros "TDC" et "Sous-totaux".

Bonne journée à tous.

Bonjour

Ta macro peut être simplifié de cette façon:

Sub TCD()
On Error Resume Next
Application.ScreenUpdating = False
ActiveSheet.PivotTables("tcd").TableRange2.Clear
    ActiveWorkbook.Names.Add Name:="mazone", RefersToR1C1:= _
        "=OFFSET(Base!R1C1,,,COUNTA(Base!C1),5)"
    Columns("F:G").ClearContents
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="mazone" _
        ).CreatePivotTable TableDestination:=Range("H4"), TableName:="TCD"
    With ActiveSheet.PivotTables("TCD")
    With .PivotFields("Prénoms"): .Orientation = xlColumnField: .Position = 1: End With
    With .PivotFields("Rente"): .Orientation = xlColumnField: .Position = 2: End With
    With .PivotFields("Montant"): .Orientation = xlRowField: .Position = 1: End With
    ActiveSheet.PivotTables("TCD").AddDataField ActiveSheet. _
        PivotTables("TCD").PivotFields("Montant"), "Nombre de Montant", xlCount
    With .PivotFields("Montant"): .Orientation = xlPageField: .Position = 1: End With
    With .PivotFields("Montant"): .Orientation = xlRowField: .Position = 1: End With
    With .PivotFields("Prénoms"): .Orientation = xlRowField: .Position = 2: End With
    With .PivotFields("Rente"): .Orientation = xlRowField: .Position = 3: End With
    With .PivotFields("Montant"): .Orientation = xlPageField: .Position = 1: End With
    With .PivotFields("Nombre de Montant"): .Caption = "Somme de Montant": .Function = xlSum: End With
    With .PivotFields("Somme de Montant"): .NumberFormat = "_ * #'##0.00_ ;_ * -#'##0.00_ ;_ * ""-""??_ ;_ @_ ": End With
    End With
Application.ScreenUpdating = True
End Sub

-- Sam Jan 09, 2010 2:59 pm --

Re

Voici le fichier avec quelques modifications

Merci infiniment à toi aussi, Bruno, pour ta réponse ; ton code me semble effectivement bien épuré. Comme indiqué à Galopin, je vais voir comment utiliser vos codes avec mon autre fichier existant.

Bonne soirée et à la prochaine.

Bonjour,

Grace à mon fichier « Journal », on pouvait créer automatiquement des sous-totaux. J’avais tout d'abord posté le présent fil en pensant ajouter une macro parallèle dans ce fichier afin de donner la possibilité supplémentaire d’établir un TCD.

Lors de mes essais, j’ai toutefois constaté qu’il serait mieux qu’une seule macro effectue les deux choses à la fois.

J’ai donc essayé de combiner le code proposé par Galopin** à mon ancien code mais ça ne fonctionne pas comme voulu.

Pour l’instant, si je lance le code tel que présenté, ça efface les colonnes inutiles, ça effectue la copie désirée de la feuille, ça crée le TCD, ça effectue le premier sous-total et ça bloque avant d’effectuer le second sous-total.

Pourriez-vous encore m’aider à corriger cela ?

PROBLEME COMPLEMENTAIRE : Comme il peut y avoir passablement de lignes à traiter, il serait intéressant qu’à la fin de la macro, le fichier se présente au niveau des dernières lignes traitées. Mais il ne faudrait pas alors que le TCD ne soit pas visible car placé en sommet de page. Serait-il alors possible qu’il apparaisse au niveau des dernières lignes, tenant compte du fait qu’on ne sait pas - puisqu’il est créé avant les sous-totaux - le nombre de lignes finales que comportera le fichier.

** NB : J’ai préféré le code proposé par Galopin à celui proposé par Bruno afin de ne pas s’embrouiller encore plus avec cette possibilité complémentaire offerte par Bruno de remplacer le TCD. Comme on utilise ce fichier à chaque nouveau cas en lecture seule, cette possibilité me paraissait peu utile.

Bonne fin de journée.

bonjour,

ça ne parait guère faisable car l'insertion de sous-totaux provoque l'insertion de lignes un peu partout, or il n'est pas possible de modifier la présentation du TCD par insertion de ligne.

Il faut donc prévoir :

Soit mettre le TCD sur une autre feuille pour pouvoir faire tes sous-totaux,

Soit mettre le TCD suffisamment bas pour ne pas géner l'allongement du tableau au moment de la confection du plan (sous-totaux) mais cette hypothèse est difficile à réaliser car la génération de lignes supplémentaires est aléatoire...

Soit prévoir 2 TCD :

Le TCD normal + un autre TCD sous-total

Les 2 TCD peuvent alors être mis n'importe ou : soit côte à côte soit l'un en dessous de l'autre...

A+

Salut Galopin et merci infiniment pour ta réponse.

galopin01 a écrit :

Soit mettre le TCD suffisamment bas pour ne pas géner l'allongement du tableau au moment de la confection du plan (sous-totaux) mais cette hypothèse est difficile à réaliser car la génération de lignes supplémentaires est aléatoire...

Ne serait-il pas possible de placer le tableau après la dernière ligne et que celui-ci soit "poussé" vers le bas quand les lignes supplémentaires des sous-totaux sont créées ? Ou d'ètre déplacé quand les sous-totaux sont finis ?

Sinon effectivement que la solution la plus simple semblerait être de créer le TCD sur une feuille séparée. Dans un tel cas est-il possible de renommer la feuille par exemple "TCD" ?

A te relire.

Bonjour,

C'est les SOLDES !

2 pour le prix d'1 ...

Le premier répond excatement à la question... YAPUKA faire les finitions !

Le deuxième intègre le basculement des données sur une feuilles temporaire, le reset de tout le bazar etc...

Comme ça t'a toujours une base propre sans avoir rien à toucher.

A+

Euh, c'est génial, ce que tu as fait là, Galopin ! Je te remercie vraiment pour ta précieuse aide. Tu réponds exactement à mon attente, et même à double.

Est-ce que j'ose abuser encore un peu ? Avec le format des chiffres indiqué dans tes codes concernant le TCD, les chiffres de 1, 2 ou 3 nombres avant la virgules s'inscrivent par exemple '10.00 ou -'46.00 (avec le séparateur des milliers).

Comment serait-ce possible de modifier le code

.NumberFormat = "_ * #'##0.00_ ;_ * -#'##0.00_ ;_ * ""-""??_ ;_ @_ "

afin d'éviter cela ?

Encore merci pour tes deux fichiers et bonne soirée.

bonsoir,

Euh... Ya pas d'enregistreur sur ton Excel ?

Parce que c'est quand même pas compliqué d'enregistrer une macro de mise en forme comme ça te plait... et de faire un copier/coller du code...

Pour moi un format comme ça pourrait suffire :

"#,##0.00;-#,##0.00"

...mébon là je n'ai pas 2007 sur cet ordi. Il faut donc voir avec ton tabazou ou avec quelqu'un d'autre pour bricoler un format 2007 si celui que je t'ai donné ne suffit pas.

A+

Désolé Galopin,

Tous d'abord je n'y ai pas vraiment pensé et deuxièmement j'ai un peu la trouille de modifier ce genre de code car je ne suis pas trop sûr de ce que je fais.

Pour ceux que ça intéresse, avec l'enregistreur j'ai obtenu deux formats différents selon que je passe par l'icone en haut de page (celle avec les trois 000) ou que je passe par "Format de la cellule".

Le première version ne fonctionne pas pour le code que j'ai essayé de transformé de cette manière :

''''    .NumberFormat = "_ * #'##0.00_ ;_ * -#'##0.00_ ;_ * ""-""??_ ;_ @_ "
    .Style = "Comma"

Alors que le code tel que transformé ci-dessous fonctionne à merveille.

''''    .NumberFormat = "_ * #'##0.00_ ;_ * -#'##0.00_ ;_ * ""-""??_ ;_ @_ "
    .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

Je suis quand même surpris que le format d'origine - qui semble-t-il a été enregistré automatiquement lors de la création du TCD - donne un si mauvais résultat.

Mon problème est donc résolu. Merci infiniment Galopin pour ton aide et bonnes salutations.