Création de TCD via VBA

Version imprimable

Bonjour, j'ai actuellement un souci qui me bloque dans un projet, et que j'aimerais donc voir résolu au plus vite :D

Il s'agit de la création d'un TCD dans VBA. J'ai regardé ici mais le code ne fonctionnait pas, n'ayant pas réussi à l'adapter pour mes données.
Ne connaissant pas la syntaxe relative aux TCD, j'ai enregistré une macro qui m'a donné ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Sub Macro6() Range("A7").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Manips-Destinations Aériennes!R6C1:R240C10", Version:=xlPivotTableVersion15) _ .CreatePivotTable TableDestination:="GES-Destination par pays!R5C2", _ TableName:="Tableau croisé dynamique4", DefaultVersion:= _ xlPivotTableVersion15 Sheets("GES-Destination par pays").Select Cells(5, 2).Select End Sub
Seulement lors de sa ré-exécution, elle ne fonctionne pas, malgré que j'ai effacé le TCD précédemment créé (pour ne pas avoir de nom en doublon).

Mes questions :
=> Après quelques expérimentations de code, j'ai eu différentes erreurs : Celle qui intervient lors de l'exécution du code ci-dessus est la n°5 : "Argument ou appel de procédure incorrect".
Si je remplace PivotCaches.Create par PivotCaches.Add, j'obtiens l'erreur : "Erreur de compilation, argument nommé introuvable" avec "Version:=" surligné.
Comment faire pour que ma création de TCD s'effectue correctement ? Je ne pense pas que ce soit une procédure très compliquée pourtant, malheureusement je n'arrive pas à comprendre les erreurs.

=> Les 3 premières lignes de code sélectionnent une plage de cellules variables à partir de A7 (qui restera fixe), mais l'argument "SourceData" renvoie à la plage de cellules sélectionnées. Si la plage change de taille, la valeur de SourceData va-t-elle changer également automatiquement ?

Merci d'avance ! Je pense (j'espère !) qu'une fois la syntaxe correcte et comprise pour ce TCD, je serais en mesure de l'adapter pour les autres TCD que je dois réaliser.

04/05/2015, 13h32
Djohn92
Bonjour,

1. Je ne sais pas ce qui plante (sans le fichier), mais à priori, si tu enregistres ta macro avec l'enregistreur et que tu ne peux PAS la ré exécuter, c'est problématique, et pas normal du tout.

La seule chose qui pourrait expliquer cela, c'est le fait que tu tentes de récréer un tableau ("Tableau croisé dynamique4") et que ce TCD existe en fait déjà, et il ne peut pas écraser, faudrait juste l'effacer avant de relancer ta macro

2. La partie "sourcedata" de ta macro est l'emplacement des données sources. Là, dans ta macro, la source n'est pas la sélection
Tu peux ajouter au début
Code:

1 2 3 4 Dim Ma_plage as range Range("A7").Select Range(Selection, Selection.End(xlToRight)).Select set Ma_Plage = Range(Selection, Selection.End(xlDown)).Select
et remplacer

Code:

"Manips-Destinations Aériennes!R6C1:R240C10"

par Ma_Plage
04/05/2015, 13h42
Akhekhu

En effet donc dans la macro la source était uniquement la plage de données sélectionnée, mais pas considérée comme étant variable donc merci du coup de main pour le Ma_Plage.
Par contre lors du pas à pas, au niveau de

Code:

Set My_range = Range(Selection, Selection.End(xlDown)).Select

, j'ai une erreur 424 "Objet requis".

J'avais pensé au doublon, donc j'ai bien supprimé le tableau créé par la macro avant de la ré-exécuter, et c'est là qu'est apparu l'erreur. J'ai bien tenté de le renommer "My TCD" mais même erreur.
Si besoin du fichier je peux en mettre une copie partielle à disposition.

Petit double message désolé mais j'ai affiné la sélection de mes données, puisque seul le nombre de lignes est variable, j'ai donc ceci et plus d'erreur 424. par contre j'ai toujours l'erreur numéro 5 lors de l'exécution.
Ma question 2 est résolue merci, reste le plus important, le code correct !
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Sub Macro6() Worksheets("Manips-Destinations Aériennes").Activate Dim last_row As Integer last_row = Cells(Rows.Count, 1).End(xlUp).Row Dim My_range As Range Set My_range = Range(Cells(7, 1), Cells(last_row, 10)) ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ My_range, Version:=xlPivotTableVersion15) _ .CreatePivotTable TableDestination:="GES-Destination par pays!R5C2", _ TableName:="Mon TCD", DefaultVersion:= _ xlPivotTableVersion15 Sheets("GES-Destination par pays").Select Cells(5, 2).Select End Sub

04/05/2015, 13h59
Djohn92

Tu es sur quelle version d'excel. Ca serait pas une antérieur à 2007 ?
04/05/2015, 14h02
Akhekhu

Non, je travaille sur Excel 2013.
L'erreur d'exécution 5 arrive lorsque le programme passe dans la partie "Création" du TCD, dans la ligne de code coupée en 4
04/05/2015, 14h10
Djohn92

Je sèche.

Tente d'ajouter

Code:

Sheets("GES-Destination par pays").Select

avant la création du TCD.

Et sinon, récréé ta macro depuis l'enregistreur, je vois que ça.

Bonjour

Si déjà tu mettais ta source sous forme de tableau, cela simplifierait le calcul de la plage source et éviterait la sélection
Code:

1 2 3 4 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Tableau1", Version:=xlPivotTableVersion14).CreatePivotTable _ TableDestination:="Feuil2!R1C1", TableName:="MyTCD", _ DefaultVersion:=xlPivotTableVersion15
Un conseil nommer le tableau cela facilite la maintenance.

L'ajout de cette ligne ne change pas l'erreur.
Par contre lorsque j'ai refait la macro à la fin, le code a légèrement changé, voici le brut :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Range("A7").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("GES-Destination par compagnie").PivotTables( _ "Tableau croisé dynamique6").PivotCache.CreatePivotTable TableDestination:= _ "GES-Destination par pays!R4C2", TableName:="Tableau croisé dynamique7", _ DefaultVersion:=xlPivotTableVersion15 Sheets("GES-Destination par pays").Select Cells(4, 2).Select ActiveWorkbook.ShowPivotTableFieldList = True
Mais il n'y a plus l'argument SourceData, et le mélange des deux codes ne fonctionne pas non plus.
En cherchant un peu sur Internet, j'ai vu que la création de TCD via VBA posait de fréquents problèmes, c'est étrange, étant donné la simplicité de la manipulation à la main.
J'espère que quelqu'un saura corriger mon petit bout de code de manière à le faire fonctionner !

EDIT : Pour 78chris :
Mettre ma source sous forme de tableau c'est-à-dire ? Sélectionner la zone source dans Excel, et la nommer tableau1 ?
le problème est que cette source est variable, donc cela va-t-il fonctionner ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub Macro6()
 
Worksheets("Manips-Destinations Aériennes").Activate
 
Dim last_row As Integer
last_row = Cells(Rows.Count, 1).End(xlUp).Row
 
Dim My_range As Range
 Set My_range = Range(Cells(7, 1), Cells(last_row, 10))
 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        My_range, Version:=xlPivotTableVersion15) _
        .CreatePivotTable TableDestination:="GES-Destination par pays!R5C2", _
        TableName:="Mon TCD", DefaultVersion:= _
        xlPivotTableVersion15
    Sheets("GES-Destination par pays").Select
    Cells(5, 2).Select
 
End Sub

Dernière tentative, après je sèche.
"GES-Destination par pays!R5C2"

Renomme ton onglet
"GES_Destination_par_pays!R5C2"
Modifie le code en conséquence

Bonne chance !:)

04/05/2015, 19h03
78chris

RE

As-tu testé ma proposition ?
04/05/2015, 20h09
Akhekhu

Bonsoir, dans un premier temps j'ai des difficultés à répondre sur ce topic, j'ai beau être connecté sur le forum, dès que je clique sur "Répondre" il me demande de me reconnecter, donc impossible c'est très désorientant mais bref.

78chris, en fait je n'ai pas bien compris ta proposition (j'avais édité mon précédent message, tu l'as probablement manqué). Qu'entends-tu par mettre la source sous forme de tableau ? En fait je ne suis pas sûr de saisir si tu as de quoi m'éclairer un peu !

Djohn92, j'ai essayé ton idée, mais ça ne fonctionne pas, le nom de l'onglet ne semble pas être un élément dérangeant puisqu'entre guillemets de toutes façons.

Ce que je ne m'explique pas en fait, c'est pourquoi l'exécution de la simple macro donnée automatiquement ne fonctionne pas. Et si sur d'autres forums ils évoquent souvent ce problème, je ne vois pas beaucoup de solutions à apporter. Je dois sans doutes louper un élément quelque part.
04/05/2015, 21h03
Djohn92

Rebonjour,

mettre "GES_Destination_par_pays!R5C2"

et pareil en onglet a corrigé le TCD, j'avais pareil une erreur "5"

En fait, dans une formule de range "classique" il faut metter des apostrophe pour isoler les noms de feuille à espace

on ne met pas
aaa aaa!A1

Mais
'aaa aaa'!A1 mais les "'" sont gérés bizarrement, d'ou le bon coté de ne pas mettre d'espace.

Pas forcément une question de parenthèse. j'étais presque sur que c'était ça. je suis sur que c'est un truc con.

peux tu réessayer ?

Sinon, peut tu spécifier avant "my range" la sheet sur laquelle tu travaille de façon explcite dans le code ?
04/05/2015, 21h14
Akhekhu

Héhé bien vu je ne connaissais pas cette particularité pour les feuilles avec noms possédant des espaces !
De fait cela marche avec les apostrophes

Code:

TableDestination:="'GES-Destination par pays'!R5C2"

Même si cela n'est pas très efficace en VBA (la preuve je viens poster ici ^^), je préfère garder mes noms de feuille avec des espaces, je n'ai pas le courage de parcourir tout mon programme pour tous les rechanger. La méthode que tu m'as donné fonctionnant je vais l'appliquer également plus loin !

Merci de l'aide, et avant de mettre ce topic en Résolu, j'aimerais quand même avoir l'explication de 78chris qui m'aidera peut-être pour la suite.
04/05/2015, 21h40
78chris

Re

Pour les tableaux voir ces 2 liens http://silkyroad.developpez.com/excel/tableau/ et http://www.excel-formations.fr/Trucs...Tableaux01.php
04/05/2015, 22h55
Akhekhu

Ok merci de l'astuce je vais essayer de comprendre la manip et de l'appliquer quand nécessaire !
Merci à vous deux, question résolue.
Bonne soirée !