Transcript Bureautique Excel-VBA et Access Dominante Économie et Gestion d’Entreprise

Bureautique
Excel-VBA et Access
Dominante
Économie et Gestion d’Entreprise
AgroParisTech
Année 2008-2009
Liliana IBANESCU et Laurent ORSEAU
UFR Informatique
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
05/01/2009
Access
2
Objectifs


05/01/2009
Prise en main d'Excel
Utilisation avancée d'Excel par
Visual Basic Applications
Access
3
Plan du cours – partie Excel
1.
Rappels sur les bases d'Excel
1.
2.
Rappels sur VBA
1.
2.
3.
Enregistreur de macros
Programmation de macros
VBA+
1.
05/01/2009
Fonctions, listes, filtres, …
Exercices avancés
Access
4
Excel

Couteau Suisse de la bureautique

Importation de données


Traitement des données


Fonctions
Présentation des données

05/01/2009
Formats externes
Graphiques, listes, tableaux, …
Access
5
Excel

05/01/2009
Une bonne connaissance d'Excel
 gain de productivité parfois
énorme !
Access
6
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

05/01/2009
"excel 2000 filtre élaboré"
Access
7
Excel : Cours 1
1.
Bases Excel
1.
2.
Filtre élaboré
1.
3.
TP Excel-long
Tableaux croisés dynamiques
1.
05/01/2009
Poly MC
Poly MC
Access
8
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




05/01/2009
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
9
Exemple

Exemple


Exercice :

05/01/2009
Numéro de commande 10332 ET "Pâtes
et céréales" OU Prix>20 ET Prix < 100
Boissons ET Prix < 200 OU Manchester
OU Boston
Access
10
Filtre élaboré avec calcul

Sous une étiquette différente,
mettre un critère calculé :


05/01/2009
Références à la première ligne de la
liste de donnée
Références relatives automatiques
ensuite
Access
11
Exemple

Exemple :



Exercice :




Numéro de commande impair
ET Prix > 100
ET Manchester
Exercice 2:

05/01/2009
Catégorie : tous "Produits …"
ET Prix < Quantité
Nom produit : taille < 12 caractères
Access
12
Autres fonctions utiles

Aujourd'hui()



Somme, Moyenne, …
RechercheV

05/01/2009
date du jour
Ex : recherche le nom d'un personne à
partir de son N° de sécu dans une liste
Access
13
VBA : Enregistrement de Macros



Outils\Macros\...
Affichage\Barre d'Outils\Visual Basic
Exemple :


Le curseur est sur "la cellule courante"
Enregistrement :
1.
2.
3.
4.

Se placer sur une autre cellule
Cliquer sur enregistrer
Ecrire "Bonjour !"
Arrêter l'enregistrement
Exécution :
5.
6.
Se placer sur une autre cellule
Exécuter la macro


Relatif ou absolu ?



Recommencer l'enregistrement
2b) Cliquer sur "Référence relative"
Exécuter à nouveau :


05/01/2009
flèche verte, ou Outils\Macros\Macros… ou Alt-F8
Se placer sur une autre cellule
Exécuter la macro
Access
14
Enregistrement de Macros

05/01/2009
cf. Exercices-macros.xls
Access
15
Objets courants :

ActiveCell


Selection



Contenu de la cellule
"Value" similaire
Objet inconnu ?


05/01/2009
Une plage de cellules
FormulaR1C1


La plage de cellules courantes
Range


La cellule courante
Placez-vous dessus et cliquez sur F1 !
Essayez sur FormulaR1C1
Access
16
Visual Basic : Collections

Application




Workbooks



Les classeurs ouverts
Workbooks("Classeur1.xls")
 contient les feuilles
Worksheets


05/01/2009
Excel
ou Word si on est dans Word
peut toujours être omis
les feuilles du classeur
Worksheets("Feuille1")
Access
17
Exercices

Faire une macro qui



Faire une macro qui




05/01/2009
écrit n fois bonjour de A1 à An,
où n est le nombre indiqué dans la case B1
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
18
Visual Basic


Sans utiliser l'enregistreur de Macros
Exemple


Exercice

05/01/2009
Créer une nouvelle macro qui écrit "L'heure du
crime" en V13.
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
19
Evénements

Dans VBA



05/01/2009
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
20
Exercices
1.
2.
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 !"

05/01/2009
Utiliser MsgBox
Access
21
Contrôles Graphiques

Affichage/Barre d'Outils/Boite à outils
contrôles


2 modes




Création
Exécution
Possèdent des évènements
Boutons


05/01/2009
Ou clic sur Marteau-clef dans la barre d'outils
VBA
Ajouter un bouton
Double-cliquer dessus
 Edition de l'action à effectuer lors
Access
22
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

05/01/2009
Sinon laisser tel quel
Access
23
VBA : Programmation





Variables
Fonctions/procédures
Conditions If Then Else End If
Boucles For, While, …
Tableaux…
 voir poly
05/01/2009
Access
24
Intégration Web


ActiveWorkbook.FollowHyperlink(Url)
 Envoie une requête GET à travers le
navigateur
Exemple d’URL:

05/01/2009
« http://www.google.com »
Access
25
Méthode GET, requête HTTP

Exemple d’URL:

http://www.google.com/search?q=hot+dog
protocole



page
paramètres
X=a&Y=b&…
Protocoles : http, https, ftp, …
Paramètres : dépendent de la page web
Exemple :

05/01/2009
serveur
Bouton de recherche Google
Access
26
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
05/01/2009
Access
27
Protocole mailto
mailto:[email protected]?subject=Ohé&body=ca va ?
protocole
adresse email
paramètres

Paramètres : subject, body, cc, bcc

Encoder aussi le texte avec TextToUrl :



05/01/2009
%20 : espace (pas toujours nécessaire)
%0d%0a : retour chariot
…
Access
28
Exercice Noté

Une liste


NOM, PRENOM, Code Postal, Ville, Rue, N°
Ex1 :

Un bouton




Ex2 :


Recherche l’adresse de la personne dans Google
Maps
A m'envoyer en l'état A LA FIN DE LA SEANCE

05/01/2009
Ecrit un mail à la personne de la ligne
sélectionnée
Sujet : « Ca fait un bail ! »
Corps : « On s’fait une bouffe ? »
Avec le nom du binome !
Access
29
… la ligne sélectionnée

Sheet1.Cells(5, 3)


ActiveCell.Row (ou .Column)

05/01/2009
Active la cellule L5C3 de la feuille
« Sheet1 »
numéro de ligne de la cellule active
Access
30
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’à CensierDaubenton


Indice :



05/01/2009
www.ratp.fr
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
31