Fichier Excel très lent

Bonjour,

J'ai un fichier Excel extrêmement lent (quelques minutes pour une mise à jour). Ce fichier fait partie d'une application comptabilité avec d'autres fichiers de structure similaire. Il n'a rien de spécial : 6 onglets, pas de formules compliquées (uniquement des sommes), pas de formats exotiques (formats date, monnaie) mais certains sont personnalisés. J'ai essayé pas mal de choses, suivi des conseils trouvés sur le web et dans les forums (la plupart ne s’appliquent pas car ce n'est pas Excel qui est lent mais ce seul fichier). Je suis reparti d'un fichier de structure similaire et qui n'a pas ce problème de lenteur et fait des copié-collé des données. Les 3 premiers onglets se passent bien mais le problème revient au 4e onglet. Je suis à court d'idées.

Merci de votre aide

Cdlt

Mike

Hello,

Impossible à diagnostiquer sans le fichier. A minima et vu de très très loin : donne une réponse ou capture d'écran des

1/ paramètres du fichier (lenu fichier > informations) : extension du fichier + taille sur le disque

2/ éléments présents dans le gestionnaire de nom (onglet formules du tableur)

3/ éléments présents dans les mises en forme conditionnelles (onglet accueil du tableur) en sélectionnant dans le menu déroulant "cette feuille de calcul" et ce pour chaque onglet

4/ vérification des éventuels codes présents dans le fichier : appuyer sur alt + F11 et double cliquer dans les éléments encadrés en rouge pour voir si présence de lignes de code. Voir illustration suivante.

Merci

Le mieux est de joindre le fichier

Merci de ton aide

Mike

Bonjour,

Il y a 50 160 objets masqués dans le fichier, dont :

CVIMI : 114 objets
CVIGI : 1254 objets
CLAGI : 4332 objets
CLAMI : 12084 objets
PELMI : 12084 objets
PELGI : 20292 objets

Pour les sélectionner, onglet par onglet, cliquer sur la touche F5, puis sur "Cellules", et enfin "Objets"

Tous les objets (masqués) vont être sélectionnés. Reste plus qu'à appuyer sur le bouton "Suppr" pour les supprimer (sauf si tu veux les conserver...)

Bon courage

Re-,

Le fichier nettoyé

Bonne soirée

Bravo cousinhub j'avais pas du tout pensé aux objets !

On sait jamais si ça peut intéresser un promeneur, on a parfois la sensation d'avoir un tableur lourdement rempli de vide (par exemple après de grosses et indélicates manipulations de plages)... ceux qui ont déjà rencontré ce souci comprendront l'idée

Fonction de compactage des données :

Option Explicit
Sub compactage(Optional msg As Boolean = True)
' Objectif : Compactage des données

Dim ws As Worksheet, Rng As Range
Dim nbCellulesAvant As LongPtr, octet As LongPtr
Dim lastrow As LongPtr, lastcol As LongPtr
Dim temp As String

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationmanual
    .EnableEvents = False
    .Cursor = xlWait
    .DisplayStatusBar = True
    .StatusBar = "Compactage en cours..."
End With

With ActiveWorkbook
    octet = FileLen(.FullName)

    ' Boucle sur chaque feuille
    For Each ws In .Worksheets
        ' Ignorer les feuilles protégées et vides
        With ws
            If .ProtectContents = False And Application.WorksheetFunction.CountA(.Cells) > 0 Then
                Set Rng = .UsedRange
                nbCellulesAvant = Rng.Cells.Count

                ' Déterminer la dernière colonne utilisée
                On Error Resume Next
                lastcol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
                If Err.Number <> 0 Then lastcol = 1
                On Error GoTo 0
                .Range(.Columns(lastcol + 1), .Columns(.Columns.Count)).Delete

                ' Déterminer la dernière ligne utilisée
                On Error Resume Next
                lastrow = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
                If Err.Number <> 0 Then lastrow = 1
                On Error GoTo 0
                .Range(.Rows(lastrow + 1), .Rows(.Rows.Count)).Delete

                ' Rapport de compactage par feuille
                If Not (.UsedRange.Cells.Count / nbCellulesAvant) = 1 Then
                    temp = temp & Chr(10) & .Name & " : " & _
                           Format(.UsedRange.Cells.Count / nbCellulesAvant, "0.00%") & " de la taille initiale"
                End If
            End If
        End With
    Next ws

    ' Message final
    If msg Then
        MsgBox "Taille de ce classeur en octets" & Chr(10) & _
               "avant = " & FileLen(.FullName) & " ; après = " & octet & Chr(10) & _
               temp, vbInformation, .FullName
    End If
End With

' Rétablir les paramètres d'Excel
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .Cursor = xlDefault
    .StatusBar = False
End With

End Sub

Lancer via procédure d'appel quelconque :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call compactage(False) 'true pour rapport de compactage
End Sub

Bonjour,

Moins gourmand peut être mais il faut de temps à autre vérifier où se trouve la vraie dernière cellule occupée ( ou qui l'a été), on est parfois surpris de voir qu'il n'y a rien en AB1233 alors que CTRL END nous y emmène :)

Le grand maitre LAURENT LONGRE a résolu ça il y a plus de 20 ans par un code vba que j'ai encore probablement.

P.

Bonjour

Un grand merci à tous, à cousinhub et tomato qui ont mis le doigt sur mon problème et l'ont résolu mais aussi à Patrick qui a rappelé Laurent Longre qui animait des forums à mes débuts sur Excel. Il faut que je me penche sur mon code qui doit être à la source de ce souci.

Par contre, j'aurais bien aimé comprendre. Quand je fais la manip (cliquer sur la touche F5, puis sur "Cellules", et enfin "Objets"), je ne vois rien. La sélection initiale part je ne sais où. Je supprime alors tous les objets avec Supp. Cousinhub, comment as tu pu quantifier le nombre d'objets ? Comment pourrais voir la nature de ces objets afin que je trouve la partie du code qui les a créé ?

Autre point que je ne comprends pas : quand je suis reparti d'un classeur "sain" (j'ai vérifié sans objets cachés) et fait un copié collé uniquement de la sélection utile, j'aurais du faire le ménage ? En sélectionnant les données, j'embarque donc ces objets.

Encore un grand merci

Cdlt

Mike

Bonjour,

Tout d'abord, lorsque j'ouvre un fichier comportant ce type d'anomalies, je vérifie la structure en suivant les quelques recommandations énoncées dans ce tuto.

Pour obtenir le nombre d'objets, j'ai utilisé ce code :

Sub clean_epar()
Dim Sh As Worksheet
Dim Nb_Shape As Long
For Each Sh In Sheets
    Nb_Shape = Nb_Shape + Sh.Shapes.Count
    Debug.Print Sh.Name & " : " & Sh.Shapes.Count & " objets"
Next Sh
Debug.Print "Nombre total d'objets : " & Nb_Shape
End Sub

Et voici le résultat :

Pour la manip "Touche F5", il faut tout d'abord qu'il n'y ait qu'une seule cellule sélectionnée. Puis, en fonction du nombre d'objets, la sélection peut être assez longue, et au final, on peut apercevoir les marques de sélection des objets (notamment à gauche, en haut).

Et aussi, en fonction du nombre d'objets, Excel peut planter lors de la demande de suppression. Il faut alors les supprimer 1 à 1, par un code VBA :

Sub suppr_objets()
Dim Sh As Worksheet
Dim Shp As Shape
For Each Sh In Sheets
    For Each Shp In Sh.Shapes
        Shp.Delete
    Next Shp
Next Sh
End Sub

Attention, ce code supprime tous les objets!

Sur ton fichier, ce code a mis une trentaine de secondes pour les supprimer.

Et enfin, ce nombre incongru d'objets provient généralement de l'utilisation d'un fichier depuis longtemps, où il y a eu des objets, qui ont pu être masqués par des masquages de lignes et/ou colonnes, et dont on a oublié l'existence. De multiples copier/coller ont fait leurs œuvres, et on se retrouve avec ce type de désagréments.

Si tu n'as pas tout compris, n'hésite pas.

Bonne apm

C'est très clair

Un grand merci

Mike