Insertion de TCD en VBA

[FlorianQ]

Bonjour le forum,

Toujours dans ma quête de créer une macro qui automatise un certains nombre de tâches, j'en arrive depuis quelques heures par-ci par-là aux tableaux croisés dynamiques. J'ai nommé mes plages de cellules qui peuvent être de taille variable avec la fonction "DECALER". En me réferant sur des forums et en n'étant pas sûr de la synthaxe, j'utilise l'enregistreur de macro pour voir ce qu'il pourrait me sortir, et voici ce qu'il en est :

Sub ModeleTableau()
'
' ModeleTableau Macro
'

'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "BaseSainePRO", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Tableaux dynamiques!R6C53", TableName:= _
        "Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion12
    Sheets("Tableaux dynamiques").Select
    Cells(6, 53).Select
End Sub

Avec BaseSainePRO qui est bien l'une de plage nommée au préalable

Cependant, même juste en exécutant cette macro, j'obtiens l'erreur "Argument ou appel de procédure incorrect"

J'ai cherché sur Internet d'où cela pouvait provenir et j'ai trouvé qu'un internaute avait le même problème, cependant ce qui a réglé son problème n'a pas arrangé le mien...
C'est pour cette raison que je ne refuserai pas un petit coup de pouce

Merci d'avance !

 

[13GIBE59]

Re : Insertion de TCD en VBA

Bonjour Florian,

Sur mon fichier comptes, je crée également un TCD par vba.
Mon code c'est ça :

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("TCD").Visible = True
Sheets("TCD").Activate
ActiveWindow.SelectedSheets.Delete 'supprime "l'ancien" TCD
Derligne = [G65536].End(xlUp).Row
Sheets("Comptes").Select
Range("G8:L" & Derligne).Select
ActiveWorkbook.Names.Add Name:="BD", RefersToR1C1:=Selection
Worksheets.Add().Name = "TCD"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"BD", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="TCD!R3C1", TableName:="TCD1", _
DefaultVersion:=xlPivotTableVersion12
Sheets("TCD").Select
Cells(3, 1).Select

Essaie d'abord Aplication.DisplayAlerts=False
Ensuite essaie de supprimer ton ancien TCD et de le recréer.

Ma feuille se nomme "TCD", et je crée ma base de données et la feuille TCD à chaque fois (Range("G8:L" & Derligne).Select
ActiveWorkbook.Names.Add Name:="BD", RefersToR1C1:=Selection
Worksheets.Add().Name = "TCD")


Bon courage !

JB

 

[FlorianQ]

Re : Insertion de TCD en VBA

Salut JB, et merci pour ta réponse !

Pour amener plus de précisions à mon sujet, j'ai un nombre prédéfini d'onglets, l'utilisateur importe sa base de données et dans des onglets spécifiques il peut les épurer selon certains critères. Ensuite de cette base épurer, un certains nombre de colonnes se rajoute, puis de cette base entière, j'aimerai créer des tableaux croisés dynamiques dans la feuille du même nom, mais à des emplacements précis (on peut naviguer entre plusieurs zones avec des boutons), puis exporter mes résultats dans un autre classeur. Ma macro se ferme et n'est pas enregistrée à moins que l'utilisateur force l'enregistrement.

Concrètement je n'ai pas besoin de supprimer ou d'ajouter des feuilles, mais simplement insérer des TCD. Et malheureusement en reprenant la fin de ton code pour la création du TCD, j'ai toujours la même erreur...

 

[FlorianQ]

Re : Insertion de TCD en VBA

Re,

Après quelques autres recherches, j'ai pu observer que certains ont trouvé leur bonheur en remplaçant ligne et colonnes qui étaient interprété par respectivement "L" et "C" en français, alors que lorsque l'on code on note "R" et "C" en anglais. La seule référence qu'il pourrait il y avoir en "L" et "C" est dans ma plage dynamique, mais comment puis-je faire pour... si j'ai bien compris, la modifier en anglais ?

 

[chris]

Re : Insertion de TCD en VBA

Bonjour
Coucou Gibe

Quelques remarques :

• depuis 2003 mais surtout 2007, il est plus simple de déclarer sa plage en tableau plutôt que de définir la plage avec DECALER
• l'enregistreur est un peu bugué sur 2007 mais même sur 2010 le code TCD de l'enregistreur ne marche pas toujours. As-tu bien supprimé tout TCD préexistant portant le nom Tableau croisé dynamique2
• si les onglets préexistent ainsi que les TCD, il serait plus simple de les mettre à jour : est-ce ton cas ?

 

[FlorianQ]

Re : Insertion de TCD en VBA

Bonjour Chris, merci pour ta réponse

Alors dans l'ordre :
-C'est bon à savoir, je l'ignorais, je "débute" en VBA, surtout pour la création de tableaux croisés dynamiques

-Il n'y avait aucun TCD pré-existant.

-Je n'y ai pas pensé mais en théorie, après insertion des données et calcul de la plage de données, des TCD qui se mettraient à jour seraient beaucoup plus pratiques. Est-ce-que cela m'autoriserait à les faire sans l'aide de VBA et à les mettre à jour à l'aide d'un bouton par exemple ?


Sinon j'ai réussi à créer un TCD, d'une apparence telle que je n'en avais jamais vu auparavant : 4 Cases encadrées, toujours le même sous-menu sur le côté mais beaucoup moins digeste en fonction sur le tableau en lui même.
Voici le code utilisé

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "BaseSainePRO").CreatePivotTable _
    TableDestination:="'Tableaux dynamiques'!R8C53", TableName:= _
    "TCD"

Dans tous les cas, s'il est possible de mettre simplement à jour des TCD, cela serait beaucoup plus pratique.

 

[chris]

Re : Insertion de TCD en VBA

Re

Ton fichier ne serait-il pas en xls ?

Si tu mets une plage sous forme de tableau, puis lui donne un nom et construis le TCD sur ce tableau tu pourras vider puis remplir ce tableau, ou bien le compléter, et simplement actualiser un TCD : tous les TCD basés sur ce même tableau le seront également.

Et ils suivront l'évolution du nombre de ligne et de colonnes du tableau source (sous réserve de ne pas y inclure de lignes vides au départ). Un peu de lecture sur les tableaux Ce lien n'existe plus

Nous n'avons pas l'ensemble de ton code mais il serait facile d'ajouter une actualisation.

 

[FlorianQ]

Re : Insertion de TCD en VBA

Re,

Très bien je vais voir ce que je peux faire alors, je n'ai plus accès à mon fichier d'ici lundi. Le fichier est en xlsm. C'est une excellente nouvelle, si je peux créer les tableaux sans passer par du code j'aurais beaucoup plus de libertés.
Si jamais j'ai un problème je donnerai suite lundi... Un peu long comme attente...

Merci à toi !

 

[FlorianQ]

Re : Insertion de TCD en VBA

Bonjour le forum,

Il semblerait après test que la mise à jour des TCD fonctionne, cependant je n'ai pas réussi à le faire à l'aide d'un tableau, je suis resté sur la méthode DECALER. Il semblerait que dans mes TCD les filtres ne se mettent pas vraiment un jour. Par exemple si j'ai une ligne "Papa", mais que celle-ci disparait à la prochaine mise à jour, je peux toujours filtrer par "Papa", un détail...

J'essaye d'exporter ces résultats dans un nouveau classeur mais je n'y parviens pas, voici le code

Private CS As Workbook 'déclare la variable CS (Classeur Source)
Private CC As Workbook 'déclare la variable CC (Classeur Cible)
Sub Exportation()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set CS = ThisWorkbook

    'On créer l'objet Excel
    Set xlApp = CreateObject("Excel.Application")
    'On défini le nombre d'onglets (ici 1)
    xlApp.SheetsInNewWorkbook = 1
    'On ajoute un classeur
    Set xlBook = xlApp.Workbooks.Add
    'On donne un nom au classeur
    xlBook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Analyse du fonds de commerce export.xlsx", FileFormat:=51
    'On rend le classeur visible
    xlApp.Visible = True
    'On créer l'objet onglet dans le nouveau classeur créé
    Set xlSheet = xlBook.Worksheets(1)
    'On affecte un nom aux l'onglets
    xlSheet.Name = "Résultats"
    'on libère l'objet onglet pour pouvoir en créer un nouveau ... etc
    Set xlSheet = Nothing
    '
    'On remet la propriété de l'application à 3 (par défaut)
    xlApp.SheetsInNewWorkbook = 3
    'On ferme l'application
'    xlApp.Quit



    
CS.Sheets("Tableaux dynamiques").Range("AZ5:BN200").Copy
Workbooks("Analyse du fonds de commerce export").Sheets("Résultats").Range("B10").Select
    
End Sub

J'essaye de copier une plage de données de mon fichier dans un second fichier que je viens de créer, il semblerait que la sélection fonctionne, cependant il refuse de coller dans mon nouveau classeur. J'ai essayé de rentrer "Analyse du fonds de commerce export" dans la variable CC (pour Classeur Cible), mais je n'y suis pas parvenu. Je me tourne vers vous car il y a quelque chose que je ne dois pas voir et plus je reste dessus plus cela devient flou...

Merci d'avance

 

[chris]

Re : Insertion de TCD en VBA

Bonjour

Ton code est dans un classeur Excel ?
Si oui pas besoin de déclarer l'application : au contraire tu risques d'ouvrir une seconde session et cela va compliquer inutilement.

Pas sûr que les variables doivent être déclarées en private mais je n'ai pas d'expertise sur ce type.

Pour le nom il faut le mettre entre ' (apostrophes) car il contient des espaces.

Pour les filtres cela se règle via une option du TCD. (Onglet données, Nombre d'éléments à retenir par champ : aucun).
Le conseil est de bien paramétrer la source (il faut retenter l'aspect tableau ;-)) puis les TCD, tout cela manuellement une fois pour toutes puis le code peut servir à automatiser l'actualisation mais c'est une ligne de code...

Poste un extrait représentatif si tu bloques.

 

[FlorianQ]

Re : Insertion de TCD en VBA

Bonjour le forum,
Merci Chris pour tes renseignements supplémentaires et désolé de ne pas avoir répondu plus tôt, je travaille sur plusieurs aspect en même temps.

Me voilà de retour sur le sujet et j'ai un simple "RefreshAll" sur ma page de TCD, ce qui raffraichissait le tout. Or en l'essayant ce matin, je vois que tous mes tableaux ne s'actualisent pas, il en manque 2-3 sur 7, pourtant issu de cette histoire de plage dynamique avec DECALER.
Du coup j'ai retenté de faire des tableaux, sauf que si mon nombre de ligne est différent, je vois que les plages de mon tableau ne s'actualise pas, je ne comprends pas bien...

Je vais tenter de faire une base factice pour voir si éventuellement quelque chose en particulier pose problème

 

[FlorianQ]

Re : Insertion de TCD en VBA

Je vous joint ici la base utilisée, je l'ai rapidement simplifié alors si vous avez des question n'hésitez pas.

 

[chris]

Re : Insertion de TCD en VBA

Bonjour

Dans ton exemple j'ai un seul TCD. Donc difficile de vérifier s'ils ont la même source.

La formule DECALER doit être ajustée car elle renvoie une ligne de trop en raison du libellé en A3 (soustraire 1 à NBVAL)

Concernant les filtres voir ma réponse au #10. a faire pour chaque TCD

Si tous les TCD sont bien basés sur la même plage, il se fondent sur le même cube de données et doivent donc s'actualiser en même temps : de toute façon le refresh all actualise tout.

Ci-joint ton fichier avec le nom modifié et le code VBA modifié.
Pour le copy j'ai mis en commentaire Destination... qui permet de copier le TCD et non les valeurs (enlever la mise en commentaire et la ligne en dessous si c'est le cas).

 

[FlorianQ]

Re : Insertion de TCD en VBA

Re,

Merci à toi Chris, on va voir si cela fonctionne sur la base et pour l'ensemble des données, je pourrais l'essayer demain en matinée.

Étrange pour le RefreshAll car avec une base vide ils devenaient tous vide, et lors de la ré-insertion de la base il ne se remplissaient pas tous...

Le copy ne permet d'exporter qu'un seul TCD ? Si oui il y a une synthaxe pour en exporter plusieurs ou bien faut-il mettre une ligne par tableau (sachant que je désire bien les exporter en TCD) ?

 

[chris]

Re : Insertion de TCD en VBA

Re

Une ligne par TCD est plus simple : avec destination tu indiques où doit aller chaque TCD.