Augmentation taille fichier par modification de ms query

[euskaran]

Bonjour à vous,

Dans un cadre professionnel, j'ai créé un fichier excel comportant plusieurs tcd (lié en ms query sur des requêtes access) dans différents onglets afin de piloter notre activité mensuelle.

Chaque début de mois, je créé un nouveau fichier et modifie chaque lien ms query afin que les tcd ciblent les requêtes du mois suivant (en remplacement au mois précédent). Tout cela fonctionne correctement hormis le fait que le fichier excel s'alourdit de plus en plus au fil des mois (passant de 14 mo en janvier à plus de 200mo en décembre!).

J'ai tenté d'utiliser diverses macros type karcher ou bien SupprimerElementsAbsents mais rien ne modifie la taille.

Merci par avance de votre aide,

 

[Guest]

Re : Augmentation taille fichier par modification de ms query

Bonjour,

il se peut que cela vienne des 'Caches' des TCD qui avec le temps augmente en taille.
Il faudrait peut-être leur appliquer la méthode .Refresh

Exemple MS de l'aide excel
Worksheets(1).PivotTables(1).PivotCache.Refresh
A+

 

[euskaran]

Re : Augmentation taille fichier par modification de ms query

Bonjour Hasco,

J'ai déjà essayé la macro ci dessous demandant un refresh à chaque tcd mais le résultat est quasi nul. Je pense qu en effet qu'il s'agit du cache tcd qui cumule les infos des précédentes version mais la méthode ci dessous ne donne rien.

Sub SupprimerElementsAbsents()
Dim tcd As PivotTable
Dim feuille As Worksheet

For Each feuille In ActiveWorkbook.Worksheets
For Each tcd In feuille.PivotTables
tcd.PivotCache.MissingItemsLimit = xlMissingItemsNone
tcd.PivotCache.Refresh
Next tcd
Next feuille

End Sub

 

[Guest]

Re : Augmentation taille fichier par modification de ms query

Re,

Normalement un upDate après le refresh:

Sub SupprimerElementsAbsents()
Dim tcd As PivotTable
Dim feuille As Worksheet

For Each feuille In ActiveWorkbook.Worksheets
For Each tcd In feuille.PivotTables
tcd.PivotCache.MissingItemsLimit = xlMissingItemsNone
tcd.PivotCache.Refresh
tcd.UpDate
Next tcd
Next feuille

Sinon je ne peux rien te dire de plus, si ce n'est de recommencer dans un classeur vierge en n'oubliant pas de nettoyer les PivotCache avant de lancer les Queries.

bon courage.
A+

 

[euskaran]

Re : Augmentation taille fichier par modification de ms query

Bonjour Hasco,

J'ai incorporé le tcd.update à la macro mais cela ne change malheureusement rien. Je tente de trouver une solution pour justement éviter de refaire l'ensemble des onglets mensuellement car l'édition de ce rapport me prend plusieurs heures.

Je pensais que ce problème d'augmentation de la taille du fichier en raison du changement de source était beaucoup plus fréquent mais apparemment non. . N'y a-t-il pas une option dans excel permettant de centraliser la source de tous les TCD?

Merci encore de votre aide

 

[Bebere]

Re : Augmentation taille fichier par modification de ms query

bonjour Euskaran,Hasco
trouvé sur le net
'
'Disparition de champs dans un TCD
'Malgré la mise à jour de mes TCDs, je n'arrive pas (sans
'refaire entièrement le TCD) à supprimer dans mon tableau d'anciennes données
'qui ont disparu de ma base. Ces données n'existent plus dans la source, mais
'apparaissent dans mes boutons champs. On dirait qu'Excel garde en mémoire
'des données supprimées.
'Je confirme ton problème sous Excel2000, je n'avais pas ce problème sous
'Excel95. Je pense qu'on peut conlure à un bug.
'J 'ai trouvé un truc pour ne pas refaire tout le tableau:
'
'-tu renommes le champ problématique dans ta feuille source (disons
'"name" au lieu de "nom" comme en-tête de ta colonne de noms)
'-tu fais la mise à jour du TCD => un avertissement te dis que le tcd a
'été modifié, et le champ "nom" disparaît
'-tu renommes le champ "name" par "nom" dans la zone de données source
'-tu modifies le tcd pour lui ajouter le champ manquant: les valeurs
'fantômes ont disparues!
'
'Cet "électrochoc" force le tcd à recalculer les valeurs distinctes des
'champs. Elle est plus rapide que de tout recréer le tableau. Quant à la
'suppression du champ "nom" puis son ajout (je suppose que tu avais
'essayé), ça ne donne rien: Le tcd se "souvient" des valeurs fantômes!
'
'Si L 'opération doit être répétée fréquemment, l'écriture d'une macro
'"électrochoc" peut être envisagée.
'
'(ceci semble corrigé dans excel XP)Laurent Mortézai

 

[euskaran]

Re : Augmentation taille fichier par modification de ms query

Bonjour Bebere,

La problématique c'est qu'en changeant à chaque fois le nom des champs dans les TCD, je mettrai quasiment autant de temps qu'en refaisant l'outil de pilotage en entier. De plus, si excel XP est excel 2003, c'est la version sur laquelle je travaille donc ca devrait être corrigé théoriquement?

 

[Guest]

Re : Augmentation taille fichier par modification de ms query

Re,

Excel xp = excel 2002, pas 2003

Euskaran, il y a peut-être aussi des choses à revoir dans les Queries. Mais sans fichier c'est difficile à dire.

A+

 

[Bebere]

Re : Augmentation taille fichier par modification de ms query

je me fais la même réflexion que Hasco au sujet de query
exemple dédié une feuille au query et faire les tcd à partir de cette feuille
en pièce jointe une solution de Myta

 

[euskaran]

Re : Augmentation taille fichier par modification de ms query

J'ai effectué une tentative de changement de noms comme ton exemple Bebere mais au vu du nombre de tableaux et de données, cela va me prendre trop de temps.
Lorsque vous dites qu'il y a des choses à revoir dans les queries à quoi pensez vous?

PS: au vu de la taille du fichier et surtout de la confidentialité des datas je ne peux malheureusement vous le transmettre

 

[Bebere]

Re : Augmentation taille fichier par modification de ms query

un extrait de fichier est bon et ce qui est confidentiel,tu mets des données bidons
et une solution par macro est ce possible pour toi(query y compris)

 

[euskaran]

Re : Augmentation taille fichier par modification de ms query

Lorsque je modifie le query, je me demande si je ne fais pas mal quelque chose:

-Tout d'abord, je vais dans l'assistant pour retourner dans l'option obtenir les données.
-Ensuite je retire l'exhaustivité des champs pour rajouter la version du mois suivant
-Je ne choisis aucun filtre puis utilise mon premier champ en clé primaire
- A la fin de la requête je choisis afficher les données ou modifier la requête dans MS Query puis je sauvegarde sous le nom du fichier la requête.

Pou chaque TCD du fichier (il y en a 10), je fais la même manip en sauvegardant sur le même nom de fichier la requête.

Pour la macro, cela ne me pose pas problème mais que peut on y mettre?

 

[Bebere]

Re : Augmentation taille fichier par modification de ms query

bonjour Euskaran,Hasco
par macro tu peux interroger(query) les tables access
mettre le résultat dans une feuille,ensuite mettre à jour les tcd
pour cela il faut des renseignements que tu dois nous fournir
pas le temps maintenant je dois partir .
en pièce jointe une macro à mettre dans xlstart
ensuite dans macro complémentaires,parcourir,etc
avant d'exécuter la macro tu regardes la taille de ton fichier
pour voir la différence après
j'ai fait l'essai sur plusieurs fichiers et gagné de 6 à 9 M(taille fichier +/-2M)

 

[euskaran]

Re : Augmentation taille fichier par modification de ms query

Bonjour Bebere,

J'ai bien installé la macro mais le fichier ne baisse pas du tout de taille. Est ce dans la définition du query que ça pêche ( que j'ai détaillé dans mon post précédent)?

 

[Bebere]

Re : Augmentation taille fichier par modification de ms query

Euskaran
supprime cette macro,elle n'est pas bonne
ce matin fichiers excel illisible
comment veux tu que l'on te réponde,avec ce que tu mets