TCD par macro

[Ducleto]

Bonjour,

Je suis novice sous VB et j'ai qq notions sous Excel mais j'ai une petite question.

J'ai une vingtaine de fichier qui contiennent chacun une plage de données à partir de laquelle je voudrais faire pour chacun de 20 fichier plusieurs TCD qui seront identiques.

J'ai donc pensé passer par une macro mais cela ne fonctionne pas. Premièrement parce que la taille des plages de donées sources diffère dans chaque fichier et deuxièmement parce que chaque fichier porte un nom différent.

Si qqun peut m'aider, je laisse mon code ci-dessous...

Merci par avance,

Pierre

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "amiens!R1C1:R251C24").CreatePivotTable TableDestination:="", TableName:= _
        "Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddFields RowFields:= _
        Array("Agence", "LIB COMPTE"), ColumnFields:="ANNEE"
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("MONTANT" _
        )
        .Orientation = xlDataField
        .NumberFormat = "# ##0_ ;-# ##0\ "
    End With
    Sheets("amiens").Select
    ActiveWorkbook.Worksheets("Feuil7").PivotTables("Tableau croisé dynamique1"). _
        PivotCache.CreatePivotTable TableDestination:="", TableName:= _
        "Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddFields RowFields:= _
        Array("Nom - Prénom", "LIB COMPTE"), ColumnFields:="ANNEE"
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("MONTANT" _
        )
        .Orientation = xlDataField
        .NumberFormat = "# ##0"
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotSelect _
        "'Nom - Prénom'[All;Total]", xlDataAndLabel, True
    With Selection.Interior
        .ColorIndex = 40
        .Pattern = xlSolid
    End With
    Range("D3").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").RowGrand = False

 

[CB60]

Re : TCD par macro

Bonsoir Ducledo,
ce n'est pas facile de t'aider en voyant la macro que tu a réalisé il y a une reference à une feuille et il y a deux TCD en création.
Que souhaite tu exactement? car si tu veux réaliser un Tcd qui puisse être utilisé sur plusieurs classeurs il faut savoir que les entêtes doivent être identique et ensuite s'il le son tu devras mettre la Macro que tu aura réalisé dans les macros complémentaires.
Si je me trompe et que "Dan" passe par là je pense qu'il corrigera mes infos.
Bruno

 

[Dan]

Re : TCD par macro

Bonjour,

Difficile de te répondre sans voir la macro complète ou mieux le fichier avec le TCD. D'ailleurs tu dois savoir qu'avec les TCD ce n'est jamais facile les macros. Toujours du cas par cas.

Quelques indications toutefois :
1. Pour définir la source, je vois : amiens!R1C1:R251C24
Essaie ceci :

Set Maplage = Sheets("amiens").Range("A1").CurrentRegion
Maplage.Name = "TCD"

Ensuite tu remplaces "amiens!R1C1:R251C24" par "TCD"

2. Partout enlève aussi : DefaultVersion:=xlPivotTableVersion10

3. pour plus de lisibilité remplace partout: Tableau croisé dynamique1 par TCD1.

4. Reste la "feuil7", là tu vas surement avoir un pb et sans fichier ...
A te lire

 

[Ducleto]

Re : TCD par macro

Merci pour vos réponses rapides.

"Amiens" est en effet un de mes 20 fichiers sources. Sur chacun de ceux-ci j'ai une feuille avec les données (le nombre de ligne varie allant jusqu'à qq milliers) et pour chacun de ces 20 fichiers, je voudrais réaliser plusieurs TCD différents entre eux mais identiques entre les fichiers. En clair chaque fichier comportera un TCD 1, TCD 2, TCD 3...

Dan, la "feuil7", c'est la ou je mets mon 2ieme TCD et ainsi de suite pour les suivants mais peu importe le nom de la feuille qui je pourrai renommer par après.

Je tente ce dont tu m'as parlé Dan et je vous tiens au courant.

Merci bien

Pierre

 

[Ducleto]

Re : TCD par macro

Je suis aussi entrain de me dire que

Set Maplage = Sheets("amiens").Range("A1").CurrentRegion

doit être variable. Chaque fichier comme "amiens" porte un nom différent. J'ai donc créer une macro qui ouvre chaque fichier l'un apres l'autre puis qui avant de le refermer et le sauvegarder, ouvre la macro pour creer le TCD. Ma question est donc comment lui dire que "amiens" doit être une variable et comment la rendre variable ?

 

[Dan]

Re : TCD par macro

Re,

Pas facile de suivre ton appli là.

Ce que je comprends :
- Tu as 20 fichiers dont un ici s'appelle "AMIENS".
- La macro que tu demandes sera dans chaque fichier (par exemple ici AMIENS)
- Dans chaque fichier, cette macro va créer quelques TCD qui seront placés dans des feuilles différentes de ce même fichier (donc ici 3 ou 4 TCD dans le fichier "AMIENS")

Peux-tu confirmer cela ?

Dans "Set myplage" remplace "AMIENS" par le nom de la feuille de la base de données de ton fichier "AMIENS".

D'autre part si je pouvais avoir un fichier modèle avec données non confidentielles mais tous les champs que l'on doit retrouver dans le TCD, ce serait plus facile car comme je te l'ai dit les macros dans les TCD c'est assez particulier.

A te lire.

 

[Ducleto]

Re : TCD par macro

Dur combat ac VB mais j'y suis arrivé et je crois avoir gagner pas mal de temps : en tout j avais 47 fichiers (et non pas 20) sur lesquels je voulais les mm TCD. Tu m'avais tout à fait compris Dan.

Ce que j'ai fait, c'est que j'ai créer une première macro qui appelle pour chaque fichier une deuxième macro avec en variable le nom du fichier et l'onglet. Dans la deuxième macro je retrouve les mm variables que j'utilise en remplacement ds la création des TCD (comme tu me l'as indiqué avant Dan).

J'ai tous mes fichiers de prêt, c'est parfait. Merci pour votre aide.

Pierre

 

[Ducleto]

Re : TCD par macro

Cependant...

Il me reste une question qui n'a pas grand chose à voir avec les macro mais peut être vous pourrez m'aider.

Mon TCD contient 2 champ (Agence et Prénom) en colonne et un en ligne (date) et une somme dans "Données"

Tout va bien jusque là, il ne me fait apparaitre dans "Données" que les montants correspondant aux prénoms des agences où les prénoms sont. Mais quand j'introduis un "éléments calculé" (TCD/formule/élémt calculés) (variation en % entre 2 dates qui est mon champ ligne), il me fait apparaitre l'ensemble des prénoms dans chaque agence en laissant vide les données ou il ne trouve pas de correspondance par agence.

Savez vous comment je pourrais lui dire de n'afficher QUE les prénoms des Agences comme il le faisait avant que j'introduise l'élément calculé ?

J'abuse peut être un peu, ce n'est peut être pas l'endroit mais vous m'avez déjà bien aidé, j'y suis presque.

Pierre

 

[Dan]

Re : TCD par macro

Re,

Non tu n'abuses pas mais comme je te l'ai dit, pas facile sans voir un fichier.
donc je reste général.

1. Ta macro
Pour ce qui est de ta macro, j'avais vu pas mal de choses à corriger pour l'améliorer et surtout l'exécuter plus rapidement car là, il y a des choses bizarres.

2. Champs calculés
Attention avec les champs calculés qui ne donne pas toujours le résultat escompté. Si tu fais un champ calculé tu ne peux le construire qu'avec les champs que tu vois dans la liste "champs". Donc ne pas introduire de formule genre SI ou autre.
Lorsque tu incorpores un champ calculé mets le à droite du TCD et surtout vérifies tes résultats. Là parfois on a des surprises car le TCD interprète pas nécessairement dans le sens que tu le souhaites.
Essaie en déplaçant les champs de manière à présenter le TCD autrement.

3. remarques
Attention avec les dates si tu actualises tes TCD. Si jamais tu les groupes en mois et années dans le TCD, veille à ne jamais te tromper à l'encodage !! sinon patatra !!

Bonne soirée

Si pb n"hésite pas

 

[Ducleto]

Re : TCD par macro

Dan,

J'avais apporté l'ensemble des modifs dont tu m'avais parlé et ca a en effet marché nickel. Tout tourne rond maintenant.

En ce qui concerne les ELEMENTS CALCULES et non les CHAMPS CALCULES, j'ai toujours ce meme pbl de l'affichage. Ca reste secondaire mais tant qu'à faire, j'aimerai avoir une présentation nickel !

Merci en tout cas pour ton aide.

Pierre

 

[Ducleto]

Re : TCD par macro

Une autre petite question...

Je veux maintenant pour chaque fichier ajouter sous le TCD de chaque onglet un tableau copier coller d'un autre fichier. Je vais procéder par macro.

Pour chaque fichier, la taille du TCD varie ce qui fait que l'endroit du copier coller doit varier aussi. Comment dire dans la macro, qu'il faut qu'elle regarde ou se finit le TCD, rajouter 2 lignes et coller à partir de là seulement ?

Merci par avance

Pierre

 

[Dan]

Re : TCD par macro

Bonsoir,

Utilise par exemple cette instruction :

activesheet.Range("A65536").End(xlUp).Row + 2

A bientôt