Bureautique Excel-VBA et Access

1. BureautiqueExcel-VBA et Access Dominante Économie et Gestion d’Entreprise AgroParisTech Année 2008-2009 Liliana IBANESCU et Laurent ORSEAU UFR Informatique

2. Le cours • 3 TDs x 2h = 6h Access (LI) • 3 TDs x 2h = 6h Excel-VBA (LO) • Présence obligatoire • Évaluation: exercices à envoyer à la fin de chaque partie Access

3. Objectifs • Prise en main d'Excel • Utilisation avancée d'Excel par Visual Basic Applications Access

4. Plan du cours – partie Excel • Rappels sur les bases d'Excel • Fonctions, listes, filtres, … • Rappels sur VBA • Enregistreur de macros • Programmation de macros • VBA+ • Exercices avancés Access

5. Excel • Couteau Suisse de la bureautique • Importation de données • Formats externes • Traitement des données • Fonctions • Présentation des données • Graphiques, listes, tableaux, … Access

6. Excel • Une bonne connaissance d'Excel  gain de productivité parfois énorme ! Access

7. Liens Excel • Site du cours www.agroparistech.fr/Spe-EGE-Bureautique.html • Donnés sur le site du cours : • Bases Excel www.agroparistech.fr/mmip/mc/agro/excel.html • Compléments Excel www.agroparistech.fr/mmip/mc/agro/excelsyntheses.html • VBA www.agroparistech.fr/UV-Le-Tableur-Excel-la.html • Google ! • Exemple • "excel 2000 filtre élaboré" Access

8. Excel : Cours 1 • Bases Excel • Poly MC • Filtre élaboré • TP Excel-long • Tableaux croisés dynamiques • Poly MC Access

9. Filtre Elaboré • Télécharger le fichier TP Excel-long sur le site du cours • Copier les étiquettes de colonne en dessous de la liste de données • Remplir des critères • ET sur une même ligne • Duplication d'étiquette possible • OU entre les lignes • * remplace toute suite de caractères • ? remplace une seul caractère Access

10. Exemple • Exemple • Numéro de commande 10332 ET "Pâtes et céréales" OU Prix>20 ET Prix < 100 • Exercice : • Boissons ET Prix < 200 OU Manchester OU Boston Access

11. Filtre élaboré avec calcul • Sous une étiquette différente, mettre un critère calculé : • Références à la première ligne de la liste de donnée • Références relatives automatiques ensuite Access

12. Exemple • Exemple : • Catégorie : tous "Produits …" • ET Prix < Quantité • Exercice : • Numéro de commande impair • ET Prix > 100 • ET Manchester • Exercice 2: • Nom produit : taille < 12 caractères Access

13. Autres fonctions utiles • Aujourd'hui() • date du jour • Somme, Moyenne, … • RechercheV • Ex : recherche le nom d'un personne à partir de son N° de sécu dans une liste Access

14. VBA : Enregistrement de Macros • Outils\Macros\... • Affichage\Barre d'Outils\Visual Basic • Exemple : • Le curseur est sur "la cellule courante" • Enregistrement : • Se placer sur une autre cellule • Cliquer sur enregistrer • Ecrire "Bonjour !" • Arrêter l'enregistrement • Exécution : • Se placer sur une autre cellule • Exécuter la macro • flèche verte, ou Outils\Macros\Macros… ou Alt-F8 • Relatif ou absolu ? • Recommencer l'enregistrement • 2b) Cliquer sur "Référence relative" • Exécuter à nouveau : • Se placer sur une autre cellule • Exécuter la macro Access

15. Enregistrement de Macros • cf. Exercices-macros.xls Access

16. Objets courants : • ActiveCell • La cellule courante • Selection • La plage de cellules courantes • Range • Une plage de cellules • FormulaR1C1 • Contenu de la cellule • "Value" similaire • Objet inconnu ? • Placez-vous dessus et cliquez sur F1 ! • Essayez sur FormulaR1C1 Access

17. Visual Basic : Collections • Application • Excel • ou Word si on est dans Word • peut toujours être omis • Workbooks • Les classeurs ouverts • Workbooks("Classeur1.xls") • contient les feuilles • Worksheets • les feuilles du classeur • Worksheets("Feuille1") Access

18. Exercices • Faire une macro qui • écrit n fois bonjour de A1 à An, • où n est le nombre indiqué dans la case B1 • Faire une macro qui • crée une série en colonne de n éléments • à partir de deux cellules courantes disposées l'une au dessous de l'autre • curseur positionné au départ sur la première • où n est donné dans la cellule de droite • puis qui écrit la date du jour à la place de n Access

19. Visual Basic • Sans utiliser l'enregistreur de Macros • Exemple • Créer une nouvelle macro qui écrit "L'heure du crime" en V13. • Exercice • En créer une autre qui calcule la somme des deux cellules du dessus et qui l'affecte à la cellule du dessous • Recalculé seulement au lancement de la macro • Ce n'est pas une "formule" ! Access

20. Evénements • Dans VBA • Ouvir le document approprié • This Workbook • Feuille1, … • Module, • … • Dans les deux onglets au dessus du document, choisir l'évènement • Ajout automatique d'une Sub • La compléter • Tester dans Excel Access

21. Exercices • Lorsque la sélection change, mettre la couleur de fond de la sélection en vert. • Lorsque la "feuille2" est activée, afficher le message "Bonjour toi !" • Utiliser MsgBox Access

22. Contrôles Graphiques • Affichage/Barre d'Outils/Boite à outils contrôles • Ou clic sur Marteau-clef dans la barre d'outils VBA • 2 modes • Création • Exécution • Possèdent des évènements • Boutons • Ajouter un bouton • Double-cliquer dessus  Edition de l'action à effectuer lors Access

23. Controles : exercices • Faire un bouton qui change le format de la sélection en • Si la valeur de la cellule est un nombre : • Gras • Souligné • Fond jaune  Utiliser : If … Then … EndIf • Sinon laisser tel quel Access

24. VBA : Programmation • Variables • Fonctions/procédures • Conditions If Then Else End If • Boucles For, While, … • Tableaux…  voir poly Access

25. Intégration Web • ActiveWorkbook.FollowHyperlink(Url) • Envoie une requête GET à travers le navigateur • Exemple d’URL: • « http://www.google.com » Access

26. Méthode GET, requête HTTP • Exemple d’URL: • http://www.google.com/search?q=hot+dog • Protocoles : http, https, ftp, … • Paramètres : dépendent de la page web • Exemple : • Bouton de recherche Google page paramètres X=a&Y=b&… protocole serveur Access

27. TextToURL (simple) Function Url_Encode(ByVal ValIn As String) As String Dim ValOut As String Dim I As Long Dim AscVal As Integer Dim MidVal As String * 1 ValOut = "" For I = 1 To Len(ValIn) MidVal = Mid$(ValIn, I, 1) AscVal = Asc(MidVal) Select Case AscVal Case 32 ValOut = ValOut & "%20" Case 42, 45, 46, 48 To 57, 64 To 90, 97 To 122 ValOut = ValOut & MidVal Case Else ValOut = ValOut & "%" & Hex$(AscVal) End Select Next I Url_Encode = ValOut End Function Access

28. Protocole mailto mailto:edgar.poe@gmail.com?subject=Ohé&body=ca va ? • Paramètres : subject, body, cc, bcc • Encoder aussi le texte avec TextToUrl : • %20 : espace (pas toujours nécessaire) • %0d%0a : retour chariot • … protocole adresse email paramètres Access

29. Exercice Noté • Une liste • NOM, PRENOM, Code Postal, Ville, Rue, N° • Ex1 : • Un bouton • Ecrit un mail à la personne de la ligne sélectionnée • Sujet : « Ca fait un bail ! » • Corps : « On s’fait une bouffe ? » • Ex2 : • Recherche l’adresse de la personne dans Google Maps • A m'envoyer en l'étatA LA FIN DE LA SEANCE • Avec le nom du binome ! Access

30. … la ligne sélectionnée • Sheet1.Cells(5, 3) • Active la cellule L5C3 de la feuille « Sheet1 » • ActiveCell.Row (ou .Column) • numéro de ligne de la cellule active Access

31. Exercice RATP: Bonus • Pour l’adresse de la ligne sélectionnée, envoyer une requête vers la RATP pour connaître le trajet jusqu’à Censier-Daubenton • www.ratp.fr • Indice : • pour connaître le format de l’URL et des paramètres, faire un premier essai à la main sur le site • puis copier l’adresse créée • certains paramètres affichés ne sont pas nécessaires ! Access