Microsoft 365 Nettoyage de fichier

[C.Laurent]

Bonjour à tous

J'ai un fichier un poil complexe qui présente des bizarreries. Je cherche un code VBA que j'ai déjà utilisé avec succès mais que ma méthode de classement a égaré ...

Il y a très très très longtemps sur MPFE, une gentille âme m'avait fait suivre un code qui depuis un fichier1.xls copiait toutes les feuilles et données dans un nouveau classeur2.xls et copiait aussi les zones nommées, les modules VBA, les graphes et TCD. En fait, il recréait le même fichier.

J'ai cherché ici sans succès, j'ai bien vu des codes pour faire fondre les fichiers à cause de la dernière cellule mais ce n'est pas ça que je cherche.

Avez vous une idée d'où puis-je trouver mon graal ?

Par avance merci pour votre aide.
@+Laurent

 

[djidji59430]

Bonjour à tous,

Témérairement, t'as tenté un copier/coller ? Ou sauvegarder sous ?
Je ne vois pas ou le mot nettoyage apparait dans le descriptif de l'action de ta macro !

Crdlmt

 

[vgendron]

Bonjour

Si ton fichier contient des bizarreries dont tu ignores la cause, qu'est ce qui te fait croire que tu ne vas pas les copier aussi dans le nouveau classeur.?
ne vaudrait il mieux pas trouver l'origine du pb et corriger.?

 

[C.Laurent]

Bonjour

Merci pour ces réponses mais non ...

Le sauvegarder sous ne va pas.
Le copier coller, c'est probablement la solution (tout refaire ce fichier dans un nouveau fichier). J'ai déjà tenté de copier les feuilles dans un nouveau classeur mais à la fin ça ne le fait pas.
La meilleure option serait probablement de trouver le problème pour pouvoir le soigner mais voilà un moment que je cherche sans succès

Puisque vous insistez j'essaye d'expliquer le problème ... ça va être un gros poil long et délicat, n'hésitez pas me dire ce que vous ne comprenez pas dans mes explications.

Je gère dans ce fichier mon PEA et mon CTO (Ouh c'est pas bien, capitalisme, exploitation de l'homme, tout ça tout ça. Ok, préparez le fouet j'amène du gros sel ).

Dans un première feuille nommée "Opérations" j'empile mes opérations d'achat de titre, de vente, dividendes, frais, apport et retrait en notant les différents montants, date et autres dans les colonnes appropriées. A droite, j'ai plusieurs colonnes calculées en fonction des données entrées pour cette ligne, par exemple le PRU, le nouveau stock à cette date et si cette ligne est un dividende la performance de ce dividende par rapport à la somme investie sur ce titre, j'ai nommé cette colonne "PerfDivid". Cette suite de ligne n'est pas un tableau structuré (obtenu par insertion/tableau) c'est juste des lignes les unes au-dessous des autres, par contre j'ai nommé toutes ces colonnes avec du décaler/nbval. On peut discuter de ce point mais je crois ce n'est pas l'origine du problème.

Dans la feuille "Positions" des formules et du VBA me listent mes positions actuelles dans un tableau structuré avec tout un tas d'informations pour ce titre (compte, PRU, quantité en stock, valeur d'achat, valeur actuelle, ...)

Dans une autre feuille ListeTitre, j'ai un tableau structuré qui reprend tous les titres que je surveille mais que je n'ai pas forcément en portefeuille. Chaque titre n'apparait qu'une seule fois dans cette liste. Dans cette feuille j'ai une colonne nommé "Masquer" contenant une formule renvoyant un nombre ou rien. L'idée est de masquer ou pas certaines lignes de dividende (les titres donnant un dividende très très faible pourrissent mon graphique de dividendes, je veux les masquer selon cette valeur "Masquer"). J'ai une autre colonne "Secteur" qui donne le secteur d’activité du titre.

Pour les trucs qui fonctionnent, par exemple j'ai un TCD (créé en cochant la case "Ajouter ces données au modèle de données") dans une feuille "Répartition" qui pointe sur les données de "Positions" et "ListeTitre". J'ai donc un joli camembert qui représente la "valeur actuelle" de mes différentes positons classées par ordre de "secteur" indiqué dans "ListeTitre". Excel à fait une "relation" entre Postions et ListeTitre, ok.

J'ai tout un tas de graphique qui s'appuient sur ces données : répartition entre devises, entre comptes, chronologie des dividendes distribués, historique de performance du compte, historique plus-value par compte, comparaison performance du compte vs CAC 40 et/ou SP500 ... ajoute à ça la gestion de X devises différentes, un classeur bien compliqué (de mon point de vue).

Si je fais un TCD + graphe de mes "Opérations" je peux sommer mes "PerfDiv" en fonction des dates et titres pour faire un joli histogramme 3D.

Pour ce qui ne fonctionne pas (bizarrerie évoquée).
Si je fais ce même TCD en ajoutant ces données au modèle de données (dans l'objectif d'utiliser la colonne "Masquer" de "ListeTitre"), lors de la création, lorsque je glisse le champ "PerfDivid" dans la case "Σ valeur" du TCD, Excel me propose le "nombre de PerfDivid" et refuse la "somme de PerfDivid" sous prétexte que c'est du texte !!! Ca fonctionne pourtant très bien si je ne coche pas "Ajouter ces données au modèle de données" :-(

J'avoue avoir fait, défait, refait plein de trucs dans ce classeur et même bidouillé avec Powert Pivot ... Je ne doute pas du fait que c'est moi qui est réussi à pourrir le fichier.

Je reste certain que le code que je cherche pour reproduire un fichier existe, je l'ai déjà utilisé.
Je crois me souvenir qu'il y avait une chouette page sur google qui permettait de chercher sur usenet (et donc sur MPFE) mais je n'arrive pas à mettre la main dessus :...(

Merci pour votre aide
@+Laurent

 

[Cousinhub]

Bonjour,
Alors MPFE, c'est par ici...
Mais ne sachant exactement ce que tu cherches, ben, bon courage
PS, si tu trouves, merci de partager

 

[TooFatBoy]

Bonjour,

Le problème vient peut-être de ta colonne Masquer et de sa formule.

 

[C.Laurent]

Le problème vient peut-être de ta colonne Masquer et de sa formule.

Bonjour

A priori non, car le problème (impossible d'avoir la somme de PerfDivid) existe avant d'avoir glissé le champ "Masquer".

Alors MPFE, c'est par ici...

Merci pour ce lien mais je l'avais trouvé, il est assez nul (on ne peut pas chercher avec le nom de l'utilisateur). J'ai eu utilisé un bien meilleur moteur de recherche (je ne suis pas encore grand père pourtant).

Mais ne sachant exactement ce que tu cherches, ben, bon courage

Je précise ma demande initiale.

Je cherche un code que je stockerai dans un module de mon fichier actuel. L'exécution de cette macro va :
- créer un nouveau fichier
- copier coller toutes les valeurs, textes du fichier initial vers ce nouveau fichier
- réécrire les formules dans le nouveau fichier
- re faire les zones nommées
- copier les modules VBA
- enregistre le nouveau classeur sous un autre nom

Je crois me souvenir que les TCD et graphes étaient aussi refaits (mais que toutes les mises en pages ne suivaient pas toujours très bien).

L'objectif atteint était de ne recopier que ce qui faisait sens au yeux d'Excel.

Merci à tous pour votre aide
@+Laurent

 

[vgendron]

et sinon.. poster ton fichier qui pose problème.. ??? (en ayant pris soin de remplacer les données perso par des données bidon...)

 

[Staple1600]

Bonjour le fil

Suggestion 1
(sur une copie)
Fichier/Ouvrir-> Ouvrir et réparer

Suggestion 2

Spoiler: c'est pas du MPFE, mais du Debra D.

Pour ce qui est de récupérer les formules
Il faut juste quelques adaptations pour ne pas traiter le classeur actif mais un classeur spécifique.

Sub ListAllFormulas()
'print the formulas in the active workbook
Dim lRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim c As Range
Dim rngF As Range
Dim strNew As String
Dim strSh As String
On Error Resume Next
Application.DisplayAlerts = False
Set wb = ActiveWorkbook
strSh = "F_"
For Each ws In wb.Worksheets
  lRow = 2
  If Left(ws.Name, Len(strSh)) <> strSh Then
    Set rngF = Nothing
    On Error Resume Next
    Set rngF = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
    If Not rngF Is Nothing Then
      strNew = Left(strSh & ws.Name, 30)
      Worksheets(strNew).Delete
      Set wsNew = Worksheets.Add
      With wsNew
        .Name = strNew
        .Columns("A:E").NumberFormat = "@" 'text format
        .Range(.Cells(1, 1), .Cells(1, 5)).Value _
            = Array("ID", "Sheet", "Cell", "Formula", "Formula R1C1")
        For Each c In rngF
          .Range(.Cells(lRow, 1), .Cells(lRow, 5)).Value _
            = Array(lRow - 1, ws.Name, c.Address(0, 0), _
              c.Formula, c.FormulaR1C1)
          lRow = lRow + 1
        Next c
        .Rows(1).Font.Bold = True
        .Columns("A:E").EntireColumn.AutoFit
      End With 'wsNew
      Set wsNew = Nothing
    End If
  End If
Next ws
Application.DisplayAlerts = True
End Sub

 

[Staple1600]

Re

La version VBA de la suggestion 1 du précédent message
(non testée, car quand un classeur me joue des tours, il finit à la corbeille et je le reconstruit à la mano au clair de lune )

Sub OpenAndRepairWorkbook()
Dim oWB As Workbook, sPath$,Classeur_AuBout2SaLife$
sPath="C:\Users\Staple\WTF\" ' à adapter
Classeur_AuBout2SaLife="JeSuisMalade.xlsm" ' à adapter
On Error GoTo Err_Open
Set oWB = Workbooks.Open(sPath & Classeur_AuBout2SaLife, CorruptLoad:=XlCorruptLoad.xlRepairFile)
Exit Sub
Err_Open:
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End Sub

NB: A tester toujours sur une copie du classeur problématique

 

[C.Laurent]

Bonjour à tous

Merci pour vos réponse, j'ai mis en œuvre le "réparer" mais ça n'a pas soigner l'affaire.

Lorsque je glisse le champ "PerfDivid" dans la case "Σ valeur" du TCD, Excel me propose le "nombre de PerfDivid" et refuse la "somme de PerfDivid" sinon erreur !

PerfDivid est une plage nommé dynamiquement avec cette formule
=DECALER(Opérations!$AC$4;1;;VarNombreOpé;1)

Ces cellules contiennent un nombre ou rien. J'ai essayé sans plus de succès avec un nombre ou zéro.

Si je ne coche pas "Ajouter ces données au modèle de données" à la création du TCD, je n'ai pas de problème.

Encore merci.
@+Laurent

 

[Staple1600]

Bonjour

@C.Laurent
Et la suggestion 2
(Pour récupérer les formules d'un classeur)
Tu as testé ?

PS: Tu ne souviens pas du pseudo de la bonne âme "qui errait sur le MPFE" , par hasard ?

 

[gbinforme]

Il y a très très très longtemps sur MPFE, une gentille âme m'avait fait suivre un code qui depuis un fichier1.xls copiait toutes les feuilles et données dans un nouveau classeur2.xls et copiait aussi les zones nommées, les modules VBA, les graphes et TCD. En fait, il recréait le même fichier.

J'avais eu un bug sur un classeur qui me divisait certaines feuilles en 2 entités VBE et avant de comprendre que c'était dû à des boutons sur la feuille qu'il suffisait de remplacer par des shapes j'avais réalisé un classeur pour recopier le fautif.
Je te le met pour recopier ton classeur mais je doute fort qu'il te permette de corriger le bug TCD que tu as et dont il faudrait analyser plus finement la cause pour pouvoir le corriger.

 

[C.Laurent]

Bonsoir à tous

... Je te le met pour recopier ton classeur mais je doute fort qu'il te permette de corriger le bug TCD que tu as ...

Excellent, c'est bien ce que je cherchais, mille mercis !

Pour infos, j'ai mis en œuvre, ça a planté avec une erreur 1004 je crois. En pas à pas en VBA, j'ai vu que ça bloquait pour "mise à jour source TCD" sur un tableau dont "SourceData" renvoyait une erreur. J'ai glisser la flèche jaune plus bas pour continuer et suis arrivé à la fin.
Suite à ça, l'action "Actualiser tous" bloquait sur la relation entre "Positions" et "ListeTitres" qui pointait sur une vielle version de ce même fichier ... j'ai pas bien compris mais je confirme que cette version de fichier était du temps ou j'expérimentais Power Pivot :-(

J'ai viré les relations hasardeuses, mon fichier va mieux, il s'ouvre plus vite et est plus réactif, il n'a pas perdu en poids (4.5 Mo).

Mon fichier va mieux sauf que j'ai toujours le même problème avec mon TCD, si je coche "Ajouter ces données au modèle de données", il ne veut plus faire la somme d'un champ car il le prend pour du texte ...

Gros merci pour ce fichier que j'archive comme il faut.

@+Laurent

 

[gbinforme]

Mon fichier va mieux sauf que j'ai toujours le même problème avec mon TCD, si je coche "Ajouter ces données au modèle de données", il ne veut plus faire la somme d'un champ car il le prend pour du texte ...

Comme je t'avais dis, en recréant le classeur tu ne peux pas corriger une fonction bancale mais cela t'a permis de localiser une erreur et de fluidifier le fonctionnement : ce n'a pas été inutile et je te remercie du retour très documenté.
bonne continuation.