Tatiak

Excel, Vba, Vbs


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 [2] - 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 [2] - 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 [1] - 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 [7] - Permalien [#]

07 février 2017

Suivi des congés

Pour répondre à plusieurs demandes et remarques, voici une ré-écriture complète du code du fichier de suivi des congés avec plusieurs nouveautés :

  • gestion de plusieurs années ou 'période' (une 'période' = 12 mois pouvant toujours commencer à une date quelconque => mois de départ à indiquer en E2 feuille 'Config') jusqu'en 2067
  • cette gestion de plusieurs années avec le même fichier permet de reporter des congés non pris d'une année sur l'année suivante (cf possibilité d'incrémenter les droits à congés pour un salarié en particulier)
  • possibilité d'ajouter des 'commentaires' sur la fiche d'un salarié, sur la fiche bilan 'congés' d'un salarié, et sur les fiches de congés
  • possibilité d'indiquer des 1/2 journées de congés ou d'absences (genre je prends mon mercredi aprèm pour aller au spectacle de fin d'année de ma p'tite dernière)
  • possibilité d'ouvrir la fiche de saisie de congés par double-clic sur une ligne salarié du graphique : la date du congé indiquée par défaut (et modifiable) est alors la date du jour de la colonne
  • l'affichage est maintenant mensuel (sur 5 semaines à partir du 1er du mois choisi dans la liste)
  • l'enregistrement des informations se fait dans le fichier 'Congés_db.accdb' à part. Ceci permet :
    • une lecture/modification directement dans Access
    • de re-initialiser les données en effaçant (ou en déplaçant) le fichier 'Congés_db.accdb'
    • une sauvegarde facile des données
    • une mise à jour facile de l'appli par simple remplacement du fichier Congés_db_v*.*.xlsm par la nouvelle version, sans aucune re-saisie d'info (sauf onglet Config)


La pré-version jointe est toute 'neuve', elle contient peut être encore des bugs ou des cas non gérés, malgré mes nombreux tests. Merci de me les signaler pour correction.

Et laissez-moi vos commentaires!
Pierre

Téléchargement de : Congés_db_v1.2.zip

 

Edit du 12/06/2017 : Version v1.2 -  Ajout colonne 'Notes' (fiche salarié) en réponse à la demande de Zeweb

Edit du 23/05/2017 : Version v1.1 - Correction d'un bug en réponse à la remarque d'Elise

Edit du 11/04/2017 : Version v1.0 - Prise en compte des absences longues (>1 mois) en réponse à la remarque de Spyprog

Edit du 12/02/2017 : Version v0.13 - corrections de bugs + améliorations diverses, Merci à Shakki et à JCGL!

Edit du 11/02/2017 : Version v0.10 - correction d'un bug

Posté par tatiak à 17:22 - Applications - Commentaires [14] - Permalien [#]

29 janvier 2017

Carte de FRANCE 'ULTIMATE'!

Pour répondre à mes nombreux lecteurs cherchant sur ce blog comment réaliser une carte de leur région ou leur département, voici l'outil 'ultime' pour générer des cartes de France à diverses échelles(régions/départements/cantons/communes)

L'idée est de proposer un outil capable de lire des données géographiques d'échelles diverses et de dessiner les contours correspondants en fonction des choix de l'utilisateur.

Ainsi, il est possible d'obtenir par simple clic :

  • une carte de France par régions / ou par départements
  • une carte d'une région par départements / ou par cantons / ou par communes
  • une carte d'un département par cantons / ou par communes


Le fichier utilise les données de https://data.opendatasoft.com, de ce fait à la première ouverture du fichier, il vous est proposé de télécharger 3 fichiers .csv

  • RQ 1 : si à l'ouverture du fichier, une fenêtre "Connexion à data.opendatasoft.com" s'ouvre, cliquez simplement sur 'Annuler', le téléchargement sera proposé quand même
  • RQ 2 : sans téléchargement, pas de traitement possible
  • RQ 3 : ces fichiers sont à priori sans risques (mais vous pouvez les scanner avec votre anti-virus si vous préférez)
  • RQ 4 : ces fichiers sont à coller dans le dossier 'CSV' créé à la première ouverture de l'appli


Une fois les téléchargements effectués et fichiers positionnés dans 'CSV', vous accédez aux différentes cartes :

  • soit en cliquant directement sur une région / un département
  • soit en choisissant une option du menu déroulant
  • soit en cliquant sur le bouton '<<retour' pour revenir à la carte affichée précédemment


A tout moment vous pouvez copier/coller l'onglet en cours dans un nouveau classeur, pour disposer d'une carte à utiliser pour votre besoin.

A noter :

  • Les 'anciennes' régions contenues dans les .csv sont automatiquement traduites par les nouveaux intitulés.
  • La vitesse de traitement dépend du nombre de formes à dessiner, le dessin d'une région divisée en communes peut être très long!


Cette appli est en version test (pour l'instant), merci de me donner votre avis et de m'indiquer si des bugs apparaissent.

Pierre

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

 

Edit du 11/06/2017 : Version v0.7  v0.8 en réponse aux retours de Supertln et de Xonic : correction d'un défaut ... et correction de la correction du défaut!

Edit du 05/06/2017 : Version v0.6 en réponse au retour de Supertln. Fonctionne quelque soit le séparateur décimal défini dans la configuration Windows ET dans la configuration Excel (ils peuvent être différents ou identiques, virgule ou point)

Edit du 31/03/2017 : Version v0.3 v0.4 v0.5 en réponse au signalement de xonic. Les données de data.opendatasoft.com ont évoluées. Le lien vers le fichier .cvs des communes est donc modifié pour rendre l'appli à nouveau fonctionnelle (testée sous Office 2007 et Office 2016)

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

25 décembre 2016

Carte du Monde

Pour répondre à la demande d'Analexam, voici ma version de la carte du monde avec dessin des pays selon leurs contours GPS, principe déjà décrit précédemment sur ce blog.

Cette méthode permet de colorer des pays ou des régions du monde, mais elle permet aussi de positionner sur la carte des points d'intérêt et/ou même de dessiner des traces GPS (par exemple pour suivre l'avancée d'Armel Le Cléac'h dans le Vendée Globe)
Nb : les coordonnées GPS sont faciles à obtenir via GoogleMap

Pour les contours des pays de cette démo, j'ai utilisé le jeu de données au 1:110m du site  https://data.opendatasoft.com/explore/?sort=modified&refine.features=geo&q=earth (si besoin il est possible d'utiliser un autre jeu de données par simple copier/coller)

Pour modifier l'échelle de la carte, il suffit de modifier la valeur de la constante 'Echelle' (au début du module 'Graphe')

Pour la demande initiale d'Analexam, il est possible d'ajouter autant de listes utiles à la suite des listes personnalisées de l'onglet 'Base' (saisir un titre, puis cocher d'un X majuscule les pays à inclure dans la liste)

Bonnes fêtes à tous
Pierre

Téléchargement de : Monde.xlsm

Posté par tatiak à 10:57 - Excel-Cartographie - Commentaires [0] - Permalien [#]

21 novembre 2016

Gestion de clefs

Pour répondre à un besoin de Chatillon, voici une application Excel de gestion de clefs.

Cette appli est basée sur le principe de hiérarchisation Passe Général / Passes Partiels

A noter :

  • pour les colonnes PG et PP (passe général/passes partiels), il suffit de cliquer dans les bonnes cases pour mettre à jour la "combinaison"
  • un clic sur une entête de colonne => tri des info selon cette colonne
  • si besoin, les entêtes de colonnes sont modifiables directement sur la feuille "Data" (notamment les intitulés T à Z)


La saisie se fait soit directement sur la feuille "Data", soit par l'interface (bouton "Nouvelle clef") =>utilisez la touche Tab ou Entrée pour aller à la zone de saisie suivante, les croix PG/PP se positionnent par clic de souris.

Pierre

Téléchargement de Clefs_v1.2.xlsm

Posté par tatiak à 16:09 - Applications - Commentaires [4] - Permalien [#]