XL 2019 Calendrier scolaire

[FredMa]

Bonjour,
Je souhaiterais générer un calendriers scolaire et différents plannings à partir d'un import ICAL des vacances scolaires (https://www.data.gouv.fr/fr/datasets/le-calendrier-scolaire/)
Je pensais partir d'un script créé par @leakim (https://www.excel-pratique.com/d/application/download?id=386) mais le format du Ical a changé du coup sa macro ne fonctionne plus et serait à reprendre.
Sinon, il y a aussi la feuille de @goube qui pourrait être une piste (https://excel-downloads.com/threads/calendrier.20071568/post-20543917)
Voici un fichier présentant mon projet mais le projet dépasse mes compétences ;(
A voir si je peux avoir un peu d'aide..
Merci
Fred

 

[Compte Supprimé 979]

Bonjour Fred,

Désolé, mais pour moi l'import des calendrier se fait parfaitement bien
En revanche beaucoup d'erreurs dans votre fichier avec des #REF! dans les plages nommées

Voici le fichier avec les quelques corrections que j'ai pu voir

A+

 

[FredMa]

Merci BrunoM45 pour le coup de main.
L'onglet TBL ne se complétait plus dans mon classeur. Avec vos corrections ça marche !
Les erreurs #REF! doivent provenir d'un problème avec la macro qui traite le fichier ics.
En effet, celle-ci supprime certaines lignes et enregistre d'autres dans l'onglet BD_CAL.
Cela fonctionne bien jusqu'à la 12ème ligne de BD_CAL.
Après cela ne fonctionne plus car le "Pont de l'Ascension" est ajouté et les dates se décalent ensuite toutes :
Pont de l'Ascension 06/07/2019 - 02/06/2019
Vacances d'Été 30/08/2019 - 01/09/2019
Vacances d'Été (prérentrée Enseignants ) 19/10/2019 01/09/2019
Cela vient du fait qu'il manque une date de début du Pont de l'Ascension.
Si descend d'une ligne la colonne de début de vacances dans l'onglet BD_CAL, les dates se recalent.
Pont de l'Ascension - 02/06/2019
Vacances d'Été 06/07/2019 - 01/09/2019
Vacances d'Été (prérentrée Enseignants ) 30/08/2019 01/09/2019
Par contre le calendrier n'affiche ni les jours fériés, ni les périodes de vacances.
Le calendrier cherche également dans BD_CAL le nom de la zone alors que cette info a disparu

Fred

 

[FredMa]

Bonjour,

J'ai simplifié la maco d'import de fichier ics.
Il n'y a plus de bug de décalage des dates lors de l'import.
L'onglet Calendrier affiche bien un liseré vert pour les dates de vacances.

Il reste encore une bizarrerie car cela ne fonctionne que pour les 12 premières lignes de l'onglet BD_CAL où sont renseignées les DVac (début vacances) et les FVac (fin vacances) qui sont nécessaires dajns la mise en forme conditionnelle de l'onglet Calendrier : =ET(C3<>"";SOMMEPROD((C3>=DVac)*(C3<=FVac))>0)

Voyez-vous ce qui coince dans la macro pour celle-ci n'aille pas jusqu'au bout ?
Je soupçonne ces 2 lignes mais comme je ne les comprends pas, pas facile pour moi....

ActiveWorkbook.Names("DVac").RefersToR1C1 = "=BD_CAL!R3C2:R12C2" 'Définition de la zone Début Vacances
ActiveWorkbook.Names("FVac").RefersToR1C1 = "=BD_CAL!R3C3:R12C3" 'Définition de la zone Fin Vacances

Merci

Fred

 

[Compte Supprimé 979]

Bonjour FredMa

Comme tu mémorises la "derligne" des congés, il faut utiliser cette variable,
Sinon le nombre de ligne reste fixe

  ActiveWorkbook.Names("DVac").RefersToR1C1 = "=BD_CAL!R3C2:R" & derligne & "C2"        'Définition de la zone Début Vacances
  ActiveWorkbook.Names("FVac").RefersToR1C1 = "=BD_CAL!R3C3:R" & derligne & "C3"         'Définition de la zone Fin Vacances

R = Row = Ligne / C = Column = Colonne

A+

 

[FredMa]

Merci BrunoM45 mais cela ne change rien ;(
Un pb de syntaxe ?
Fred

 

[FredMa]

Je viens de comprendre ces 2 lignes. Elles sont pourtant correctes :
'Ces lignes donnent le nom "Dvac" ou "Fvac" à une plage de données situées sur la feuille BD_CAL
' La plage pour Dvac va de la ligne(R) n°3 à la dernière ligne derligne, (variable défine au-dessus) sur la colonne(C) C2
' R = Row = Ligne / C = Column = Colonne
ActiveWorkbook.Names("DVac").RefersToR1C1 = "=BD_CAL!R3C2:R" & derligne & "C2" 'Définition de la zone Début Vacances
ActiveWorkbook.Names("FVac").RefersToR1C1 = "=BD_CAL!R3C3:R" & derligne & "C3" 'Définition de la zone Fin Vacances

Il doit y avoir autre chose qui coince ;(

 

[sylvanu]

Bonjour FredMa, Bruno,
Dans votre PJ et Gestionnaire de noms vous avez de multiples déclaration de DVAC er FVAC.
Une définie sur le classeur et deux définies sur le Calendrier et sur TBL :

J'ai supprimé les déclarations et n'ai gardé que la déclaration sur l'étendue du classeur.
Puis mis ces deux lignes de Bruno dans le code.

ActiveWorkbook.Names("DVac").RefersToR1C1 = "=BD_CAL!R3C2:R" & derligne & "C2"        'Définition de la zone Début Vacances
ActiveWorkbook.Names("FVac").RefersToR1C1 = "=BD_CAL!R3C3:R" & derligne & "C3"         'Définition de la zone Fin Vacances

Et la déclaration s'effectue correctement.

Je pense qu'il se mélangeait les pinceaux avec ces multiples déclarations.

 

[FredMa]

Super, cela fonctionne !

Je regarde maintenant la mise en forme conditionnelle pour la comprendre.
=ET(D3<>"";SOMMEPROD((D3>=DVac)*(D3<=FVac))>0)
La fonction ET renvoie la valeur VRAI si tous ses arguments produisent un résultat vrai,
et la valeur FAUX si au moins l'un des arguments produit un résultat faux.
Si vrai -> on affiche le liseré vert
Si faux -> on ne l'affiche pas

On teste sur la première date du Calendrier (cellule D3)
Il faut qu'elle soit non nulle et qu'elle soit supérieure à la date de début de vacances DVac et inférieure à la date de fin de vacances FVac.
Cela se comprend par contre, je ne comprends pas où aller rechercher les valeurs DVac et FVac de l'année en cours. Ce sont des tableaux à priori.
Comment afficher par exemple dans une cellule les valeurs DVac et FVac des vacances d'automne de l'année en cours ?
Vacances d'Automne du DVac à FVac

Merci !

 

[sylvanu]

Re,
Dvac et Fvac sont les deux plages que vous avez définies en VBA.
Pour "voir" où elles se trouvent :

Comment afficher par exemple dans une cellule les valeurs DVac et FVac des vacances d'automne de l'année en cours ?

Vous allez avoir du mal à trouver car "vacances d'automne" n'est pas référencé dans la liste.

 

[FredMa]

oui oui j'avais bien vu ces 2 colonnes de BD_CAL.
Ce qui m'interrogeais c'était la mise en forme conditionnelle et le contenu de DVac FVac
=ET(D3<>"";SOMMEPROD((D3>=DVac)*(D3<=FVac))>0)

 

[sylvanu]

Dvac est une liste de dates de début de vacances, Fvac est une liste de dates de fin de vacances.

=ET(D3<>"";SOMMEPROD((D3>=DVac)*(D3<=FVac))>0)

On regarde si la cellule date est non vide (D3), et si cette date est >= à une date de début ET <= à une date de fin.
En d'autres termes on regarde si cette date est incluse dans un des intervalles Début-Fin.

SOMMEPROD (SOMMEPROD, fonction) - Support Microsoft

Comment utiliser la fonction SUMPRODUCT dans Excel, l’une des fonctions mathématiques et trig d’Excel, en utilisant la multiplication, l’addition, la soustraction et/ou la division pour retourner la somme des produits des plages ou tableaux correspondants.

support.microsoft.com

 

[FredMa]

Et du coup pour trouver une valeur DVac ou FVac correspondant à une année et un intitulé "Hiver", "Printemps", il vaut mieux faire une recherche sur l'onglet BD_Cal où il y a un moyen plus simple en recherchant dans les listes de vacances DVac et FVac.
Désolé de ces questions et merci pour la patience

 

[sylvanu]

Par exemple avec :

=SOMME.SI.ENS(B3:B23;A3:A23;J15;B3:B23;">=1/1/"&J17;C3:C23;"<1/1/"&J17+1)

Voir PJ feuille BD_CAL

 

[FredMa]

Avec vos différents conseils et notamment l'utilisation du "Gestionnaire de nom", j'ai pu faire pas mal de corrections.
Le générateur de calendrier à partir d'un fichier ics fonctionne très bien.

Maintenant, je dispose de 2 onglets avec toutes les données nécessaires pour générer d'autres documents :
- BD_CAL qui a toutes les dates de vacances : début de vacances DVac et fin de vacances FVac.
- TBL qui a tous les jours fériés.

Je cherche donc maintenant à faire un planning des jours de classe, c'est à dire tous les lundis-mardis-jeudis et vendredis de septembre à début juillet sans les vacances et les jours fériés. C'est l'onglet "Jours de classe".
Les jours sont regroupés par semaines numérotées de 1 à 36 semaines et à chaque vacance, il y a un changement de période.
Je ne sais par contre pas quel outil utiliser : des formules ou une nouvelle macro pour aller extraire les informations de l'année sélectionnée.
L'onglet BD_CAL peut changer de nombre de lignes (derligne = 62 actuellement),

Une piste à me proposer ?
Merci
Fred