Détérioration des MFC au passage entre Excel 2003 et 2011

Bonjour le Forum,

Je reviens sur un problème que j'ai déjà évoqué sous un angle différent dans un autre sujet.

Je suis contraint de faire migrer des fichiers .xls vers Excel 2011 sur Mac.

Mais parfois, je dois à nouveau ouvrir ces fichiers sur Excel 2003.

Et là, c'est la cata : les MFC ne fonctionnent plus.

Une des raisons principales, c'est que les formules subissent une transformation.

Ainsi, la formule

=L(-28)C1<>""

devient

=RECETTES!L(-28)C1<>""

où "RECETTES" est le nom de la feuille Excel où se trouve la formule.

...et les MFC ne fonctionnent plus. Mais si je supprime "manuellement" le préfixe dans la formule, ça remarche.

Du coup je vois 2 solutions :

– repérer toutes les MFC dans une feuille et supprimer ce préfixe

– créer une macro qui détecte toutes les MFC de toutes les feuilles du classeur, et qui en efface le préfixe.

Cette 2ème solution est évidemment nettement plus intéressante, mais je suis incapable de l'imaginer, encore moins de l'écrire.

Quelqu'un pourrait-il m'aider ?

Merci !

Bonsoir,

Mon conseil sur ton sujet précédent était de coder la mise en place des MFC par VBA, et réaliser un toilettage à chaque ouverture.

Cordialement.

Bonjour,

Je n'ai pas oublié ce conseil.

Mais 1/ c'est difficile voire impossible d'identifier a posteriori toutes les MFC d'un classeur qui en compte parfois des dizaines par feuille, et cela, pour une vingtaine de classeurs et 2/ je ne saurais pas le faire en VBA....

bonjour

salut MFerrand

il me semble me souvenir que le conseil n°1 était de simplifier drastiquement le nombre de couleurs pour plus de lisibilité...

un truc à tenter : sur le PC, ouvrir avec OpenOffice, enregistrer au format xls, xlsx et ods

sur Mac, ouvrir ces 3 fichiers avec Excel et avec OpenOfficePourMac

tu en as pour 5 minutes

OK, merci. Je tenterai ça lundi au boulot (pas de Mac chez moi).

Bonjour,

Je n'ai pas oublié ce conseil.

Mais 1/ c'est difficile voire impossible d'identifier a posteriori toutes les MFC d'un classeur qui en compte parfois des dizaines par feuille, et cela, pour une vingtaine de classeurs et 2/ je ne saurais pas le faire en VBA....

Tu n'auras guère le choix que de la reconstruire, d'une façon ou d'une autre (vba ou passage par open office si ça fonctionne)

.formula1 est en lecture seule : impossible de l'éditer et de remplacer.

Tu peux balayer les MFC d'une feuille :

Sub controleMFC()
    Const sep As String = "=;(&+-*/<>"
    Dim sh As Worksheet, fc As FormatCondition
    Dim f As String, nomf As String, pos As Long, pos1 As Long, pos2 As Long, i As Long
    For Each sh In Worksheets
        For Each fc In sh.Cells.FormatConditions
            f = fc.Formula1
            Do
                pos1 = InStr(f, "!")
                If pos1 > 0 Then
                    For i = 1 To Len(sep)
                        pos = InStrRev(f, Mid(sep, i, 1), pos1)
                        If pos > pos2 Then pos2 = pos
                    Next i
                    If Mid(f, pos2 + 1, pos1 - pos2 - 1) = sh.Name Then
                        MsgBox f & vbLf & "MFC à problème" & vbLf & "La reconstruire"
                    End If
                End If
            Loop Until pos1 = 0
            'fc.Formula1 = f ' lecture seule !!!
        Next fc
    Next sh
End Sub

la macro stoppe sur les MFC utilisant le nom de la feuille où elles sont.

C'est à cet endroit que tu dois la reconstruire en récupérant toutes ses caractéristiques, là je dois passer à autre chose...

Compléter Const sep si besoin. J'ai mis les caractères qui m'étaient évident, il en manque peut-être.

Si tu les connais et qu'elles ne varient pas c'est plus simple de tout supprimer et de refaire directement comme te l'indiquait MFerrand

eric

Bonjour, Salut jmd !

Si tu viens te faire aider sur le Forum, ce n'est pas logiquement pour y trouver ce que tu sais déjà faire, mais parvenir à réaliser ce que tu n'étais pas encore en mesure de faire.

Des dizaines de MFC par feuille ? Ce n'est pas impossible, certes, mais j'ai vu des dizaines de classeurs avec un nombre conséquent de MFC qui, une fois correctement rétablies se réduisaient à 2 ou 3 ! Le principe est une MFC par mise en forme, pas plus, alors à moins d'avoir des dizaines de couleurs ou autres éléments de mise en forme, moyennant quoi on aura du mal à percevoir ce que chacune signifie et elles perdront beaucoup de leur intérêt, je soupçonnerais donc a-priori des MFC doublonnées qui du même coup gagneraient à être réduites.

Cordialement.

@eriiic

Merci pour le code. Mon niveau en VBA ne me permet pas de comprendre comment il fonctionne.

(et je ne comprends pas ".formula1 est en lecture seule : impossible de l'éditer et de remplacer.")

Je l'ai testé et il déclenche le msg "Erreur de compilation: Nombre d'arguments incorrect ou affectation de propriété incorrecte" avec la ligne "fc.Formula1 = f" en surbrillance. Ceci est sans doute en rapport avec cela.

Je pensais naïvement que c'était possible de faire une macro qui modifie une formule de MFC modifiée par Excel sous Mac de façon à passer de

=Nom_de_la_feuille!Formule

=Formule

...ça a l'air malheureusement plus compliqué...

@MFerrand

Des dizaines de MFC par feuille ?

Oui, je persiste et signe.

J'ai par exemple des tableaux de bords qui vérifient que des documents ont bien été reçus (vérification que les cellules sont bien renseignées, qu'il n'y a pas de retard de réception, de transmission), qui vérifient ensuite pour 10 (ou plus) intervenants s'ils ont rempli des conditions qui leur sont propres, ce qui déclenche des alertes différentes pour différents intervenants avec différents niveaux d'alerte (feu vert/orange/rouge), etc...

Impossible de réduire le nombre de MFC.

Et pour certaines cellules, qui ne sont pas répétitives, très difficile de vérifier cellule par cellule si elles contiennent ou pas une MFC (car la plupart de mes MFC laissent le fond de la cellule sans remplissage s'il n'y a pas d'alerte).

Et de toutes façons, faute de disposer sur mon PC d'une version postérieure à Excel 2003, je suis obligé d'éditer les MFC par ensembles de cellules comportant exactement les mêmes MFC, sinon les champs de formules apparaissent en blanc, ce qui complique encore les choses.

Enfin, quand le problème se manifeste sur des dizaines de feuilles d'une vingtaine de classeurs, même les feuilles auxquelles je n'ai pas touché, c'est totalement décourageant d'imaginer rééditer manuellement toutes les MFC dès lors qu'un fichier aura été enregistré sur un Mac.

Il est évident que si tu ouvres sur 2003 un classeur dont les MFC ont été mises en place avec une version ultérieure, si certaines ne sont pas réalisables sur 2003 elles seront inopérantes et tu risques en enregistrant sous 2003 de les endommager ou les détruire.

Maintenant, tu me permettras de douter d'une complexité de situation qui empêcherait que l'on puisse recourir à des choses relativement simples, tant que je ne l'ai pas examiné par moi-même. Et si l'on se trouvait dans un cas où la situation s'avérait vraiment inextricable, mon diagnostic est toujours alors que c'est la conception qui est défectueuse et qu'il faut y revenir...

Cordialement.

il déclenche le msg "Erreur de compilation: Nombre d'arguments incorrect ou affectation de propriété incorrecte" avec la ligne "fc.Formula1 = f"

oui, cette ligne est à supprimer. C'est là où je me suis retrouvé bloqué. J'ai éditer le post en conséquence.

On ne peut pas modifier une formule de MFC, il faut la supprimer et la recréer.

eric

@MFerrand

Malheureusement, il y a des questions de confidentialité, donc je ne peux pas communiquer mes fichiers.

Cela dit, quand bien même tu trouverais des simplifications, le problème n'est pas là à mes yeux.

Ce qui pose fondamentalement problème, c'est que je doive reconstruire manuellement les MFC de toutes les feuilles d'un classeur après chaque aller-retour d'un fichier entre Mac et PC. C'est ingérable !

Donc, puisque eriiic me dit qu'une macro ne pourra pas modifier une MFC, je vais devoir trouver autre chose. Dernière solution en date : récupérer un vieux Mac à mon boulot pour travailler chez moi sans avoir à repasser par un PC, mais c'est vraiment pas terrible...

@eriiic

Merci d'avoir persévéré.

Dommage que ma demande butte sur une limite du VBA, mais c'est la vie...

Bonjour,

tu ne peux les modifier mais tu peux les supprimer et les recréer.

eric

@jmd

Ton intuition a été bonne : j'arrive à récupérer des MFC intègres en procédant comme suit :

– sur Mac : ouvrir fichier.xlsm dans OpenOffice et enregistrer sous fichier.ods

– sur PC : ouvrir fichier.ods dans OpenOffice et enregistrer sous fichier.xls

Il y a un peu de perte en ligne : perte des macros et mauvaise interprétation de certaines formules, mais à la limite, je peux coller les formats dans le même fichier enregistré en .xls sur Mac et ouvert sur mon PC.

Voilà, j'espère que ça pourra servir à qqn d'autre.

Et merci à tous ceux qui m'ont aidé !

Une info, suite à un nouveau test : l'export des MFC depuis un fichier .xlsm sur Office 365 sur Mac vers un fichier .xls sur PC se passe très bien.