Tatiak

Excel, Vba, Vbs


01 juillet 2018

Relevé d'activité

Pour répondre à Green_Lemon, voici un planning de relevé d'activité.

La demande concerne "un planning de gestion d'entreprise afin de comptabiliser le nombre d'heures consacrés chez les clients pour un nombre X d'employés".

Une contrainte est de proposer une vision mensuelle et une vision hebdomadaire de l'activité en synchronisant ces 2 plannings.

La demande précise aussi que plusieurs salariés peuvent intervenir chez un même client.

Et si j'étais le manager de cette équipe, je chercherai à détailler l'activité pour chaque client pour le mois, par exemple pour faire la facturation au client.

Pour répondre à cette question, voici un planning avec 4 onglets :

  •  un onglet "Data", pour enregistrer les info (nb cet onglet peut être masqué)
  •  un onglet "Mensuel", de saisie
  •  un onglet "Hebdo", de saisie
  •  un onglet "Bilan Clients", listing mensuel par client (en D), totalisant  le nb d'heures à facturer (en E), détaillant les salariés qui sont intervenus(en F) et détaillant pour chaque client les dates/heures/intervenants (en G). Pour cette dernière liste, agrandir la barre de formules pour voir l'ensemble des lignes (cf exemple pour le Client2 en juin 2018)


Pour tous les onglets, la synchronisation des info se fait soit par sélection de l'onglet soit après changement de date (année et mois ou nunéro de semaine)

Avec ce planning, vous pourrez donc suivre l'activité de vos salariés et faire vos facturations.

Si cette démo vous est utile, n'hésitez pas à me laisser votre commentaire!

 

Téléchargement de : Planning_Greg.xlsm

Posté par tatiak à 10:20 - Applications - Commentaires [0] - Permalien [#]

07 avril 2018

Export données Excel vers ics

Pour essayer de répondre à une question de Lionel (pseudo arthour973) voici une démo de création d'une fichier .ics à partir d'un fichier Excel.

L'idée de cette démo est de pouvoir générer un fichier lisible par un grand nombre d'agendas ('Calendrier' de Windows, 'Outlook' ou 'Google Agenda' par exemple)

Une fois le fichier au format .ics généré, il suffit par exemple de l'envoyer par mail à un destinataire pour qu'il l'importe dans son agenda.

Pour l'import, par exemple avec l'appli 'Calendrier' de Windows il suffit de double-cliquer sur le fichier .ics, et de choisir l'option 'Ajouter au calendrier'

Voici donc une méthode qui peut servir à caller des rendez-vous avec plusieurs participants.

Si cette démo vous est utile, n'hésitez pas à me laisser votre commentaire!

 

Téléchargement de Export_ics.xlsm

Posté par tatiak à 13:44 - Excel-Divers - Commentaires [0] - Permalien [#]

15 mars 2018

PLANNING DE GANTT

Il est temps de réactualiser mes outils "de base".

Pour aujourd'hui voici une nouvelle version de mon planning de Gantt.

Dans ce fichier les données sont dans l'onglet "Data", le graphe dans l'onglet "Planning"

C'est un planning sur 3 mois glissants :

  • seules les tâches à réaliser sur ces 3 mois sont représentées
  • pour chaque tâche, dans l'histogramme est affiché un rappel du n°+ intitulé+ %réalisé
  • le %réalisé d'une tâche est représenté dans l'histogramme sous forme d'une barre vert-foncée en bas de l'histogramme
  • chaque histogramme prend la couleur de la cellule de la même ligne/colonne A du planning


Le graphe se réactualise soit au retour sur la feuille "Planning", soit au changement de mois de début de graphe

 

Si ce Gantt vous est utile, n'hésitez pas à me laisser votre commentaire!

 

Téléchargement de : Gantt_2018d.xlsm

Téléchargement de : Gantt_2018f.xlsm

Téléchargement de : Gantt_2018g.xlsm

 

* Edit du 22/09/2018 : Ajout version 2018g pour répondre aux demandes d'une visualisation hebdomadaire

* Edit du 09/06/2018 : Ajout version 2018f pour répondre à Sweetee : coloration des histogrammes en fonction de la couleur indiquée en colonne A de l'onglet Data

* Edit du 28/03/2018 : Ajout des n° de semaine pour répondre à la demande de doro1006. Et ajout d'une liste de choix pour le nombre de mois à afficher sur le planning

Posté par tatiak à 16:24 - Excel-Gantt - Commentaires [12] - Permalien [#]

07 mars 2018

VILLE ET CODES POSTAUX


Un besoin fréquent dans les appli Excel est de trouver un code postal en fonction d'une ville ou inversement une ville en fonction d'un code postal.

Pour cela, il est simple d'utiliser la base officielle des codes postaux (proposée en OpenData) :
https://data.opendatasoft.com/explore/dataset/laposte_hexasmal@datanova/

Mais inutile de coller les données dans votre fichier Excel, n'alourdissez pas votre fichier pour rien!

Voici un exemple d'utilisation de cette base des CP. Voyons plutôt :

  • Dans ce fichier, vous trouverez d'abord le lien pour télécharger la base 
  • Une fois le fichier 'laposte_hexasmal.csv' téléchargé, vous le placez dans le même dossier que le CP.xlsm. Vous noterez au passage que ce fichier démo est léger (35ko)
  • Le fichier contient un userform s'ouvrant suite à un clic sur le bouton "Interface"
  • L'interface est simple : 2 champs 'Code postal' et 'Ville' dans lesquels on saisit la recherche soit dans l'un, soit dans l'autre.
  • Au fur et à mesure de la saisie, l'info trouvée est proposée sous forme de liste sélectionnable si plusieurs réponses ou collée dans le champs recherché en cas de réponse unique.


Vous noterez que cette méthode rend disponible l'ensemble des codes postaux de France dans votre appli.

Si vous utilisez cette méthode, merci d'indiquer la provenance de mon code dans votre appli !

Et n'oubliez pas de me laisser un commentaire si cette démo vous est utile !

 

Téléchargement de : CP.xlsm

Téléchargement de : CP_INSEE.xlsm

Posté par tatiak à 11:54 - Excel-Howto - Commentaires [4] - Permalien [#]

17 janvier 2018

RESERVATION DE CHAMBRES D'HÔTES

Pour essayer de répondre au besoin de Jérôme, voici une appli de réservation de chambres d'hôtes.

L'idée principale est de visualiser l'occupation des chambres sur un planning et d'éditer les factures.

Capture

Pour ce faire, on enregistre les réservations en saisissant :

  •  les renseignements sur le client (nom, téléphone, ...)
  •  la ou les chambres réservées (chaque chambre pouvant être réservée à des dates différentes)
  •  les prestations retenues pour chacune des chambres (p'tits déj, repas, taxe, ...)

 

A noter :

* La saisie des renseignements sur le séjour et sur les prestations se fait en cliquant directement sur la liste correspondante et dans la colonne ad-hoc.

* L'affichage de la liste des prestations ne se fait qu'après saisie d'un nom, d'une chambre et des dates d'arrivées et de départ.

* L'appli vous signale si la chambre choisie est déjà occupée sur la période indiquée.

* A la saisie des prestations, le total dû pour la chambre s'affiche en face de la ligne correspondante

* L'appli est en mesure d'éditer les factures, soit individuellement pour chaque chambre, soit pour l'ensemble des chambres réservées par le même client. Pour ce faire il suffit d'un clic gauche sur l'icône 'Facture' en bout de la ligne chambre (ou sur l'icône de l'entête pour la facture globale)

* Par la suite vous retrouverez la facture par un clic droit sur ces mêmes icônes.

 

Capture2

Avec mes tests, il me semble que cette appli doit être réservée à des petites structures d'un maximum d'une vingtaine de chambre de type chambres d'hôtes ou petit hôtel. Au-delà l'utilisation de cette appli ne me semble pas raisonnable.

 

Comme d'habitude et malgré mes tests, je ne garantis pas que l'appli est exempt de bug ou de défaut.

Avant de l'utiliser 'en vrai', testez-là ! Et en utilisation courante, pensez à faire régulièrement des sauvegardes.

Et si l'appli correspond à vos besoins, n'oubliez-pas de me laisser un commentaire !

 

En réponse au commentaire de Romain, voici en complément un module de "statistique". Avec ce fichier complémentaire 'RESAH_STAT', vous obtenez les 'scores mensuels' : nombre de chambres occupées et nombre de clients par jour pour le mois affiché. Le fonctionnement est simple : après avoir activer les macro, vous choisissez le mois  ou vous cliquez sur le bouton 'MAJ' de mise à jour du tableau. (Ce fichier est à placer dans le même dossier que celui de l'appli).

 

Téléchargement de : RESAHOTEL_2018_v2.8.zip

Téléchargement de : RESAH_STAT_v0.2.xlsm

 

Edit du 28/05/2018 : version v2.8 - Correction bug (multilignes) - Merci Jean

Edit du 19/04/2018 : version v2.7 - catégories + Stat + améliorations diverses + correction bug création des répertoires

Edit du 03/03/2018 : version v2.6 - initialisation des répertoires utiles + améliorations diverses (code)

Edit du 02/03/2018 : version v2.5f - uniformisation du formatage des dates et heures

Edit du 28/02/2018 : version v2.5e - sauvegarde de la config + sauvegarde de la liste des chambres

Edit du 28/02/2018 : version v2.5d - sauvegarde de la config

Edit du 28/02/2018 : version v2.5c - pour compatibilité avec Excel 2007 (testé avec 2007 et 2016)

Edit du 07/02/2018 : version v2.5 - Amélioration du code + suppression possible des lignes de réservation et de prestation

Edit du 04/02/2018 : version v2.4 - Correction de bugs + réservation posible par sélection de cases d'une ligne du planning

Edit du 03/02/2018 : version v2.3 - Correction de bugs

Edit du 02/02/2018 : version v2.2 - Correction d'un bug (effacement de fiche) suite à signalement de Jean

Posté par tatiak à 18:33 - Applications - Commentaires [33] - Permalien [#]

04 novembre 2017

Radial Bar Chart sur Excel

Radial Bar Chart ou Diagramme à Barres Radial sur Excel

Radial_Bar


Pour aggrémenter vos rapports avec un autre style de graphique qu'un histogramme classique, vous pouvez faire appel à un diagramme à barres radial.
L'idée est d'utiliser un système de coordonnées polaires pour représenter vos données.

Comme je n'ai pas trouvé sur le net d'outil pour dessiner ce type de diagramme, j'ai fabriqué le mien en utilisant Excel.

Dans le fichier à télécharger, vous trouverez donc une liste d'indicateurs avec leurs scores respectifs et des codes couleurs. Un clic sur le bouton met à jour le graphique à droite des données.

Il est possible d'ajouter ou d'ôter des lignes d'indicateurs, et de modifier les codes couleurs pour dessiner votre graphique.

Si cet outil vous est utile, laissez-moi vos commentaires!

 

Téléchargement de : Radial_Bar_v1.2.xlsm

Posté par tatiak à 16:38 - Excel-Howto - Commentaires [2] - Permalien [#]

29 octobre 2017

Synchronisation Excel Access

Pour essayer de répondre à une question de Matt (pseudo chacal33) voici une démo de synchronisation de données entre un fichier Excel et une base Access.

L'idée de cette démo est de permettre d'interfacer le fichier Excel joint avec n'importe quelle base accdb (ou mdb).
Toutefois quelques limites, entre autres :

  • la base n'est pas protégée par mot de passe,
  • les index primaires de la base sont toujours les premiers champs des tables


L'utilisation est simple :
A la première ouverture du fichier, il est demandé de pointer vers un fichier Access à synchroniser.
Une fois la base choisie, il est créé un onglet par table, chaque onglet se synchronisant automatiquement sans autre manipulation de l'utilisateur.

A noter :

  • seules les lignes modifiées ou ajoutées font l'objet d'un UPDATE ou d'un INSERT, pour éviter d'allonger inutilement le temps de traitement (surtout pour une base importante)
  • pas de DELETE
  • à la fermeture du fichier xl, les données (du fichier xl) sont effacées
  • ce n'est qu'une démo => pas de contrôle de saisie des données, ni sur le type de données ni sur la cohérence avec les clefs primaires ou secondaires (pas de contrôle d'intégrité référentielle).


Pour stopper la synchro avec une base et retrouver un fichier 'neuf', il suffit exécutez la macro 'Reboot' (via Alt F8)
 
Pour tester, vous trouverez dans le zip joint : le fichier xlsm + 2 fichiers accdb avec data bidons

Ici, le code est ouvert pour vous permettre d'adapter le code à vos besoins.

Si cette démo vous est utile, laissez-moi un commentaire!

 

Téléchargement de : Synchro_Access_v0.4.zip

Posté par tatiak à 13:33 - Excel-Base de Données - Commentaires [2] - Permalien [#]

02 septembre 2017

Statistique des Vents (data de pioupiou.fr)

Capture2

Vous pratiquez une activité extérieure soumise aux conditions météo et en particulier dépendante des vents (windsurf, kite, voile, parapente, aéromodélisme, ...)

Pour choisir votre spot, il est alors intéressant d'avoir des statistiques des vents de l'endroit où vous envisagez d'aller : quels sont les vents dominants et quelles sont leurs vitesses ?
On peut également se poser la question après une journée de navigation : quelle était la vitesse réelle du vent quand j'étais sur l'eau ?

Pour répondre à ces questions, le site http://pioupiou.fr met à disposition une API qui permet de récupérer les données des capteurs installés.
Pour rappel les Pioupiou sont des capteurs permettant "d'observer le vent en live, n'importe où, même là où il n'y a ni électricité ni internet". Le site propose pour chaque spot les données en temps réel de direction et de vitesse du vent avec un rappel antérieur de 2 heures.
Il est également proposé de lire les données antérieures via son API.

C'est donc l'objet du fichier Excel que je vous propose aujourd'hui.
Son utilisation est simple :

  • 3 listes déroulantes de choix (spot/mois/année) pour récupérer les données.
  • une rose des vents indique alors la fréquence des directions des vents,
  • une première courbe indique les vitesses enregistrées (moyenne/mini/maxi) sur le mois complet,
  • une deuxième courbe indique les azimuts des vents sur le mois complet.

 

Capture


Pour une lecture plus précise, une barre de défilement permet de "zoomer" ces 2 dernières courbes sur chaque paire de jours du mois.

Le fichier proposé liste (à gauche) les capteurs installés en Bretagne. Pour les malchanceux habitant une autre région, il suffit de compléter la liste avec les données que vous trouverez sur le site Pioupiou. Chaque capteur y est identifié avec un numéro de code et éventuellement avec une dénomination (s'il est impératif de respecter le code, vous pouvez saisir la dénomination qui vous convient)

Vous voici avec un outil permettant d'analyser les conditions de vent de votre spot favori!

Pierre

Edit du 4 juin 2018 : version 1.8 - ajout de flèches pour indiquer la direction des vents

Edit du 9 janv 2018 : version 1.6 - correction du défaut signalé par Malc

Edit du 6 nov 2017 : version 1.3 - amélioration de la représentation de la "rose des vents"

Edit du 26 oct 2017 : version 1.2 - avec calcul de l'heure légale en France (UTC+1 ou UTC+2 selon date) + qq améliorations

 

Téléchargement de : Stat_Vents_v1.8.xlsm

Posté par tatiak à 09:38 - Applications - Commentaires [13] - Permalien [#]

23 juin 2017

Modélisation Tables Base de données

Modelisation_v0

Il me semble de plus en plus intéressant d'utiliser des tables Access depuis Excel.
En effet, même sans licence Access, il est possible de stocker ses données dans un fichier accdb par exemple pour alleger ses fichiers Excel et/ou 'protéger' les données de modification/effacement malencontreux.

Ceci étant dit, la première étape importante avant de se lancer dans le codage d'une appli Excel<=>accdb est de bien concevoir les tables à utiliser. Deux points sont à soigner : la structure des tables d'une part et les relations entre ces tables.
Pour visualiser ce que vous voulez mettre en place, rien de mieux que de dessiner votre modèle de base de données en faisant figurer vos tables (et leurs contenus) et les liens les reliant.

Pour ce faire, je vous propose aujourd'hui une petite appli Excel de modélisation.
L'utilisation est simple :

  • dans l'onglet 'Tables' vous complétez la structure de chacune de vos tables (colonnes A à H)
  • dans ce même onglet, vous creez les liaisons (colonnes J à L) que vous souhaitez en reprenant le numéro (num) d'ordre de vos champs. Dans l'exemple le champs Id de la tables CLIENTS est relié à l'Id_c de la table FACTURES avec une relation 1 à plusieurs (un client peut avoir plusieurs factures).
  • Dans l'onglet 'Diagramme', 3 boutons :
    •  'Actualiser Tables' : pour dessiner chaque tables. Ces tables pourront être repositionnées comme vous le souhaitez (notamment pour mieux visualiser les relations)
    •  'Actualiser Relations' : pour redessiner les liens entre tables
    •  'Créeer .accdb' : pour générer le fichier Access à la fin de votre modélisation. Lors de cette étape, il vous sera demandé de nommer votre base. A la fin du processus, un fichier log attestera du bon déroulement de la création des tables.
  • Pour repérer les clés sur ce diagramme (voir illustration) :
    • Les clés primaires seront précédées d'un * et seront en gras +soulignées
    • Les clés étrangères seront en gras + soulignées


L'outil, peut-être un peu simpliste, n'a pas d'autre ambition que de modéliser des petites bases de quelques tables pour celui qui de possède pas de licence Access. Il vous restera ensuite la tâche d'écrire le code pour lire et écrire dans ces tables => en utilisant par exempe cette méthode


N'hésitez pas à poster vos commentaires pour faire évoluer cette appli!
Bon téléchargement
Pierre

 

Téléchargement de : Modélisation_Tables_Access_v0.3.xlsm

 

Edit du 27/06/2017 : Pour répondre à la demande de Christophe : voici la version v0.3 qui permet de définir un mot de passe de son choix lors de la création de la base.
Secondairement pour utiliser cette base, il suffira de demander le mot de passe, par exemple à l'ouverture du fichier, puis de le stocker dans une variable 'public' (MDP par exemple). Cette variable 'MDP' sera à utiliser dans la chaine de connexion, du genre :
Cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & BDD & ";Jet OLEDB:Database Password=" & MDP & ";"
(BDD étant ici le nom de la base avec son chemin et son extension)
Avec une méthode de ce genre : pas de mot de passe en clair dans le code.

 

Posté par tatiak à 11:50 - Excel-Base de Données - Commentaires [3] - Permalien [#]

12 avril 2017

Suivi périodique de matériel

Pour répondre à Patrick, responsable sécurité d'une entreprise, voici une appli Excel pour assurer le suivi périodique de matériel.

De nombreux matériels divers (sécurité incendie, équipements sportifs, ... ) doivent être vérifiés-contrôlés plusieurs fois par an (du fait d'une réglementation ou pour répondre au plan de gestion de risques de votre entreprise).
L'idée de cette appli est d'établir chaque mois un planning des vérifications à faire, et de tenir à jour un historique des vérification pour chaque matériel.

Dans cette appli, une interface (bouton "Fiches Matériel") permet la saisie-modification des fiches de chaque matériel et la saisie des compte-rendus de vérification

Sur l'onglet "Liste_du_mois", le bouton "Liste du mois" dresse la liste des matériels à vérifier en fonction de la dernière date de vérification et de la fréquence annuelle de vérification (à fixer entre une à 12 fois par an).
Cette liste peut être filtrée pour chaque responsable de chaque secteur (à renseigner dans l'onglet "Responsables")

En colonne "A" une date de vérification est proposée sur le jour ouvrable le plus proche de la date anniversaire. Un clic sur une flèche verte enregistre la vérification comme étant faite à la date proposée (le compte-rendu sera "Enregistrement Automatisé" sans plus de détail, donc à compléter par la suite si vous le souhaitez).
Cette date proposée peut être modifiée (ou effacée) si vous le souhaitez.

En colonne "L" un clic sur une petite fiche jaune pâle ouvre la fiche "matériel" correspondante.

Une fois la "Liste du mois" établie, le planning (onglet du même nom) se complète pour proposer une vue "calendrier" du mois en cours. Sur cet onglet, un clic sur un histogramme matériel ouvre la fiche correspondante.

Avec cette appli, il vous sera donc facile de planifier les vérifications-contrôles périodiques à faire.

Si cette appli correspond à votre besoin, laissez-moi un commentaire!

Pierre

Téléchargement de : Suivi_Materiel_v1.6b.zip

Téléchargement de : Suivi_Materiel_v1.7.zip

 

Edit du 12 mai 2017 : Ajout date de mise en service + correction bugs en réponse à Vince

Posté par tatiak à 11:44 - Applications - Commentaires [7] - Permalien [#]