Distribuer des modifications dans les noms des onglets

[CISCO]

Bonjour à tous

J'ai un fichier BaseForum comportant plusieurs feuilles :
* une dénommée Database
* et plusieurs pour divers services, par ex CO, SMJ, CFT...

Dans la plage B1:Q1 des feuilles des services, j'ai des noms. Par ex pour la feuille du service SMJ

A coté de ça, j'ai autant de fichiers que de feuilles de service. J'ai donc un fichier CO, un autre SMJ, un autre CFT et ainsi de suite... Chacun de ces derniers fichiers comportent une feuille Modèle, et 0 ou plusieurs feuilles portant des noms provenant du fichier BaseForum dans la feuille du service correspondant. Par exemple, pour le service SMJ, donc dans le fichier SMJ, j'ai les onglets suivants :

Dans la pratique, l'utilisateur ouvre BaseForum. Dans la feuille à l'écran, ou une autre, sauf DataBase, il doit pouvoir modifier les noms dans la plage B1:Q1, ou en rajouter. L'ensemble des macros doit automatiquement transférer ces modifications dans le fichier du service correspondant.

Il y a donc deux types de travail à faire avec ces macros dans le fichier du service adéquat :
* Modifier le nom de certains onglets.
Par exemple, si dans la feuille CO du fichier BaseForum, si dans la plage B1:Q1 on change le nom PRETY Jeanne en GURTIEZ Jeanne, l'ensemble des macros doit changer dans le fichier CO l'onglet PRETY Jeanne en GURTIEZ Jeanne.
* Rajouter une ou des nouvelles feuilles, construites comme la feuille modèle, en leur donnant le bon nom
Par exemple, si l'utilisateur rajoute dans la feuille CO du fichier BaseForum le nom PAULI Claude, il faut que l'ensemble des macros crée dans le fichier CO une nouvelle feuille comme la feuille Modèle, mais dénommée PAULI Claude.
Attention : Les noms des onglets ne sont pas forcément dans le même ordre que dans B1:Q1.

L'utilisateur ne supprime pas de noms dans la plage B1:Q1

En pièce jointe, je met 4 fichiers simplifiés. Le fichier BaseForum contient plusieurs macros dans Thisworkbook (dont une Workbook_Open) qui font le travail. Vous pouvez mettre ces 4 fichiers dans le même dossier. Pour que l'ensemble fonctionne chez vous, il faut bien sûr mettre le bon chemin donnant l'accès au bon fichier de service, à la place de "C:\Users\FORUM\" ci-dessous.

'INSCRIRE CI-DESSOUS LE CHEMIN COMPLET DONNANT L'ACCES AU REPERTOIRE CONTENANT LES FICHIERS ANNUELS DE CHAQUE SERVICE
Chemin = "C:\Users\FORUM\"

dans la macro Transfertnomsemployés.

Il ne faut ouvrir que le fichier BaseForum au début et y faire les modifications désirées, dans les feuilles de service uniquement, dans B1:Q1. Les macros sont lancées automatiquement.​

J'ai utilisé la méthode suivante, dérivée d'une proposition faite par Mapomme là :
* A l'ouverture, ou lors de l'activation d'une feuille de service (autre que DataBase), une des macros met dans un tableau dénommé employésdébut tous les noms déjà dans la plage B1:Q1.
* Le ou les changements dans cette plage change une variable booléenne de False à True
* Lors du changement de feuille, ou à la fermeture, une des macros enregistre dans un tableau dénommé employésfin tous les noms dans la plage B1:Q1 s'il y a eu un ou des changements dans cette plage.
Ces changements sont transférés avec Transfertnomsemployés dans la feuille du service correspondant, soit en changeant un ou des noms, soit en créant les nouvelles feuilles nécessaires.
Les fichiers modifiés sont sauvegardés sans changer de noms.

Apparemment, cela fonctionne, mais :

1) je trouve que cette méthode est (un peu) lourde (mettre tous les noms dans un tableau au début, mettre tous les noms dans un autre tableau à chaque changement de feuille ou à la fermeture)... Est-ce que vous auriez une autre méthode plus fine ?

2) Les fichiers des services, CO, SMJ et CFT en pièce jointe sont enregistrés au format Excel 97-2003. Si j'enregistre avec une version plus récente, cela plante sur la dernière ligne de code ci-dessous

Fichierannuel = serviceencours & ".xls"
'OUVERTURE DU FICHIER ANNUEL DU SERVICE CORRESPONDANT
Workbooks.Open Chemin & Fichierannuel, False

Pourquoi ?

@ plus

P.S 1 : Bien sûr, les fichiers en pièce jointe, sont des fichiers simplifiés, et les macros réelles ne font pas que ça. De plus, dans la réalité, ces fichiers ne sont pas tous dans le même dossier.

P.S 2 : Je soignerai la partie déclaration des variables, des tableaux...et les alignements/décalages lorsque tout tournera bien.

 

[CISCO]

Rebonjour

Je met le code, pour ceux qui ne voudraient pas ouvrir les fichiers, mais auraient la gentillesse de me faire profiter de leurs connaissances sur ce sujet.

'Option Explicit
Dim ModifPlagenomsemployés As Boolean
Dim nbreemployésdébut As Integer
Dim nbreemployésfin As Integer
Dim employésdébut(2 To 100) As String
Dim employésfin(2 To 100) As String
Dim serviceencours As String
==================_____________________________________________
Private Sub Workbook_Open()
'MsgBox ("Open")

    ModifPlagenomsemployés = False 'initialisation de ModifPlagenomsemployés à 0 lors de l'ouverture du fichier ou de la sélection d'une feuille
    serviceencours = ActiveSheet.Name
    If serviceencours <> "Database" Then
    macroemployésdébut
    End If

End Sub
=====================_______________________________________________
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'MsgBox ("Changement")

    If Sh.Name <> "Database" Then
    If Not Intersect(Range("B1:Q1"), Target) Is Nothing Then ModifPlagenomsemployés = True
    End If

End Sub
====================____________________________________________
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'MsgBox ("Deactivate")

    If ModifPlagenomsemployés * (serviceencours <> "Database") Then
    macroemployésfin
    End If

End Sub
============================______________________________________
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'On fait la même chose qu'avec Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    'car on peut très bien fermer le fichier base après avoir fait une ou des modifications sur une feuille serviceencours
'MsgBox ("Beforeclose")

    If ModifPlagenomsemployés * (serviceencours <> "Database") Then
    macroemployésfin
    End If

'Cancel = True
End Sub
=================================____________________________________
Private Sub macroemployésfin()
    
        'If ModifPlagenomsemployés Then
        'puisqu'il y a eu un ou des changements, enregistrement de tous les noms des employés lors de la désactivation de la feuille serviceencours
            nbreemployésfin = 0
            For col = 2 To 16 'de la colonne B à la colonne Q
            If Worksheets(serviceencours).Cells(1, col) <> "" Then nbreemployésfin = nbreemployésfin + 1 'nombre d'employés dans la feuille serviceencours lors du changement de feuille
            employésfin(col) = Worksheets(serviceencours).Cells(1, col) 'nom des employés lors de l'activation de la feuille serviceencours
            Next col
         'puisqu'il y a eu un ou des changements, on transfère les (nouveaux) noms des employés
         Transfertnomsemployés
        'End If

End Sub
=================__________________________________
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   'Rem : La macro Private Sub Workbook_SheetDeactivate fonctionne avant la macro Sub Workbook_SheetActivate
   'MsgBox ("Activate")
   ModifPlagenomsemployés = False 'initialisation de ModifPlage à 0 lors de la sélection d'une nouvelle feuille
   serviceencours = ActiveSheet.Name

   If serviceencours <> "Database" Then
   macroemployésdébut
   End If

End Sub
=====================______________________________________________
Sub macroemployésdébut()

   nbreemployésdébut = 0
   For col = 2 To 16 'de la colonne B à la colonne Q
   If Cells(1, col) <> "" Then nbreemployésdébut = nbreemployésdébut + 1 'Nombre d'employés de la nouvelle feuille service en cours
   employésdébut(col) = Cells(1, col) 'Noms des employés dans la nouvelle feuille service en cours
   Next col

End Sub
===================__________________________________________________
Sub Transfertnomsemployés()
Dim listefeuilledestinataire(1 To 100)

'INSCRIRE CI-DESSOUS LE CHEMIN COMPLET DONNANT L'ACCES AU REPERTOIRE CONTENANT LES FICHIERS ANNUELS DE CHAQUE SERVICE
Chemin = "C:\Users\FORUM\"


Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements

Fichierannuel = serviceencours & ".xls"
'OUVERTURE DU FICHIER ANNUEL DU SERVICE CORRESPONDANT
Workbooks.Open Chemin & Fichierannuel, False
Set Wbkdestinataire = Workbooks(Fichierannuel)
Wbkdestinataire.Activate

'_______________
'LISTE DES FEUILLES DE WBKDESTINATAIRE
i = 1
For Each Sht In Wbkdestinataire.Sheets
listefeuilledestinataire(i) = Sht.Name
i = i + 1
Next Sht


'________________
'CHANGEMENT DE CERTAINS NOMS D'ONGLETS
For col = 2 To 16
If (employésdébut(col) <> "") * (employésdébut(col) <> employésfin(col)) Then
x = Application.Match(employésdébut(col), listefeuilledestinataire, 0)
If Not IsError(x) Then
If employésfin(col) <> "" Then
Wbkdestinataire.Worksheets(x).Name = employésfin(col)
Wbkdestinataire.Worksheets(x).Range("E2") = employésfin(col)   'on met le nom de l'employé dans E2
listefeuilledestinataire(x) = employésfin(col) 'on modifie listefeuilledestinataire en conséquence
End If
End If
End If
Next col

'________________
'CREATION DE NOUVELLES FEUILLES
For i = 2 To nbreemployésfin + 1
If employésfin(i) <> "" Then
x = Application.Match(employésfin(i), listefeuilledestinataire, 0)
If IsError(x) Then
'If Application.CountA(plage)
nbredongletsfichierannuel = Workbooks(Fichierannuel).Worksheets.Count
Wbkdestinataire.Worksheets("Modèle").Copy After:=Wbkdestinataire.Worksheets(nbredongletsfichierannuel) 'création de la nouvelle feuille
Wbkdestinataire.Worksheets(nbredongletsfichierannuel + 1).Name = employésfin(i) 'On nomme le nouvel onglet avec le nom de l'empoyé
Wbkdestinataire.Worksheets(employésfin(i)).Range("E2") = employésfin(i) 'et on met ce nom dans E2
End If
End If
Next i

'FERMETURE DE CE FICHIER ANNUEL
Wbkdestinataire.Close SaveChanges:=True

Application.EnableEvents = True 'réactive les évènements (Open, Active...)
Application.ScreenUpdating = True

End Sub

D'avance merci

@ plus

 

[David Aubert]

Bonjour Cisco,
Est ce que les noms, prénoms présents dans le fichier et en photo d’ecran sont anonymisés?
Cela ne me semble pas être le cas.
J’en profite pour rappeler qu’il est interdit de publier des données confidentielles et à caractères personnel sur Xld et sur tout autres sites d’ailleurs.
Outre la charte qui le précise, ceci est interdit par la loi, vous pouvez être attaqués personnelllement (faute grave) pour cela et le site aussi.
Si tes données sont anonymysées cela m’aura permis de faire ce rappel et sinon merci de supprimer ces éléments immédiatement.
Bonne journée
David

 

[CISCO]

Bonjour

Les noms sont anonymisés.

@ plus

 

[eriiic]

Bonjour,

Houla, il y a de quoi lire... Et 3-4 fois pour essayer d'assimiler ;-)
Allez, je me lance pour ouvrir le débat

1) je trouve cette technique pas mal. Ca permet de regrouper toutes les modifs pour les faire d'un coup.
Le faire au fil de l'eau risque d'entrainer des ralentissements avec les ouvertures de fichiers.
Ce n'est pas agréable, et peut causer des erreurs de saisies si ce n'est pas assez réactif.

2) tu changes bien .xls en .xlsx ou .xlsm dans le code ?
J'ai testé et pas eu d'erreur d'ouverture.
eric

 

[CISCO]

Bonsoir à tous

Bonjour,

Houla, il y a de quoi lire... Et 3-4 fois pour essayer d'assimiler ;-)

J'ai essayé d'être assez précis, mais ce n'est pas évident.

1) je trouve cette technique pas mal. Ca permet de regrouper toutes les modifs pour les faire d'un coup.

C'est exactement la méthode utilisée, mais s'il y avait mieux, j’apprécierai. Je ne sais si des changements faits au fil de l'eau donneraient un meilleur résultat.

2) tu changes bien .xls en .xlsx ou .xlsm dans le code ?
J'ai testé et pas eu d'erreur d'ouverture.
eric

Je n'ai fait qu'avec un & ".xls".

Merci pour ta réponse.

@ plus

 

[eriiic]

Ca pose quoi comme problème que tout soit exécuté à la fin ?

 

[CISCO]

Bonjour à tous, bonjour Eriiic

Aucun . Je posais cette question juste pour voir s'il existait quelque chose de mieux, pour ma culture personnelle.

@ plus

 

[eriiic]

Bonjour,

au fil de l'eau. Maintenant est-ce mieux ? Personnellement je ne le pense pas.
Le seul avantage que j'y vois c'est plus de sécurité pour éviter de refaire 2-3 saisies.
Mais bon, je pense que tu ne comptes pas avoir un fichier qui plante tous les 3 jours, ou plein de coupures EDF ;-)
eric