Reduire la taille de mon fichier

[frusciantefan]

Bonjour,

Je commence a être embêté par un problème de taille... de fichier!
Je suis embêté car je ne peux pas partager mon fichier en question (données sensibles et confidentielles) mais je vais tenter de vous exposer clairement mon problème :

Je consolide le suivi des effectifs au niveau France. J'ai donc deux bases de données a ma disposition :

Les données actuelles (un peu moins de 2000 lignes environ)
Les données forecastées.(moins de 1000 lignes pour les 3 bases)

Les données actuelles sont une extractions des BDD de la société, qui regroupent 3 sites.
Les données forecastées sont des data manuelles, splittées par 3 (un forecast par site)


J'ai donc un onglet SITE1 tableau croisé dynamique (1TCD qui va chercher les données actuelles du site + 1 TCD qui va chercher les données forecastées du site. Idem pour onglet SITE2 et onglet SITE3. En faisant la somme des données actuelles + des forecasts je sais prédire a telle date combien de personnes sont dans la société.

En plus de ca, j'ai un onglet conso France, qui va chercher toujours les données actuelles + une concaténation des data manuelles de chaque site. (sous forme de copier/coller valeurs)

Le but premier étant de pouvoir double cliquer sur les chiffres des TCD pour accéder aux listes des noms concernés.

Je ne comprends pas comment mes fichiers peuvent atteindre des tailles de 20 à 50 Mo !!! Alors que la base actuelle reste la même (même si je duplique les TCD) et que je vais chercher 3 autres base de données forecastées.

Du coup, je perds un temps monstrueux :

- a l'ouverture
- à la fermeture
- à l'actualisation
- à l'affichage
- à l’enregistrement

J’imagine que les histoire de cache de TCD sont la cause, mais comment puis faire diminuer considérablement la taille de mes fichiers en question sans perdre aucune données?

Merci pour votre aide, cela devient un peu critique... Je souhaite conserver la forme de mon fichier car elle répond aussi a une demande opérationnelle.

FF

 

[JCGL]

Re : Reduire la taille de mon fichier

Bonjour à tous,

Je pense que tu dois prendre des colonnes entières pour ta base de données pour le TCD.

Peux-tu essayer en nommant cette plage par "Base_TCD" et comme formule (la feuille qui contient les données s'appelle "Data") :


A+ à tous

Edition : Colle ce code dans un module de ton fichier et lance le :

Option Explicit


Sub DeleteOldItemsWB()
'Effacer les Items non utilisés dans un TCD
'Debra Dalgleish - Basé sur MSKB (202232)
Dim WS As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Dim i As Integer
On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
    For Each PT In WS.PivotTables
        PT.RefreshTable
            For Each PF In PT.PivotFields
                For Each PI In PF.PivotItems
If PI.RecordCount = 0 And Not PI.IsCalculated Then PI.Delete
Next
    Next
        Next
            Next
End Sub

 

[frusciantefan]

Re : Reduire la taille de mon fichier

Merci JCGL !

J'ai vérifié, et j'ai redéfini les colonnes pour le TCD donc je ne prends pas toutes les lignes. Je dois laisser seulement 10 lignes vides pour être sur de ne rien oublier.

J'ai copié collé le code VBA dans mon fichier, il se passe quelque chose mais la taille du fichier n'est pas affectée : 50Mo...

Help

 

[Efgé]

Re : Reduire la taille de mon fichier

Bonjour frusciantefan Bonjour JC ,

Une idée:
Supprime toutes les lignes et colonnes vides de toutes tes feuilles puis utilise ceci :

Sub tentative()
Dim ThF As Worksheet, F As Worksheet
Set ThF = ActiveSheet
Application.ScreenUpdating = False
For Each F In Worksheets
    F.Activate
    ActiveSheet.UsedRange
Next F
ThF.Activate
End Sub

Ca m'a déja sauvé.
Cordialement

 

[frusciantefan]

Re : Reduire la taille de mon fichier

Salut Efgé

Ca a fonctionné !!
J'ai supprimé toutes les lignes et colonnes puis lancé la macro !

Resultat, je suis passé de 50Mo à moins d'1Mo !!

Peux tu m'expliquer clairement ce que cette petite macro fait?
Pas de risque que j'explose mes données?

FF

 

[Efgé]

Re : Reduire la taille de mon fichier

Re
En fait quand tu delete des lignes ou des colonnes, Excel a tendance à garder des "traces" des données supprimées.
Le code ne fait qu'une chose importante;

ActiveSheet.UsedRange

Cette simple instruction oblige Excel à recalculer son range..
Pas de danger puisque c'est toi qui supprime les lignes et colonnes
Cordialement

 

[frusciantefan]

Re : Reduire la taille de mon fichier

Genial, je le garde bien précieusement... Merci !

 

[job75]

Re : Reduire la taille de mon fichier

Bonjour frusciantefan, Jean-Claude, Efgé

Efgé ta macro est très bien quand on veut recalculer le UsedRange sans enregistrer le fichier.

Pour rafraîchir les barres de défilement en particulier.

Mais ici, après suppression des lignes et colonnes excédentaires, il suffisait d'enregistrer le fichier.

A+

 

[Efgé]

Re : Reduire la taille de mon fichier

Re à Tous, Bonjour Job
En fait, si on regarde ce fil de Cathodique il semble que l'enregistrement du fichier n’entraîne pas nécessairement le recalcule des ranges.
Premier post, le fichier exemple fait 121 Ko.
Je l'enregistre sur mon bureau, je monte ma proposition en ajoutant un bouton, j'enregistre les changements.
Au post 5 il fait 128 Ko
Une précision demandée m’amène à reprendre le fichier. Je m’aperçois que son poids ne correspond pas aux données présentent. En faisant Ctrl + Maj + Fin on vois que des lignes ont été supprimées.
Je (re) supprime les lignes et colonnes vides, je l'enregistre et constate que son poids reste identique....
Je craque et applique le code magique du ActiveSheet.UsedRange.
Résultat Post 8 : 48Ko

Je pense donc qu'Excel en fait un peu à sa tête

Cordialement

EDIT
J'irais même plus loin : Le UsedRange est d'autant plus efficace quand le classeur n'a pas été enregistré après les suppressions.
Il serait intéressant de faire un test, si frusciantefan repasse par ici : reprendre son fichier de 50Mo supprimer les lignes / colonnes, enregistrer et voir le poids obtenu.
Amicalement

 

[MJ13]

Re : Reduire la taille de mon fichier

Bonjour à tous

Merci Efgé pour ce code, qu'il faudra que je teste. Ce problème arrive souvent quand on fait des demandes de types ODBC.

L'idée serait de faire une macro qui teste chaque colonne remplie et supprime sur chaque feuille du classeur les lignes et les colonnes en trop.

 

[Efgé]

Re : Reduire la taille de mon fichier

Re à tous Bonjour MJ

@MJ
Pour les requêtes ODBC, il me semble toujours préférable d'utiliser un modèle (Type .xlt, .xltx, .xltm)
A la création du modèle tu limite la requête pour n'avoir que très peux de données (par exemple pour une importation de date tu limite à aujourd’hui )
Ensuite ton fichier n'aura que le poids de la première requête.
Comme tu devras l'enregistrer sous un autre nom, tes classeurs seront toujours plus légers.

Cordialement