Médiaforma

All posts in VBAExcel

Vous voulez savoir combien de fichiers se trouvent dans un dossier de vos unités de masse ? Vous êtes au bon endroit.

Pour parcourir un dossier, nous affecteront la fonction Dir() à une variable String, en précisant le chemin du dossier à examiner en paramètre de la fonction. Dir() retourne le nom du premier fichier du dossier parcouru. Tant que la valeur retournée n’est pas vide, cela signifie que tous les fichiers n’ont pas été passés en revue. Dans ce cas, vous incrémenterez une variable compteur et vous passerez au fichier suivant en affectant la fonction Dir sans paramètre à la variable String. Lorsque la variable String sera vide, tous les fichiers auront été parcourus et la variable compteur contiendra le nombre de fichiers du dossier.

Voici le code utilisé :

Dim Fichier As String, NbFic As Integer

NbFic = 0

Fichier = Dir("c:\data\encours\")

Do While Fichier <> ""

  NbFic = NbFic + 1

  Fichier = Dir

Loop

MsgBox NbFic

Et voici un exemple d’exécution :


Cet article va vous montrer comment lister les fichiers contenus dans un dossier quelconque dans une feuille de calcul. Ici, les fichiers seront listés dans les cellules de la colonne A de la feuille de calcul Feuil1.

Pour cela, nous utiliserons la fonction Dir() pour parcourir le dossier :

Dim Fichier As String

Fichier = Dir("chemin")

chemin représente le chemin du dossier à examiner. Par exemple c:\dossier\sous-dossier\.

Si nécessaire, vous pouvez préciser le modèle des fichiers recherchés à la suite du chemin. Par exemple, c:\dossier\sous-dossier\*.docx recherchera les fichiers d’extension docx dans le dossier c:\dossier\sous-dossier.

Voici le code utilisé :

Dim Dossier As String, Fichier As String, i As Integer

Dossier = "C:\data\encours\"

i = 0

Fichier = Dir(Dossier)

Do While Fichier <> ""

  i = i + 1

  Sheets("Feuil1").Range("A" & i) = Fichier

  Fichier = Dir

Loop

La première ligne définit les variables utilisées dans le programme.

Dim Dossier As String, Fichier As String, i As Integer

La deuxième ligne affecte le dossier dont on désire lister les fichiers à la variable Dossier :

Dossier = "C:\data\encours\"

La troisième ligne initialise la variable compteur qui sera utilisée pour copier le nom des fichiers dans la feuille de calcul :

i = 0

La quatrième ligne utilise la fonction Dir() pour rechercher les fichiers dans le dossier spécifié en argument :

Fichier = Dir(Dossier)

Une boucle Do While parcourt les fichiers listés dans la variable Fichier jusqu’au dernier :

Do While Fichier <> ""

La variable compteur est incrémentée, puis le nom du fichier courant est copié dans la cellule de colonne A et de ligne i :

  i = i + 1

  Sheets("Feuil1").Range("A" & i) = Fichier

Pour passer au fichier suivant, il suffit d’affecter la fonction Dir à la variable Fichier :

  Fichier = Dir

Loop

Voici un exemple d’exécution :


VBA est en mesure d’effectuer des opérations élémentaires sur des fichiers. Cet article va vous montrer comment copier, renommer et supprimer un fichier.

Copier un fichier

Pour copier un fichier, vous utiliserez l’instruction FileCopy :

FileCopy "source", "destination"

source est le chemin complet du fichier à copier et destination est le chemin complet de la copie.

Par exemple, pour copier le fichier Paye.xlsm qui se trouve dans le dossier c:\data\encours\ dans le fichier Paye-Janvier.xlsm du même dossier, vous utiliserez l’instruction suivante :

FileCopy "c:\data\encours\Paye.xlsm", "c:\data\encours\Paye-Janvier.xlsm"

Renommer un fichier

Pour renommer un fichier, vous utiliserez l’instruction Name As :

Name "ancien" as "nouveau"

ancien est le chemin complet du fichier à renommer et nouveau est le chemin complet du fichier renommé.

Par exemple, pour renommer le fichier Paye-Janvier.xlsm qui se trouve dans le dossier c:\data\encours\ en Paye-Fevrier.xlsm, vous utiliserez l’instruction suivante :

Name "c:\data\encours\Paye-Janvier.xlsm" As "c:\data\encours\Paye-Fevrier.xlsm"

Supprimer un fichier

Pour supprimer un fichier, vous utiliserez l’instruction Kill :

Kill "fichier"

fichier est le chemin complet du fichier à supprimer.

Par exemple, pour supprimer le fichier Paye-Fevrier.xlsm qui se trouve dans le dossier c:\data\encours\, vous utiliserez l’instruction suivante :

Kill "c:\data\encours\Paye-Fevrier.xlsm"

VBA est en mesure d’exécuter une procédure lorsque l’utilisateur appuie sur une touche ou une combinaison de touches. Pour cela, vous utiliserez la procédure Application.OnKey :

Application.OnKey "touche", "proc"

touche est la touche ou la combinaison de touches qui déclenche l’exécution de la procédure proc.

Le tableau ci-après donne un aperçu de la syntaxe à utiliser.

Touche Code de la touche
Majuscule +
Contrôle ^
Alt %
Suppr {DELETE}
Retour Arrière {BACKSPACE}
Verr Num {NUMLOCK}
Verr Maj {CAPSLOCK}
Arrêt Defil {SCROLLLOCK}
Page Suivante {PGDN}
Page précédente {PGUP}
Haut {UP}
Bas {DOWN}
Gauche {LEFT}
Droite {RIGHT}
Origine {HOME}
Fin {END}
F1 à F12 {F1} à {F12}
Entrée {ENTER}
Echap {ESC}
Insertion {INSERT}
Impr écran {PRTSC}
Tabulation {TAB}

 

Vous appellerez la méthode Application.OnKey dans la procédure Workbook_Open().

Ouvrez la fenêtre Microsoft Visual Basic pour Applications du classeur concerné. Double-cliquez sur ThisWorkbook dans la fenêtre Projet (1) et sélectionnez Workbook dans la liste déroulante Objet (2). La procédure Workbook_Open() est automatiquement créée. Il ne vous reste plus qu’à la compléter (3). Ici par exemple, la procédure raccourci() est exécutée lorsque l’utilisateur appuie sur Contrôle + Alt + j :

Voici le code utilisé :

Private Sub Workbook_Open()

  Application.OnKey "^%j", "raccourci"

End Sub

Il ne reste plus qu’à définir la procédure raccourci(). Sous Module, double-cliquez sur Module1 et définissez la procédure raccourci() :

Sub raccourci()

  MsgBox "Vous avez appuyé sur Contrôle + Alt + J"

End Sub

Chaque fois que l’utilisateur appuie simultanément sur les touches Contrôle, Alt et j, une boîte de dialogue s’affiche :


Vous pouvez demander à VBA d’exécuter une procédure à une heure donnée en utilisant la méthode Application.OnTime :

Application.OnTime heure, "proc"

heure est un objet Time qui définit l’heure d’exécution de la procédure proc.

Exécuter une procédure à une heure fixe

Vous appellerez la méthode Application.OnTime dans la procédure Workbook_Open().

Ouvrez la fenêtre Microsoft Visual Basic pour Applications du classeur concerné. Double-cliquez sur ThisWorkbook dans la fenêtre Projet (1) et sélectionnez Workbook dans la liste déroulante Objet (2). La procédure Workbook_Open() est automatiquement créée. Il ne vous reste plus qu’à la compléter (3) :

Lorsque vous ouvrirez le classeur, le code placé dans la procédure Workbook_Open() sera automatiquement exécuté. Voici le code utilisé :

Private Sub Workbook_Open()

  Application.OnTime TimeValue("12:30:00"), "manger"

End Sub

Ici, nous utilisons la fonction TimeValue() pour obtenir un objet Time à partir d’une chaîne au format hh:mm:ss. Cet objet définit l’heure d’exécution de la procédure manger().

Il ne reste plus qu’à définir la procédure manger(). Sous Module, double-cliquez sur Module1 et définissez la procédure manger() :

Sub manger()

  MsgBox "Il faudrait peut-être penser à aller manger !"

End Sub

A 12 heures 30, une boîte de message rappellera que c’est l’heure du repas :

Exécuter une procédure un certain laps de temps après l’ouverture du classeur

En utilisant la procédure Application.OnTime, vous pouvez également exécuter une procédure un certain laps de temps après l’ouverture du classeur.

La procédure Workbook.Open() est la très proche de celle utilisée dans l’exemple précédent, si ce n’est qu’ici, on ajoute le délai (TimeValue) à l’heure actuelle (Now). Dans cet exemple, la procédure alerte() s’exécutera une minute après l’ouverture du classeur :

Private Sub Workbook_Open()

  Application.OnTime Now + TimeValue("00:01:00"), "alerte"

End Sub

Voici le code de la fonction alerte() :

Sub alerte()

  MsgBox "Le classeur est ouvert depuis une minute."

End Sub

Cet article va vous montrer comment changer la couleur de certaines cellules en fonction de leur valeur. Nous allons partir de la feuille suivante :

A titre d’exemple, nous allons parcourir les cellules de la colonne G. Lorsque la valeur d’une de ces cellules sera supérieure ou égale à 10.5, la ligne correspondante sera colorée en orange. Dans le cas contraire, la ligne correspondante sera colorée en vert.

Voici le code utilisé :

Dim i As Integer

For i = 2 To 9

  If Cells(i, 7) >= 10.5 Then

    Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(255, 128, 128)

  Else

    Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(128, 255, 128)

  End If

Next i

Une boucle parcourt les cellules de la plage G2:G9. Si la valeur contenue dans une de ces cellules est supérieure ou égale à 10.5 :

For i = 2 To 9

  If Cells(i, 7) >= 10.5 Then

La plage comprise entre les colonnes A et G de la ligne concernée est colorée en orange :

Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(255, 128, 128)

Dans le cas contraire, cette même plage est colorée en vert :

Else

  Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(128, 255, 128)

Voici le résultat :


Dans l’article “Tracé d’un graphique en VBA“, vous avez appris à insérer un graphique dans la feuille courante. Cet article va aller beaucoup plus loin. En effet, vous allez y apprendre comment choisir l’emplacement, la taille, la position et le type du graphique dans la feuille.

Voici le code utilisé :

Dim feuille As Worksheet

Dim graphique As ChartObject

Set feuille = Sheets("Feuil1")

Set graphique = feuille.ChartObjects.Add(60, 50, 500, 300)

With graphique.Chart

  .ChartType = xlLineMarkers

  .SeriesCollection.NewSeries

  With .SeriesCollection(1)
 
    .Values = feuille.Range("A2:C2")

    .XValues = feuille.Range("A1:C1")

  End With

End With

Set graphique = Nothing

Set feuille = Nothing

Les deux premières instructions définissent les variables feuille et graphique qui représenteront respectivement la feuille dans laquelle le graphique sera inséré et le graphique à insérer :

Dim feuille As Worksheet

Dim graphique As ChartObject

L’instruction suivante affecte la feuille Feuil1 à la variable feuille :

Set feuille = Sheets("Feuil1")

L’instruction suivante ajoute à la feuille Feuil1 un graphique :

  • de largeur 500 pixels ;
  • de hauteur 500 pixels ;
  • dont l’angle supérieur gauche se trouve à 60 pixels du bord gauche et à 50 pixels du bord supérieur.

Pour faciliter l’écriture du code, ce graphique est affecté à la variable graphique :

Set graphique = feuille.ChartObjects.Add(60, 50, 500, 300)

L’instruction With … End With suivante définit les caractéristiques du graphique. On commence par le type du graphique, via la propriété ChartType :

With graphique.Chart

  .ChartType = xlLineMarkers

Comme le montre le tableau suivant, de très nombreux graphiques peuvent être définis dans Excel 2016 :

ChartType Signification
xl3DArea Aires 3D
xl3DAreaStacked Aires 3D empilées
xl3DAreaStacked100 Aires empilées 100 %
xl3DBarClustered Barres groupées 3D
xl3DBarStacked Barres empilées 3D
xl3DBarStacked100 Barres empilées 100 % 3D
xl3DColumn Histogramme 3D
xl3DColumnClustered Histogramme groupé 3D
xl3DColumnStacked Histogramme empilé 3D
xl3DColumnStacked100 Histogramme empilé 100 % 3D
xl3DLine Courbe 3D
xl3DPie Secteurs en 3D
xl3DPieExploded Secteurs éclatés en 3D
xlArea Aires
xlAreaStacked Aires empilées
xlAreaStacked100 Aires empilées 100 %
xlBarClustered Barres groupées
xlBarOfPie Barres de secteurs
xlBarStacked Barres empilées
xlBarStacked100 Barres empilées 100 %
xlBubble Bulles
xlBubble3DEffect Bulles avec effet 3D
xlColumnClustered Histogramme groupé
xlColumnStacked Histogramme empilé
xlColumnStacked100 Histogramme empilé 100 %
xlConeBarClustered Barres groupées à forme conique
xlConeBarStacked Barres empilées à forme conique
xlConeBarStacked100 Barres empilées 100 % à forme conique
xlConeCol Histogramme 3D à forme conique
xlConeColClustered Histogramme groupé à formes coniques
xlConeColStacked Histogramme empilé à formes coniques
xlConeColStacked100 Histogramme empilé 100 % à formes coniques
xlCylinderBarClustered Barres groupées à formes cylindriques
xlCylinderBarStacked Barres empilées à formes cylindriques
xlCylinderBarStacked100 Barres empilées 100 % à formes cylindriques
xlCylinderCol Histogramme 3D à formes cylindriques
xlCylinderColClustered Histogramme groupé à formes coniques
xlCylinderColStacked Histogramme empilé à formes coniques
xlCylinderColStacked100 Histogramme empilé 100 % à formes cylindriques
xlDoughnut Anneau
xlDoughnutExploded Anneau éclaté
xlLine Courbe
xlLineMarkers Courbes avec marques
xlLineMarkersStacked Courbe empilée avec marques
xlLineMarkersStacked100 Courbe empilée 100 % avec marques
xlLineStacked Courbe empilée
xlLineStacked100 Courbe empilée 100 %
xlPie Secteurs
xlPieExploded Secteurs éclatés
xlPieOfPie Secteurs de secteurs
xlPyramidBarClustered Histogramme groupé à formes pyramidales
xlPyramidBarStacked Histogramme empilé à formes pyramidales
xlPyramidBarStacked100 Histogramme empilé 100 % à formes pyramidales
xlPyramidCol Histogramme 3D à formes pyramidales
xlPyramidColClustered Histogramme groupé à formes pyramidales
xlPyramidColStacked Histogramme empilé à formes pyramidales
xlPyramidColStacked100 Histogramme empilé 100 % à formes pyramidales
xlRadar Radar
xlRadarFilled Radar plein
xlRadarMarkers Radar avec marqueurs
xlStockHLC Max-Min-Clôture
xlStockOHLC Ouverture-Max-Min-Clôture
xlStockVHLC Volume-Max-Min-Clôture
xlStockVOHLC Volume-Ouverture-Max-Min-Clôture
xlSurface Surface 3D
xlSurfaceTopView Surface 3D avec structure apparente
xlSurfaceTopViewWireframe Contour
xlSurfaceWireframe Contour filaire
xlXYScatter Nuage de points
xlXYScatterLines Nuages de points avec courbes
xlXYScatterLinesNoMarkers Nuages de points avec courbes et sans marqueurs
xlXYScatterSmooth Nuages de points avec courbes lissées
xlXYScatterSmoothNoMarkers Nuages de points avec courbes lissées et sans marqueurs

 

L’instruction suivante ajoute une collection de séries :

.SeriesCollection.NewSeries

Cette collection est utilisée pour définir les abscisses (XValues) et les ordonnées (Values) correspondantes :

With .SeriesCollection(1)

  .Values = feuille.Range("A2:C2")

  .XValues = feuille.Range("A1:C1")

End With

Le code se termine par la suppression des variables objet graphique et feuille :

Set graphique = Nothing

Set feuille = Nothing

Voici un exemple d’exécution :