{"id":373,"date":"2022-11-24T13:15:25","date_gmt":"2022-11-24T12:15:25","guid":{"rendered":"https:\/\/68600.fr\/D6D\/?p=373"},"modified":"2023-01-09T18:52:44","modified_gmt":"2023-01-09T17:52:44","slug":"developpement-fichiers-ics-et-calendrier-partie-3","status":"publish","type":"post","link":"https:\/\/68600.fr\/D6D\/developpement-fichiers-ics-et-calendrier-partie-3\/","title":{"rendered":"D\u00e9veloppement- Fichiers ICS et Calendrier &#8211; Partie 3"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">S\u00e9rie de 3 articles sur les fichiers ICS : <a href=\"https:\/\/68600.fr\/D6D\/developpement-fichiers-ics-et-calendrier-partie-1\/\" data-type=\"URL\" data-id=\"https:\/\/68600.fr\/D6D\/developpement-fichiers-ics-et-calendrier-partie-1\/\">(1) D\u00e9finition<\/a>; <a href=\"https:\/\/68600.fr\/D6D\/developpement-fichiers-ics-et-calendrier-partie-2\/\" data-type=\"URL\" data-id=\"https:\/\/68600.fr\/D6D\/developpement-fichiers-ics-et-calendrier-partie-2\/\">(2) Cr\u00e9ation et Partage<\/a>; (3) Automatisation Excel &gt; Outlook<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Partie 3: Cr\u00e9er une Interface entre Excel et Outlook<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Mon but : exporter la cellule Excel s\u00e9lectionn\u00e9e vers un rendez-vous Outlook pour \u00e9viter de devoir rentrer mes rendez-vous en double.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Pr\u00e9 requis :<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"791\" height=\"625\" src=\"https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-1.png\" alt=\"\" class=\"wp-image-382\" srcset=\"https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-1.png 791w, https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-1-300x237.png 300w, https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-1-768x607.png 768w\" sizes=\"auto, (max-width: 791px) 100vw, 791px\" \/><figcaption class=\"wp-element-caption\">Exemple de Fichier Excel<\/figcaption><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Ce fichier Excel contenant un calendrier en mode colonne de mois et lignes de jours.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ignorez l\u2019anglais car peu importe la langue utilis\u00e9e nous travaillerons avec les chiffres. Admettons donc :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Cellule H1 contenant l&rsquo;ann\u00e9e (2022)<\/li>\n\n\n\n<li>Colone L et P &#8211; Ligne 1 : mois en chiffre<\/li>\n\n\n\n<li>Colone L et P &#8211; Lignes 2 \u00e0 32 : jour du mois en chiffres<\/li>\n\n\n\n<li>Les r\u00e9unions toujours formatt\u00e9es en utilisant HH:MM Texte (Heure et Minutes sur 2 chiffres)<\/li>\n\n\n\n<li>Utilisation : s\u00e9lectionner cellule M17 &gt; appuyer sur g\u00e9n\u00e9rer &gt; Outlook ouvert avec les champs pr\u00e9 remplis.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Pour effectuer cette action, il faut passer en mode \u00ab\u00a0d\u00e9veloppeur\u00a0\u00bb sur Excel : Cliquer sur &lsquo;View&rsquo; et &lsquo;Macro&rsquo; . Voici les macros utilis\u00e9es.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>---- \nSub Export_ICS_Vers_Outlook()\nDim ICS_str As String\n    ICS_str = \"BEGIN:VCALENDAR\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"VERSION:2.0\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"PRODID:-\/\/68600.fr\/\/iCal Event Maker\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"CALSCALE:GREGORIAN\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"BEGIN:VTIMEZONE\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZID:Europe\/Berlin\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"BEGIN:STANDARD\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZNAME:CET\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZOFFSETFROM:+0200\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZOFFSETTO:+0100\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"DTSTART:19701025T030000\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"RRULE:FREQ=YEARLY;BYMONTH=10;BYDAY=-1SU\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"END:STANDARD\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"BEGIN:DAYLIGHT\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZNAME:CEST\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZOFFSETFROM:+0100\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZOFFSETTO:+0200\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"DTSTART:19700329T020000\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=-1SU\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"END:DAYLIGHT\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"END:VTIMEZONE\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"LAST-MODIFIED:20201011T015911Z\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TZURL:http:\/\/tzurl.org\/zoneinfo-outlook\/Europe\/Berlin\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"X-LIC-LOCATION:Europe\/Berlin\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"BEGIN:VEVENT\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"ATTENDEE;CN=\" &amp; \"\"\"\" &amp; \"Papa\" &amp; \"\"\"\" &amp; \";RSVP=TRUE:mailto:papi@gmail.com\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"CLASS:PRIVATE\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"CATEGORIES:Important\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"DTSTAMP:20220823T131634Z\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"UID:'D6D\" &amp; Int((100000000 * Rnd) + 1) &amp; \"'\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"DTSTART;TZID=Europe\/Berlin:MyDateStart\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"DTEND;TZID=Europe\/Berlin:MyDateEnd\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"SUMMARY;LANGUAGE=en-us:MyTitle\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"DESCRIPTION:MyDescription\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"LOCATION:MyLocation\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TRANSP:OPAQUE\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"X-MICROSOFT-CDO-BUSYSTATUS:FREE\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"BEGIN:VALARM\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"ACTION:DISPLAY\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"DESCRIPTION:Reminder\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"TRIGGER:-PT15M\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"END:VALARM\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"END:VEVENT\" &amp; vbCrLf\n    ICS_str = ICS_str &amp; \"END:VCALENDAR\" &amp; vbCrLf\n\n'MsgBox ICS_str\n\nMyRow = ActiveCell.Row\nMyCol = ActiveCell.Column\nMeetDay = 0\nMeetMonth = 1\nMeetTime = 18                                           'Default Meeting Time\nMeetText = ActiveCell.Value                             'Default Meeting Text\nIf (Len(ActiveCell.Value) &gt; 2) Then                      'If more than  digits then we run the rest\n    If (Left(ActiveCell.Value, 2) &lt; 24) Then                'If 2 digits then it is a valid info for time\n        MeetTime = Left(MeetText, 2)                        'Extract new Meeting Time\n        MeetText = Right(MeetText, Len(MeetText) - 3)       'Extract new Meeting Text\n        If (Left(MeetText, 2) &lt; 60) Then\n            MeetTime = MeetTime &amp; Left(MeetText, 2)\n            MeetText = Right(MeetText, Len(MeetText) - 3)\n        End If\n        If Len(MeetTime &gt; 3) Then\n            MeetTime = MeetTime &amp; \"00\"\n        Else\n            MeetTime = MeetTime &amp; \"0000\"\n        End If\n        \n    End If\n    'MsgBox (MeetTime)\n    If Len(MeetTime &lt; 3) Then MeetTime = MeetTime &amp; \"00\"    'Complement to have Time on 6 digits (HHMMSS)\n    ActiveCell.Offset(0, -1).Select                         'Select Day date in left column\n    While MeetDay = 0\n     If (ActiveCell.Value &gt; 0 And ActiveCell.Value &lt; 32) Then\n       MeetDay = ActiveCell.Value\n      Else\n       ActiveCell.Offset(0, -1).Select                      'Else Select Day date in one more left column\n        If (ActiveCell.Value &gt; 0 And ActiveCell.Value &lt; 32) Then\n            MeetDay = ActiveCell.Value\n        End If\n        'MsgBox ActiveCell.Value &amp; \" \" &amp; ActiveCell.Row &amp; \" \" &amp; ActiveCell.Column\n      End If\n      If (MeetDay &lt; 10) Then MeetDay = \"0\" &amp; MeetDay\n    Wend\n    \n    ActiveCell.Offset((1 - ActiveCell.Row), 0).Select       'Select Month date in top row same column\n    MeetMonth = ActiveCell.Value\n    If (MeetMonth &lt; 10) Then MeetMonth = \"0\" &amp; MeetMonth\n    \n    Range(\"J1\").Select                                      'Select Year date in top row same column\n    MeetYear = ActiveCell.Value\n    \n    ICS_str = Replace(ICS_str, \"MyDescription\", MeetText)\n    ICS_str = Replace(ICS_str, \"MyTitle\", MeetText)\n    ICS_str = Replace(ICS_str, \"MyLocation\", \"Volgelsheim\")\n    '20221006T100000\n    ICS_str = Replace(ICS_str, \"MyDateStart\", MeetYear &amp; MeetMonth &amp; MeetDay &amp; \"T\" &amp; MeetTime &amp; \"00\")\n    ICS_str = Replace(ICS_str, \"MyDateEnd\", MeetYear &amp; MeetMonth &amp; MeetDay &amp; \"T\" &amp; MeetTime &amp; \"10\")\n    \n    Range(Cells(MyRow, MyCol), Cells(MyRow, MyCol)).Select\n    \n    ICS_Filename = MeetYear &amp; MeetMonth &amp; MeetDay &amp; \"_\" &amp; Replace(MeetText, \" \", \"_\") &amp; \".ics\"\n    lg = Ecrire_Txt(ActiveWorkbook.Path &amp; \"\\\" &amp; ICS_Filename, ICS_str)\n    \n    Call convertTxttoUTF(ActiveWorkbook.Path &amp; \"\\\" &amp; ICS_Filename, ActiveWorkbook.Path &amp; \"\\\" &amp; \"UTF\" &amp; ICS_Filename)\n    \n      'MsgBox \"Export vers .ics = Ok\"\n    If lg = 0 Then Shell (\"C:\\Program Files\\Microsoft Office\\root\\Office16\\OUTLOOK.EXE\" &amp; \" \/ical \" &amp; ActiveWorkbook.Path &amp; \"\\\" &amp; \"UTF\" &amp; ICS_Filename)\n    '\/ical &lt;icsfilename&gt;\n    \n   End If\nEnd Sub\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Enregister la macro, repartir sur une cellule du tableau Excel et essayer. <br>Note : Le fichier ICS et fichier ICS format UTF seront sauvegard\u00e9s dans le meme dossier que fichier Excel utilis\u00e9.<br>Note 2: La dur\u00e9e n&rsquo;\u00e9tant pas sp\u00e9cifi\u00e9e dans le calendrier Excel, la r\u00e9union aura une dur\u00e9e d&rsquo;une seconde; c&rsquo;est arbitraire et il me suffit de changer cela dans Outlook quand tout le reste sera d\u00e9j\u00e0 en place !<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u00c9tape finale, ajouter un moyen de lancement facile (plutot que naviguer dans les menus) : ajouter un bouton : <\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>Passez en <a href=\"https:\/\/support.microsoft.com\/fr-fr\/office\/afficher-l-onglet-d%C3%A9veloppeur-e1192344-5e56-4d45-931b-e5fd9bea2d45\">mode d\u00e9veloppeur<\/a>\n<ul class=\"wp-block-list\">\n<li>Sous l\u2019onglet&nbsp;<strong>Fichier<\/strong>&nbsp;, acc\u00e9dez \u00e0&nbsp;<strong>Options&nbsp;<\/strong>&gt;&nbsp;<strong>Personnaliser le ruban<\/strong>.<\/li>\n\n\n\n<li>Sous&nbsp;<strong>Personnaliser le ruban<\/strong>&nbsp;et&nbsp;<strong>Onglets principaux<\/strong>, activez la case \u00e0 cocher&nbsp;<strong>D\u00e9veloppeur<\/strong>.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>S\u00e9lectionnez le menu d\u00e9veloppeur, ins\u00e9rer &gt; bouton<\/li>\n\n\n\n<li>Dessinez la taille et position du bouton (un gros rectangle )<\/li>\n\n\n\n<li>D\u00e9finissez la macro \u00ab\u00a0Export_ICS_Vers_Outlook\u00a0\u00bbqui lui sera affect\u00e9. <\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"365\" src=\"https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-3.png\" alt=\"\" class=\"wp-image-392\" srcset=\"https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-3.png 1024w, https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-3-300x107.png 300w, https:\/\/68600.fr\/D6D\/wp-content\/uploads\/2022\/11\/image-3-768x274.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\">Insertion d&rsquo;un bouton (\u00e9tape 3)<\/figcaption><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Bilan <\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Comme pour la version HTML ou plutot PHP, j&rsquo;ai adapt\u00e9 les champs \u00e0 mon besoin, r\u00e9union priv\u00e9e, en mode important, avec rappel et qui ne bloque pas mon calendrier, envoi de l&rsquo;invitation par courriel \u00e0 un contact. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Cela conclue cette aventure un peu poilue dans le monde des fichier iCalendar, totalement hors sujet de D6D mais tout de m\u00eame proche de l&rsquo;automatisation \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"<p>S\u00e9rie de 3 articles sur les fichiers ICS : (1) D\u00e9finition; (2) Cr\u00e9ation et Partage; (3) Automatisation Excel &gt; Outlook Partie 3: Cr\u00e9er une Interface entre Excel et Outlook Mon but : exporter la cellule Excel s\u00e9lectionn\u00e9e vers un rendez-vous Outlook pour \u00e9viter de devoir rentrer mes rendez-vous en double. Pr\u00e9 requis : Ce fichier [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":382,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32],"tags":[40,38,37,39],"class_list":["post-373","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-informatique","tag-excel","tag-icalendar","tag-ics","tag-vba"],"_links":{"self":[{"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/posts\/373","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/comments?post=373"}],"version-history":[{"count":12,"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/posts\/373\/revisions"}],"predecessor-version":[{"id":474,"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/posts\/373\/revisions\/474"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/media\/382"}],"wp:attachment":[{"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/media?parent=373"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/categories?post=373"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/68600.fr\/D6D\/wp-json\/wp\/v2\/tags?post=373"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}