Discussion :

[philoflore]

Bonjour à tous,

Objectif : Réduire la taille d'un fichier Excel qui contient des lignes avec des formats, données / validation, filtre et formule…

Contexte : Version Excel 2003. Le fichier contient une base de donnée de 3000 lignes et 56 colonnes qui augmente de jour en jour. Aujourd'hui la taille du fichier est de 12 Mo. Au final, cette base de données va contenir dans les 12000 lignes.

Première question : existe-il l'équivalent dans Excel de la commande Outil / Utilitaire de base de donnée / Compacter dans Access (qui est vraiment efficace) ?

Deuxième question : il y avait un tableau croisé dynamique qui a été supprimé. Y a-t-il en mémoire des éléments stockés qui n'on plus d'utilité et que l'on pourrait supprimer ?

Troisième question : existe-t-il une procédure, une fonction… de "nettoyage" vraiment efficace pour supprimer du code ou des éléments qui prennent de la place inutilement dans le fichier ?

Merci infiniment pour la, les réponses apportées.

[ouskel'n'or]

12 Mo pour 56 colonnes et 3000 lignes, ce n'est pas normal. Combien de feuilles, de graphes, de boutons, de lignes de code, d'images ?
N'as-tu pas passé ton fichier d'une version d'Excel dans une autre ? Cela se produit si un fichier créé sur 2003 est sauvegardé dans la version 1997. Idem avec PowerPoint.
Est-ce le cas ?

[Godzestla]

Salut
je me suis laissé dire que l'instruction suivante, exécutée 2 fois de suite faisait des miracles au niveau de la récupération d'espace libre du sheet sur lequel on l'effectue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
ActiveSheet.UsedRange
ActiveSheet.UsedRange

Cela me semble très étrange mais j'ai l'impression que cela fonctionne, alors que je ne comprends pas.

Ce qui donne sur tout le worksheet :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Sub Compact_All()
'
' Compact All Worksheets List Macro
'
    On Error Resume Next
 
    'Browse detailled Sheets
    For Sheet_number = 1 To Sheets.Count                'BFor10
        Sheets(Sheet_number).UsedRange
        Sheets(Sheet_number).UsedRange
    Next                                                'Efor10
End Sub

Est-ce un Hoax ? aucune idée. Moi je l'utilise.

Qu'en penses-tu ouskel'n'or ?

[fring]

Il arrive que l'on sélectionne toute une colonne, toute une ligne voir même toute une feuille et qu'on lui applique une mise en forme.
Une petite cure d'amaigrissement parfois efficace, consiste à supprimer toutes les lignes et les colonnes vides, Excel les remplacera automatiquement par des nouvelles. Ce qui permet de supprimer toutes traces de mise en forme qu'Excel garde en mémoire malgré que ces cellules ne sont pas utilisées.

[ouskel'n'or]

Bonjour,

Qu'en penses-tu ouskel'n'or ?

Je ne connaissais pas mais si ça fonctionne, philoflore nous le dira et la solution méritera de figurer dans Contribuez, voire dans la FAQ

[philoflore]

Merci pour toutes ces réponses.

Effectivement, avec un Edition / Effacer tout sur les lignes hors base de données, le fichier perd 6Mo !

Après avoir essayé le code plus haut "ActiveSheet.UsedRange" j'obtiens une réduction de 2 ko.

Je me demande s'il existe une procédure permettant de nettoyer ou de compacter le fichier Excel ?

Encore merci

[ouskel'n'or]

6 Mo (12 - 6) c'est encore trop pour le fichier que tu décris. A mon avis tu as un autre pb.
Je testerai une copie du contenu des feuilles sur un classeur tout neuf.
Tu te mets en A1 et tu fais Ctrl + A, tu copies et tu colles sur le nouveau classeur. Enregistre et vérifie.
Enfin, c'est ce que je ferais...
Pour les macros, on verra après mais j'ai un code de plus de 20000 lignes, 16 userforms, une douzaine de modules, etc, et la taille du classeur n'est "que" de 1, 8 Mo

[mayekeul]

bonjour,

petite question!

Utilise tu le format d'enregistrement multi versions?
genre "excel 95-2003" ?

[cafeine]

Hello,

une autre piste : essaie d'effacer les noms de ton classeur, ça peut significativement diminuer la taille.

[philoflore]

Merci à tous pour tous ces éléments de réponses.

Le fichier est bien enregistré au format xls.

Après nettoyage, la feuille de la base de données représente 2,5 Mo.

Dans le classeur, il y'avait des tableaux croisés supprimés et il y a d'autres feuilles qui contiennent des formules et des objets (zone de texte, rectangle, ellipse).

Voilà, je pensais qu'il y aurait des fonctionnalités pour nettoyer la base (c'est-à dire, supprimer des éléments qui ne sont plus utilisés…) ou compacter le fichier comme dans ACCESS.

Si vous avez une autre idée ?

[ouskel'n'or]

Ben moi je trouve que 2,5 Mo c'est encore beaucoup !

[philoflore]

Effectivement, il ya des formats (importants pour la lecture) de type bordure, couleur... et une quantité importante de cellules avec des données /validation comprenant des listes de valeur autorisées.

[ouskel'n'or]

Pour répondre à ta question (à part le zip ) je ne connais pas de méthode pour compacter un classeur Excel (95 - 2003 après je ne sais pas)

[philoflore]

Merci à tous.

[feboici]

ci-dessous une macro qui réduit le fichier, mais vérifier après si tt ok :
PS : elle n'est pas de moi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
Sub Nettoie()
Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String, Avant As Double, plage As Range
On Error Resume Next
Calc = Application.Calculation ' ---- mémorisation de l'état de
recalcul
'------------------------------------------------------------
MsgBox "Pour le classeur actif : " _
& Chr(10) & ActiveWorkbook.FullName _
& Chr(10) & "dans chaque feuille de calcul" _
& Chr(10) & "recherche la zone contenant des données," _
& Chr(10) & "réinitialise la dernière cellule utilisée" _
& Chr(10) & "et optimise la taille du fichier Excel", _
vbInformation, _
"d'après LL par <a href="mailto:GeeDee@m6net.fr">GeeDee@m6net.fr</a>"
'-------------------------------------------------------------
MsgBox "Taille initiale de ce classeur en octets" _
& Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
'------------------------------------------------------------
With Application
.Calculation = xlCalculationManual
.StatusBar = "Nettoyage en cours..."
.EnableCancelKey = xlErrorHandler
.ScreenUpdating = True
End With
'-------------------- le traitement
For Each Sht In Worksheets
Avant = Sht.UsedRange.Cells.Count
Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address
'-------------------Traitement de la zone trouvée
If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
'----------------Suppression des lignes inutilisées
If Not DCell Is Nothing Then
Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
Set DCell = Nothing
Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
'----------------Suppression des colonnes inutilisées
If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
End If
Rien = Sht.UsedRange.Address
End If
ActiveWorkbook.Save
'---------------------Message pour la feuille traitée
MsgBox "Nom de la feuille de calcul :" _
& Chr(10) & Sht.Name _
& Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") & " de la taille initiale", _
vbInformation, ActiveWorkbook.FullName
Next Sht
'--------------------Message fin de traitement
MsgBox "Taille optimisée de ce classeur en octets " & Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, _
ActiveWorkbook.FullNameActive
'--------------------
Application.StatusBar = False
Application.Calculation = Calc
End Sub

[brutus437]

LORSQUE J'essaie d'utiliser cette macro, cela supprime bcp de données dans mes tables comportants des zones de cellules nommées (mais pas toute, pourquoi?) la taille est réduite de moitié mais inexploitable après car un nombre de plages nommées on disparues

[Patrice740]

Bonjour,

Essaies avec cette macro :

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Option Explicit
Sub Nettoie()
Dim wsh As Worksheet
Dim rng As Range
Dim dL As Long
Dim nL As Long
Dim dC As Long
Dim nC As Long
Dim i As Long
  If MsgBox("Nettoyer " & ActiveWorkbook.Name & " ?", vbOKCancel + vbDefaultButton2) <> vbOK Then Exit Sub
  For Each wsh In ActiveWorkbook.Worksheets
    With wsh
      Set rng = .UsedRange
      dL = rng.Row + rng.Rows.Count - 1
      ' Chercher la dernière colonne
      For i = 1 To dL
        nC = .Cells(i, .Columns.Count).End(xlToLeft).Column
        If nC > dC Then dC = nC
      Next i
      ' Supprimer les colonnes vides
      .Range(.Columns(dC + 1), .Columns(.Columns.Count)).Delete
      ' Chercher la dernière ligne
      dL = 0
      For i = 1 To dC
        nL = .Cells(.Rows.Count, i).End(xlUp).Row
        If nL > dL Then dL = nL
      Next i
      ' Supprimer les lignes vides
      .Range(.Rows(dL + 1), .Rows(.Rows.Count)).Delete
    End With
  Next wsh
End Sub