Nombre de formats de cellule trop élevé Excel 2010

Le 14/08/2017 à 16:41

Bonjour, depuis plusieurs semaines, je suis franchement handicapé par le message d'erreur "Nombre de formats de cellules trop élevé" qui apparaît comme suit :

fichier Excel 2010 avec plusieurs onglets (aucune macro, ni graphique, ni lien tarabiscoté vers la planète Mars ; juste des onglets alimentés avec la fonction "Déplacer ou copier les feuilles sélectionnées" depuis d'autres fichiers, bien plus costauds !)

J'ai déjà consulté pleins de sites et forum, y compris la doc de Microsoft en français (et en anglais autant que possible).

Les forums consultés focalisent davantage sur des versions d'Excel 2003 et au-dessous.

Il paraît que la limite avec Excel 2007 et au-delà est de 64000 différents formats différents !

Je suis un très petit joueur, et je ne pense pas être déjà parvenu à une telle valeur.

Question :

* Comment connaître le nombre de formats de cellule différents utilisés dans un onglet et/ou dans une feuille Excel ?

* Pourquoi ce problème débarque-t-il dans mon environnement alors que j'ai déjà composé des feuilles dans le passé davantage garnies de différents formats ?

Je rappelle que je travaille avec Excel 2010 (dans un environnement pro).

Un grand MERCI pour votre aide.

Gérard.

Le 14/08/2017 à 17:33

Bonjour,

Une piste à explorer.

Fais une sauvegarde de ton fichier.

Sur la copie, ALT F11 et copie la procédure ci-dessous dans un module standard.

La procédure va supprimer tous les styles personnalisés du classeur actif !...

Option Explicit

Public Sub Remove_Styles()
Dim wb As Workbook
Dim Sty As Style
Const NUM_STYLES As Byte = 47
Dim N As Long, i As Long
Dim Msg As String, Title As String
Dim Buttons As VbMsgBoxStyle, Answer As VbMsgBoxResult
    Set wb = ActiveWorkbook
    N = wb.Styles.Count
    Msg = "Le classeur comporte " & (N - NUM_STYLES) & " style(s) personnalisé(s)." & vbCrLf
    Msg = Msg & "Souhaitez-vous le(s) supprimer ?"
    Buttons = vbYesNo + vbQuestion
    Title = "Suppression styles personnalisés ?"
    Answer = MsgBox(Msg, Buttons, Title)
    Select Case Answer
        Case vbYes
            For i = N To 1 Step -1
                Set Sty = wb.Styles(i)
                If Not Sty.BuiltIn Then Sty.Delete
            Next
        Case vbNo
            Exit Sub
    End Select
End Sub

Nota : Tu peux aussi essayer cet outil.

https://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/

Le 14/08/2017 à 21:08

Bonsoir Jean-Eric,

Je testerai ta proposition dès mon retour au bureau et te tiendrai au courant.

Une question néanmoins : comme le disait un autre internaute sur ce site et pour ce même sujet, quand je suis "infecté" par ce problème, c'est trop tard ; j'ai perdu mon travail ! En effet, tant que le fatidique message n'est pas apparu, je peux continuer à travailler correctement et sans souci ; par contre, après, il faut garder le doigt sur la touche "Entrée" du clavier pour valider la fermeture du message environ 600 fois (oui, je les ai compté au début en cliquant sur le bouton gauche de la souris !!!!) Après que cette fastidieuse tâche soit accomplie, la page se ferme et il est impossible d'accéder de nouveau au fichier. Il est perdu corps et âme !

Merci

Gérard

Le 15/08/2017 à 00:45

Bonjour,

sans doute que tes copier multiples de différentes sources n'y sont pas étranger.

Il suffit que chacun ait mis sa 'patte déco' avec sa police, ses tailles, ses couleurs etc et ça se multiplie vite.

Tu devrais faire plus de collage spécial valeur ou formule et faire ta mise en forme toi même, tu maitriseras mieux ce paramètre.

Ou bien lancer la proposition de jean-eric à chaque fermeture pour faire un peu le ménage sans que ça s'accumule.

Les 64000 c'est : Nombre maximal de formats/styles de cellule uniques selon la doc. A voir ce qu'ils entendent avec cette notion.

eric

Le 15/08/2017 à 12:34

Bonjour

Exemple : si tu veux encadrer un tableau de 10 lignes et 5 colonnes en ayant

toutes les cellules de la 1ere ligne (titres)

et celle de la dernières ligne (totaux) encadrées

et pour les lignes intermédiaire seulement les séparations verticales,

moi je fait le tout en une fois mais j'ai vu faire en 7 fois (voire plus) : dans mon cas les 50 cellules ne sont pas "uniques" en terme de mise en forme (j'ai une seule mise en forme pour le bloc de 50 cellules) dans l'autre cas...

Le 16/08/2017 à 08:31

Bonjour Jean-Eric,

Je viens d'exécuter la macro sur le petit fichier xlsx en PJ.

Dans le pdf joint, tu trouveras des copies écran des 2 messages apparu à l'exécution de la macro.

A la 1ère exécution, il y avait 57701 styles personnalisés, ensuite il n'y en a plus "que" 13436. Chaque exécution supplémentaire ne modifie plus cette valeur.

Puis vient le message d'erreur qui clôture l'exécution de la requête. Là, mes connaissances en macro sont trop limitées pour déceler ce qui coince dans la macro.

Merci

Gérard

130explication.pdf (118.95 Ko)
70drdt-extension-cmd-2017-08f.xlsx (0.96 Mo)
Le 16/08/2017 à 10:27

Bonjour,

J'ai utilisé XLStyles Tool, pour arriver à supprimer tes 57.700 styles différents de ton classeur.

Je ne sais pas comment on peut arriver à un nombre aussi important!...

Cdlt.

https://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/

78drdt-extension-cmd-2017-08f.xlsx (558.97 Ko)
Le 16/08/2017 à 11:40

Bonjour

Gérard7 a écrit :

... juste des onglets alimentés avec la fonction "Déplacer ou copier les feuilles sélectionnées" depuis d'autres fichiers, bien plus costauds !)...

Manifestement de tous pays et en toutes langues y compris asiatiques qui plus est...

Et probablement de versions Excel diverses : ça par contre, MS aurait du le gérer depuis le temps... et ne pas superposer ses propres styles...

Le 17/08/2017 à 11:23

Merci Jean-Eric d'avoir pu faire la manip de ton côté avec l'exemple que je t'ai fait parvenir.

Par contre, as-tu rencontré l'erreur dont je t'ai parlé dans la copie écran figurant sur le pdf qui était aussi joint à mon précédent message ?

Et si non, pourquoi est-ce que je rencontre cette erreur ? Et quelle conséquence a-t-elle sur le nettoyage espéré ?

Pour info, tu trouveras ci-joint un autre exemple pour lequel j'ai fait passer ta macro. Il est sensé rester 13436 éléments à nettoyer à partir des 57070 initialement trouvés (je peux repasser autant de fois que je veux la macro sans que le nombre résiduel ne décroisse !)

Et le message d'erreur revient systématiquement. Il pointe d'ailleurs sur la ligne suivante de la macro

" If Not Sty.BuiltIn Then Sty.Delete"

Merci

Gérard

41drdt-extension-cmd-2017-08g.xlsx (670.38 Ko)
Le 17/08/2017 à 11:59

Bonjour,

mais tu ne peux pas agir à la source du problème et faire des collage spécial valeur ?

eric

Le 17/08/2017 à 12:14

Bonjour,

Ton fichier nettoyé en partie, car il reste 32 styles 'personnalisés' qui ne peuvent pas être supprimés.

C'est pourquoi, j'avais utilisé XLStyles Tool, qui a réussi là ou VBA échoue.

Ci-dessous la procédure initiale modifiée pour voir les styles indélicats.

Cdlt.

49drdt-extension-cmd-2017-08g.xlsm (565.77 Ko)
Option Explicit

Public Sub Remove_Styles()
Dim wb As Workbook
Dim Sty As Style
Const NUM_STYLES As Byte = 47
Dim N As Long, i As Long
Dim Msg As String, Title As String
Dim Buttons As VbMsgBoxStyle, Answer As VbMsgBoxResult
    Set wb = ActiveWorkbook
    N = wb.Styles.Count
    Msg = "Le classeur comporte " & (N - NUM_STYLES) & " style(s) personnalisé(s)." & vbCrLf
    Msg = Msg & "Souhaitez-vous le(s) supprimer ?"
    Buttons = vbYesNo + vbQuestion
    Title = "Suppression styles personnalisés ?"
    Answer = MsgBox(Msg, Buttons, Title)
    Select Case Answer
        Case vbYes
            For i = N To 1 Step -1
                On Error GoTo err_Handler
                Set Sty = wb.Styles(i)
                If Not Sty.BuiltIn Then Sty.Delete
            Next
        Case vbNo
            Exit Sub
    End Select
err_Handler:
    MsgBox "Style : " & Sty.Name & vbCrLf & "Erreur " & Err.Number & " : " & Err.Description
    Resume Next
End Sub
Le 17/08/2017 à 13:59

Bonjour

eriiic a écrit :

Bonjour,

mais tu ne peux pas agir à la source du problème et faire des collage spécial valeur ?

eric

Pas quand on copie des onglets, ce qui est nettement plus pratique que créer un onglet, déterminer la plage source et récupérer les valeurs sans compter la perte des largeur de colonnes, hauteur de lignes, formules (quoiqu'on puisse utiliser collage spécial formules), mise en page (zone d'impression, en-tête et pied).

Et dans nombre de cas le format dont MFC est utile. Sans parler d'éventuels graphiques ou autres éléments...

Le problème c'est que MS n'a pas pas codé proprement ses propres styles de cellules et quand on copie d'une version différente dans le temps ou dans la langue, la copie crée des styles personnalisés pour tous les styles MS...

Donc si on assemble les onglets de 5 classeurs de sources différentes on multiplie par 5 les 47 styles MS (sur 2010 j'en vois 47 mais je crois qu'ils ne sont pas tous affichés...)

Très peu d'utilisateurs, à tord, crée leurs propres styles donc c'est un problème que MS aurait du traiter depuis très longtemps...

Le 17/08/2017 à 17:04

Le problème c'est que MS n'a pas pas codé proprement ses propres styles de cellules

C'est clair. Quand on voit qu'il est incapable d'en supprimer un qui est listé et parait complet, on peut se poser des questions. Ca a dû être fait par le stagiaire

Le 18/08/2017 à 21:55

Merci Eric et Chris pour vos retours.

Effectivement, je procède par "Déplacer ou copier" l'onglet d'un fichier à l'autre, ce qui permet de préserver la mise en page globale.

Pour limiter le commentaire de Chris, tous les fichiers émanent exclusivement de Excel 2010 et ils parlent tous seulement le français !

Depuis que j'ai fait tourner la macro, je n'ai plus de problème. Je vais donc laisser le post en sommeil jusqu'à nouvel avis.

Encore merci à tous pour vos réponses.

A plus

Gérard

Le 18/08/2017 à 22:05

Bonjour

Tu avais des styles en caractères asiatiques dans ton fichier ! Tu peux vérifier toi-même

Il est probable qu'il y a eu des copies de copies de copies...

Le 27/05/2021 à 21:12

Bonsoir 78Chris et bien sûr à tous les utilisateurs de cette page.

Je reviens sur le dernier échange que nous avions eu le 18 août 2017 à 22h05 où tu me répondais que "j'avais des styles en caractères asiatiques dans ton fichier ! Tu peux vérifier toi-même. Il est probable qu'il y a eu des copies de copies de copies..."

Je serai curieux de savoir comment tu avais pu voir ces caractères et où les retrouver ... afin de plus facilement les éliminer !

Il s'avère de 4 ans plus tard, il m'arrive encore d'avoir certains de mes fichiers qui me renvoient la même problématique !

Si tu es toujours sur ce forum, merci pour ton retour

Gérard,

Le 27/05/2021 à 21:37

Bonjour

Si, j'ouvre ton fichier posté 17/08/2017 à 11:23 dans la liste des styles de cellules il y a ces styles avec des caractères exotiques

Rechercher des sujets similaires à "nombre formats trop eleve 2010"