Tatiak

Excel, Vba, Vbs


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 !

 

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

Posté par tatiak à 18:33 - Applications - Commentaires [2] - 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 [0] - 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 [0] - 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.

(à noter : sur ces courbes les heures sont en temps universel UTC, il convient donc de lire l'échelle avec +1h/+2h selon la période hivers/été)

Capture3


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 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.6.xlsm

Posté par tatiak à 09:38 - Applications - Commentaires [7] - 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 [4] - Permalien [#]

04 avril 2017

"PILOTER" WORD DEPUIS EXCEL - 4ème PARTIE (4/4)

Excel n'est pas adapté pour faire de la mise en page comme avec Word. Pour générer depuis Excel des rapports, des factures, des contrats ou autres documents, j'utilisais jusqu'à présent la méthode de remplissage d'un modèle déjà mis en page (décrite précédemment dans d'autres articles).

Seulement voilà quand on oublie de transmettre le modèle Word avec le fichier Excel, l'export des données vers Word est totalement inopérant!

La solution est de créer de zéro votre document Word depuis Excel. Votre xlsm sera alors complètement autonome.

Mieux qu'un long descriptif, je vous renvoie directement au code commenté de la démo jointe. Dans ce code, diverses opérations sont menées, dans l'ordre :

  • création d'un nouveau document
  • ajustement des marges
  • ajout d'un logo
  • ajout d'un titre
  • ajout d'un signet (en cas de besoin ultérieur)
  • ajout d'un ou de plusieurs paragraphes (selon choix fait sur la feuille Excel)
  • ajout d'un tableau
  • ajustement des colonnes du tableau
  • remplissage de l'entête du tableau
  • remplissage (et ajout) des lignes
  • calcul d'un total d'une des colonne du tableau
  • inscription du total calculé à la dernière ligne du tableau
  • ajout de la date et de l'heure de la création du document
  • enregistrement du document dans "Mes Documents" sous-dossier "Word" (qui se crée si besoin)


A chacune de ces opérations, on règle la police, le soulignement, l'alignement, l'espacement, ... bref on fait la mise en page qui rendra votre doc parfaitement lisible.
Ceci n'est pas exhaustif des possibilités offertes par le VBA, pour d'autres fonctions dites-moi votre besoin et je vous indiquerai le code à ajouter.

Cette démo est fonctionnelle avec Office 2016 (pas testée avec d'autres versions)

Bon téléchargement et laissez-moi vos commentaires!
Pierre

Téléchargement de : Créer_Doc_Word.xlsm

Posté par tatiak à 13:56 - Excel-Word - Commentaires [1] - Permalien [#]

27 mars 2017

Graphe "social mapping"

 

Capture4

Pour répondre au besoin d'Okalius, voici un outil de représentation de "social mapping" adapté à la plongée sous-marine.

L'idée est de visualiser les différentes expériences de plongée avec diverses personnes pour objectiver la fréquence des rencontres et le partage d'expérience.

Le fichier est constitué de 2 onglets : Data pour les données et Map pour le graphique.

L' onglet "Data" :
* Un tableau "plongeurs" pour décrire le profil de chacun
* 2 tableaux de configuration de profil
* Un tableau de plongées indiquant avec qui le plongeur "Id=0" a effetué ses plongées

L'onglet "Map" :
* à la sélection de cet onglet "Map", les éventuels nouveaux User sont dessinés (petits carré bleus avec le nom au dessus) à partir du coin sup gauche (vers la cellule B2) puis à suivre de gauche à droite si plusieurs nouveaux User

* on peut cliquer sur ces carrés pour choisir une image-avatar (gif, jpg ou png) à insérer (pour ne pas trop alourdir le fichier, préférer des .gif de quelques ko)

* on peut déplacer ces carrés (en fonction des relations) => clic droit puis gauche

* une fois que les carrés sont positionnés, un clic sur le bouton "Dessin Relations" pour tirer les traits qui vont bien en fonction des données. Pour chaque relation le nombre total de plongée entre 2 individus s'affiche au milieu du trait entre ces 2 personnes.

Ce graphique vous permet ainsi de savoir ce que vous avez eu comme expérience et quels ont été vos partenaires les plus fréquents.

Ce principe peut être adapté pour d'autres types d'activités pour lesquelles un outil similaire serait utile

Pierre

 

Téléchargement de : SocialMapping.xlsm

Posté par tatiak à 11:31 - Excel-Divers - Commentaires [1] - Permalien [#]

10 mars 2017

Cartes de pays

Pour répondre à des demandes de lecteurs, je développe en ce moment un outil permettant de dessiner n'importe quel pays du monde, utilisant les données OpenStreetMap.

Pour l'instant, ma méthode n'est pas pleinement 'automatique', elle n'est donc pas publiable en l'état.

En revanche, cette nouvelle méthode me permet aujourd'hui de répondre à un besoin de Sara07 avec cette carte du Liban coloriable selon 4 colonnes d'indicateurs et selon l'échelle de couleurs (à droite de la carte). Cette échelle peut être modifiée (valeurs mini, maxi et gamme de couleurs) à vos besoins.

A la suite de cette première carte, j'ajouterai celles répondant aux autres demandes.

Pierre

 

Téléchargement de : Liban.xlsm

Téléchargement de : Maroc.xlsm

Téléchargement de : Tunisie.xlsm

Téléchargement de : Romania.xlsm

Posté par tatiak à 14:22 - Excel-Cartographie - Commentaires [5] - Permalien [#]

09 mars 2017

Distancier

Pour répondre à un besoin régulièrement exprimé, voici un "distancier" qui permet d'avoir des informations sur les trajets entre 2 villes.
La démo proposée ici utilise plusieurs méthodes :

  • les api de GoogleMap
  • le décodage json
  • le calcul de distance entre 2 points GPS


Voyons en détails :
2 api de GoogleMap sont utilisées :

  •  'api/distancematrix' pour obtenir un trajet GoogleMap en mode 'driving' (voiture)
  •  'api/geocode/' pour obtenir le positionnement GPS indiqué par GoogleMap


Dans cette démo, l'option '/json' est utilisée, ce qui va nous permettre de lire facilement l'info retournée par le serveur.

Pour décoder le json obtenu, une fonction 'oRecordSet' permet de placer cette info dans un 'objet' facilement lisible élément par élément pour affichage.

On aurait pu s'arrêter là, mais il est des cas où GoogleMap ne propose pas de trajet 'driving' (pour des distances longues notamment, par exemple un Paris-Pékin en voiture n'est pas pris en charge par GoogleMap )
Dans ces cas, il m'a semblé intéressant de calculer la distance "à vol d'oiseaux" entre ces 2 villes. Pour cela la 2ème api de GoogleMap nous retourne les coordonnées GPS via '/json' et un calcul simple nous donne la distance en ligne droite.

L'utilisation du fichier proposé est simple, il suffit de saisir les adresses de départ et d'arrivée en colonnes A et B (sur 2000 lignes possibles) pour obtenir :

  • la distance 'en ligne droite'
  • la distance du trajet GoogleMap proposé
  • la durée du trajet GoogleMap proposé
  • et le complément d'adresse lu : pour 'Brest' Google propose 'Brest, France'


Avec cette démo, il sera donc facile, par exemple à un professionnel itinérant (genre trajets à la journée de 200 à 400 km autour de sa base), de calculer ses frais de déplacement courants.

Bon téléchargement, et laissez-moi vos commentaires!
Pierre


Téléchargement de : Distancier_json.xlsm

 

Edit du 03/03/2017 : Pour répondre à Dominique, voici une autre version pour des séries de distances : on colle une liste des départs-arrivées en colonnes A et B et on clique sur le bouton pour avoir les distances et durées des trajets Google.

Téléchargement de : Distances_Json_série.xlsm

Posté par tatiak à 09:48 - Excel-Cartographie - Commentaires [9] - Permalien [#]