Base de connaissances KB0204 

Certains fichiers Excel sont ralentis avec les liaisons de données

Problème

Si un fichier Excel contient des liaisons de données Excel (voir 21. Liaisons de données Excel) et un nombre trop important de styles de cellules, il peut se mettre à défiler très lentement et Excel peut paraître peu réactif.

Ce problème peut survenir si le fichier Excel contient plus de 3000 styles de cellules environ. Vous pouvez vérifier le nombre de styles de cellule grâce à la commande VBA suivante dans la Fenêtre Exécution :

print Activeworkbook.Styles.Count

Pour ce faire, procédez comme suit :

  1. Ouvrez l'éditeur VBA en appuyant sur Alt+F11.
  2. Accédez à Fenêtre d'exécution. Si elle n'est pas visible, vous pouvez l'afficher en cliquant sur AffichageFenêtre d'exécution.
  3. Saisissez la commande suivante et appuyez sur Entrée
    print Activeworkbook.Styles.Count

Solution : Nettoyez tous les classeurs concernés

  • Copiez vos feuilles de calcul dans un nouveau classeur

    Dans Office 2013 et ses versions ultérieures, le fait de copier les feuilles de calcul entre les classeurs permet d'éviter l'accumulation par défaut de styles de cellules inutilisés :

    • Sélectionnez toutes les feuilles de votre classeur, maintenez la touche SHIFT enfoncée et cliquez sur la première et la dernière des feuilles de calcul
    • Effectuez un clic droit sur la sélection : Déplacer ou copier…Vers le classeur : (nouveau classeur) et cochez Créer une copieOK
    • Enregistrez un nouveau classeur sous un nouveau nom.

    Dans Office 2007 et 2010, le fait de copier une feuille de calcul dans un nouveau classeur copie l'ensemble des styles de cellules inutilisés. Afin d'éviter cela, veuillez activer le correctif suivant en premier lieu :

    Si votre version d'Office est entièrement à jour, ce correctif est probablement déjà installé. Cependant, vous devez également activer ce correctif en utilisant la solution Fix it de Microsoft ou dans le registre, comme explicité dans les articles Microsoft suivants :

    Si l'activation du correctif ne fonctionne pas, appliquez la mise à jour mentionnée à la section Résolution dans les articles Microsoft ci-dessus.

  • Nettoyez vos classeurs avec un outil de nettoyage

    Utilisez l’outil de nettoyage recommandé par Microsoft dans KB213904. À l’aide de l’outil XLStyles, cliquez sur Get Excel File, sélectionnez Force all cell styles to 'Normal', puis cliquez sur Process File. Lorsque le fichier nettoyé s'ouvre dans Excel, enregistrez-le sous un nouveau nom.

  • Supprimez les styles de cellule personnalisés de votre classeur à l’aide d’une macro VBA

    Ouvrez l'éditeur VBA en appuyant sur Alt+F11. Sélectionnez votre classeur et cliquez sur InsertionModule. Collez ensuite l'une des macros suivantes et lancez-la en appuyant sur F5 :

    DeleteCustomStyles() supprime l'ensemble des styles de cellule personnalisés de votre classeur.

    Sub DeleteCustomStyles()
    Dim st As Style
    Dim i, i_cust As Long
    i_cust = 0
    For i = (ActiveWorkbook.Styles.Count) To 1 Step -1
    	With ActiveWorkbook.Styles(i)
    		If .BuiltIn = False Then
    			On Error Resume Next
    			.Locked = False
    			On Error GoTo 0
    			If Not .Locked Then
    				.Delete
    				i_cust = i_cust + 1
    			End If
    		End If
    	End With
    	If (i Mod 100) = 0 Then Debug.Print i
    Next
    MsgBox "Styles deleted: " & i_cust & " styles" & Chr(13) & "Styles left: " & ActiveWorkbook.Styles.Count
    End Sub

    Remove_Styles() supprime uniquement les styles de cellule inutilisés de votre classeur. Cette macro est similaire à la macro ci-dessus, mais son exécution peut être très longue. La macro est fournie par le support Microsoft Office.

    Option Explicit
    Dim st_array() As String
    Dim i_x As Long
    Sub Remove_Styles()
    Dim stname As String
    Dim ustname As String
    Dim uc As Range
    Dim retval As Boolean
    Dim ust As Style
    Dim sh As Worksheet
    i_x = 0
    For Each sh In ActiveWorkbook.Worksheets
        For Each uc In sh.UsedRange
            stname = uc.Style.Name
            retval = Check_Array(stname)
            If retval = False Then
            ReDim Preserve st_array(i_x)
            st_array(i_x) = stname
            i_x = i_x + 1
            End If
        Next uc
    Next sh
    For Each ust In ActiveWorkbook.Styles
        If ust.BuiltIn = False Then
            ustname = ust.Name
            retval = Delete_Styles(ustname)
            On Error Resume Next
            If retval = True Then ust.Delete
            On Error GoTo 0
        End If
    Next ust
    End Sub
    Function Delete_Styles(stylename As String) As Boolean
    Delete_Styles = True
    Dim i_y As Long
    For i_y = 0 To i_x - 1
        If st_array(i_y) = stylename Then Delete_Styles = False
    Next i_y
    End Function
    Function Check_Array(stylename As String) As Boolean
    Check_Array = False
    Dim i_y As Long
    For i_y = 0 To i_x - 1
        If st_array(i_y) = stylename Then Check_Array = True
    Next i_y
    End Function

La meilleure solution consisterait à éviter de coller les styles de cellules inutilisés, mais nécessite la résolution d’un bug par Microsoft dans Excel 2007 et les versions ultérieures. Jusqu'à présent, Microsoft n'a pas jugé que l'impact était suffisamment important sur les sociétés pour développer un correctif. Si votre société possède un contrat auprès de l'Assistance Microsoft Office, vous pouvez contacter Microsoft pour demander un correctif en leur mentionnant le numéro de dossier 113012810172527.

Explication

Certains classeurs .xlsx contiennent un nombre important (plusieurs dizaines de milliers) de styles de cellule en raison de bogues et de la conception problématique du code dans Excel. Les outils de reporting qui génèrent parfois un nombre excessif de styles de cellule peuvent contribuer à ce problème.

Partager