Comment réduire le poids d'un fichier Excel?

[nicopat]

Bonjour,

Mon fichier .xlsm (XL 2007) contient 100 feuillets.
Certains feuillets ont un contenu très sommaire.
Quelques feuillets ont un contenu très complexe : par exemple, le + gros feuillet contient 230.000 cellules contenant des formules plus ou moins complexes.
Mon fichier contient bon nombre de macros, mais je ne crois pas que cela pèse.

Mon objectif est d'optimiser la vitesse de fonctionnement des macros, ce qui me semble être passer par l'allégement du poids du fichier.
Je veux alléger au maximum le poids de mon fichier pour accélérer le fonctionnement des macros (actuellement, je suis contraint de fonctionner sous mode "calcul sur demande F9", sinon ça rame trop).

J'ai opéré une cure d'amaigrissement pour réduire la taille de mon fichier :
- suppression des cellules lignes et colonnes vides (gain important)
- raccourcissement des noms des feuillets (compte tenu du très grand nombre de formules cross-feuillets, ça m'a presque fait gagner 2MO !)
- création d'un nouveau fichier excel et copie de tous les feuillets vers le nouveau fichier excel, ce qui n'a absolument rien fait gagner.


Actuellement, mon fichier .xlsm pèse 43 MO.

Mes questions :
1. Y a-t-il d'autres moyens que ceux énumérés ci-dessus pour alléger un fichier excel?
2. J'ai notamment lu que supprimer les mises en forme graphiques (bordure, couleurs, etc...) pouvait être utile, mais les premiers tests que j'ai réalisés sur certains feuillets ont contredit cette thèse. Quelqu'un en sait-il +?
3. J'observe qu'en changeant de format de fichier et en passant en .xlsb (fichier binaire), le poids du fichier passe à 30 MO, mais je ne sais pas si les performances des macros seront accrues, et je ne connais pas les implications de ce changement de format.
4. à part cela à quoi pensez-vous pour accélérer des macros?


Voili voilou, je crois que j'ai donnée toutes les indications nécessaires, mais si vous avez des questions, n'hésitez-pas.

Merci

Nicole

PS : je n'ai pas de macro matricielle dans mon fichier.

 

[Paritec]

Re : Comment réduire le poids d'un fichier Excel?

Bonsoir Nicopat le forum
Concernant tes macros comment veux tu que l'on te réponde?? il est ou le fichier?
de quel type de macro tu parles?? que traitent tes macros?? si tu travailles sur les feuilles directement le fait de passer par des tableaux virtuels va te faire diminuer le temps par en moyenne 20 fois !!!!
Mais encore une fois sans fichier!!!!! ????????????????
Pour le gain de poids tu peux simplement tester d'ouvrir un nouveau classeur et de sélectionner que les cellules remplies de tes feuilles et de les coller dans le nouveau classeur en cellule A1 de chaque feuilles, moi j'ai déjà vu passer de 10MO à 1 MO en faisant cela.
Tu peux aussi éviter tout ce qui est formule, mise en page coloré, mise en forme conditionnelle etc
tu peux aussi éviter tous les liens vers d'autre fichier.
Il n'en reste pas moins vrai qu'un classeur excel avec 100 feuilles est une ânerie Monumentale.
bonne soirée
Papou

 

[grisan29]

Re : Comment réduire le poids d'un fichier Excel?

bonjour a tous

doublure de post

pascal

 

[Herdet]

Re : Comment réduire le poids d'un fichier Excel?

Bonjour ,
Paritec : salut l'ami Papou, toujours en pleine forme à ce que je vois.

Nicole :
Ma prose ci-après rejoint pleinement ce que vient de dire Paritec.... sous une autre forme !
Vaste problème récurent que cette lenteur d'exécution d'Excel ou du VBA et j'espère pour toi que les "cadors" de XLD vont pouvoir te transmettre leurs astuces ou règles de programmation.
Sans fichier d'exemple à tester, c'est un peu plus aléatoire à résoudre mais bon, allons-y.

Il y a quelques procédures à mettre en place que tu as peut être déjà intégrées :
- bloquer l'affichage pendant l'exécution : Application.ScreenUpdating = False et Application.ScreenUpdating = True à la fin ou avant l'affichage de Msgbox
- bloquer/débloquer le calcul : Application.Calculation = xlCalculationManual et Application.Calculation = xlCalculationAutomatic
- formules dupliquées sur plusieurs milliers de lignes :
Elles sont consommatrices de beaucoup de temps de calcul et très souvent,elles peuvent être remplacées par des valeurs
Solution : Créer une formule en VBA, calculer ( ou non), copier et coller les valeurs à la place des formules
exemple ( utilisé dans une boucle sur 45000 lignes par 18 colonnes d'une de mes applications)

   '  ajout formule, copie des valeurs
   LiCh = Range("BASE_Champs")                           ' ligne des champs du tableau
   Set aa = Cells(LiCh, 1).CurrentRegion                 ' base avec champs
   Set Dest = aa.Offset(1, 0).Resize(aa.Rows.Count, 1)   ' base sans les champs
   Dest.FormulaR1C1 = "=INDEX([" & BASE_MOD_nom & "]DATA_SOURCE!C7,MATCH(RC[1],[" & BASE_MOD_nom & "]DATA_SOURCE!C8,0))"
   Dest.Copy
   Dest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False

- utiliser au maximum des tableaux déclarés en VBA et traités en VBA plutôt que des milliers de formules

- essayer de découper le fichier source de 43 Mo en plusieurs fichiers et de réduire le nb de feuilles
Suivant le besoin, faire des procédures pour les ouvrir, copier les valeurs dans des tableaux VBA et les refermer
(à la limite importer des feuilles complètes sans ouvrir les fichiers de données.

- limiter la quantité de formats différents pour les 100 feuilles
Cette zone de ressources d'Excel est limitée même avec 8 Go de RAM
Proscrire totalement les fusions de cellules à remplacer par "Centrer sur plusieurs colonnes"

- Optimiser le nombre de feuilles à réduire en regroupant les tableaux dans moins de feuilles

- .....

A suivre
Robert

 

[nicopat]

Re : Comment réduire le poids d'un fichier Excel?

Bonsoir Nicopat le forum
Concernant tes macros comment veux tu que l'on te réponde?? il est ou le fichier?
de quel type de macro tu parles?? que traitent tes macros??

Bonjour PAritec,

Merci pour tes réponses.
Les macros sont bien le fonds de ma préoccupation, mais ce post concerne l'allègement du fichier (je n'exclue pas de créer un post prochainement spécifiquement sur l'accélération des macros).
Je ne peux pas joindre le fichier car il contient des données confidentielles que je n'ai pas le droit de communiquer.

Il n'en reste pas moins vrai qu'un classeur excel avec 100 feuilles est une ânerie Monumentale.

C'est un fichier ultra complexe avec bcp de calculs. Donc de nombreuses pages sont nécessaires pour conserver une organisation du contenu. Je peux essayer de réduire ce nombre de feuillets en regroupant du contenu, mais est-il avéré que j'obtiendrai un gain de performances?

Pour le gain de poids tu peux simplement tester d'ouvrir un nouveau classeur et de sélectionner que les cellules remplies de tes feuilles et de les coller dans le nouveau classeur en cellule A1 de chaque feuilles, moi j'ai déjà vu passer de 10MO à 1 MO en faisant cela.
Tu peux aussi éviter tout ce qui est mise en forme conditionnelle etc
tu peux aussi éviter tous les liens vers d'autre fichier.

J'ai déjà fait tout ça, sans résultat notable.

J'ai lu dans quelques threads que supprimer les mises en forme graphiques (bordure, couleurs, etc...) pouvait être utile pour alléger un fichier, mais les premiers tests que j'ai réalisés sur certains feuillets ont contredit cette thèse. Quelqu'un en sait-il +?

 

[nicopat]

Re : Comment réduire le poids d'un fichier Excel?

si tu travailles sur les feuilles directement le fait de passer par des tableaux virtuels va te faire diminuer le temps par en moyenne 20 fois !!!!

Paritec, je ne connais pas les tableaux virtuels : kézako?

 

[nicopat]

Re : Comment réduire le poids d'un fichier Excel?

Bonjour Herdet,

Merci pour tes conseils.
Je vais tester ce que tu m'as indiqué, dans la limite de mes compétences.

utiliser au maximum des tableaux déclarés en VBA et traités en VBA plutôt que des milliers de formules

Kezako?

limiter la quantité de formats différents pour les 100 feuilles
Cette zone de ressources d'Excel est limitée même avec 8 Go de RAM
Proscrire totalement les fusions de cellules à remplacer par "Centrer sur plusieurs colonnes"

Les formats que tu évoques, ce sont les formats graphiques?
Par exemple, je dois proscrire les bordures, les fonds de cellules en couleur, les couleurs de police, etc...
Cela est censé réduire le poids du fichier ou la vitesse d'éxécution des macros?
J'ai déjà fait le test de supprimer toute fioriture graphique sur le + gros feuillet du fichier et cela n'a rien changé au poids du fichier.

Optimiser le nombre de feuilles à réduire en regroupant les tableaux dans moins de feuilles

Cela est censé réduire le poids du fichier ou la vitesse d'éxécution des macros?

 

[Modeste geedee]

Re : Comment réduire le poids d'un fichier Excel?

Bonsour®

Mon objectif est d'optimiser la vitesse de fonctionnement des macros, ce qui me semble être passer par l'allégement du poids du fichier.
Je veux alléger au maximum le poids de mon fichier pour accélérer le fonctionnement des macros (actuellement, je suis contraint de fonctionner sous mode "calcul sur demande F9", sinon ça rame trop).

on parle de volumétrie ou de performance ?

- extérioriser les résultats ou données peu volatiles.

Je ne peux pas joindre le fichier car il contient des données confidentielles que je n'ai pas le droit de communiquer.

ce sont les données ou bien le process qui est confidentiel ?

- revoir l'organisation de l'appli : y a-t-il lieu de recalculer l'ensemble à chaque intervention ???
- scinder l'application en modules fonctionnels
- traiter les calculs des tableaux en mémoire (VBA en place de formules)