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

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 copie → OK
  • 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 :

  • Microsoft Office 2007 : KB2553085
  • Microsoft Office 2010 : KB2598127, qui a été publié dans la mise à jour Office 2010 du mois d’avril 2012.

  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 Insertion → Module. 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.