VBA : L’objet Fichier système

Comment manipuler les fichiers positionnés sur votre disque dur, à l’aide du langage VBA. L’utilisation de l’objet "FileSystemObject" va nous permettre de régler cette question. Dans cet exemple nous allons pouvoir :

• Supprimer un fichier
• Renommer un fichier
• Déplacer un fichier
• Ouvrir un fichier

Évidement pour pouvoir réaliser ces actions il faudra au préalable trouver sur le disque le fichier qui nous intéresse. Donc rechercher un fichier sera notre première action.

Nous saisissons dans la zone de texte "TextBox1" du formulaire "UserForm2" le nom d’un fichier Excel (sans l’extension), puis nous cliquons sur le bouton "Find". Le système parcours ici tous les sous-dossiers d’un dossier "Blog" d’un disque "E:".

Si le fichier est localisé ; son Path (chemin d’accès) va s'afficher dans la zone de label "Label1" située en dessous, l'utilisateur choisira alors l'action qu'il désire accomplir.

Il sera nécessaire de créer 2 objets, un pour l'accès au système de fichiers Windows (scripting.FileSystemObject) et un autre objet de type dossier pour récupérer notre dossier racine par la méthode GetFolder. Vous noterez également l'emploi de la fonction Dir() de la classe FileSystem qui permet la récupération du nom du fichier dans une chaîne de type Path ou une chaîne vide en cas d'absence. Le reste du code ne semble pas poser de difficultés.

'-----------------------------------------------------------------------

Option Explicit

Public fichier As String

Public fichier2 As String

Public Flder As Object

Public comp As String

 Const boite = vbOKOnly + vbInformation

 Const titre = "www.olivier - picot.fr"

 '-----------------------------------------------------------------------

Private Sub Find_Click()

Dim fso As Object

Dim Dossier As Object

Dim LeDossier As String

Dim chemin As String

comp = TextBox1.Value

LeDossier = "E:\Blog"

Set fso = CreateObject("scripting.FileSystemObject")

Set Dossier = fso.getfolder(LeDossier)

'Examen de chaque sous dossier du dossier E:\Blog

For Each Flder In Dossier.subfolders

    fichier = Dir(Flder.Path & Application.PathSeparator & comp & ".xlsx")

    If fichier <> "" Then 'donc si on l'a trouvé

        chemin = Flder.Path & Application.PathSeparator

        Exit For

    End If

Next Flder

If fichier = "" Then

    MsgBox "le fichier n'existe pas", vbInformation + vbOKOnly, "ERREUR"

Else

    fichier = chemin & comp & ".xlsx"

    UserForm2.Label1.Caption = fichier

End If

End Sub

 '-----------------------------------------------------------------------

Private Sub Ouvrir_Click()

Workbooks.Open fichier

MsgBox "Fichier ouvert", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub Renommer_Click()

fichier2 = Flder.Path & Application.PathSeparator & "copie_" & comp & ".xlsx"

    Name fichier As fichier2

    MsgBox "Fichier rennomé", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub Supprimer_Click()

 Kill fichier

 MsgBox "Fichier supprimé", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub Deplace_Click()

Name fichier As "E:\SNCF\" & comp & ".xlsx"

MsgBox "Fichier déplacé", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub sortir_Click()

 UserForm2.Hide

End Sub

Vous pourrez très facilement adapter cet exemple,

Merci de votre attention

Partager |

VBA : Supprimer les doublons

Nous voici donc sur le second article concernant le probléme des doublons dans les bases de données. La procédure suivante va nettoyer la base de tous les doublons qu'elle contient, ici nous considérons que les clés en surnombre sont placées dans  la première colonne.
Par sécurité il n'y a pas d'action dans la base de départ, le contenu de la base est stockée dans un tableau a(), puis après traitement, la nouvelle base contenue dans un tableau c() sera récupérée sur une autre feuille de calcul.
Nous utilisons bien sur l'objet Dictionary pour réaliser ce traitement, vous pouvez consulter l'article précédent si vous ne maîtrisez pas complétement cet objet.


Sub Doublons_BDD()

Dim a(), c()
Dim mondico As Object
Dim ligne, i, k As Long

  Application.ScreenUpdating = False

'va suspendre l'affichage à l'écran des travaux de la macro
'ce qui accélére son exécution le nombre de lignes dans la base pouvant être élevé

  Set mondico = CreateObject("Scripting.Dictionary")
 
  '********************************************************************
  'attention je suis sur la feuille ou se trouve ma base
  'sinon je declare un autre dico puis le dico est stockée dans le tableau
  'dim dico2 as object
  'Set dico2 = Sheets("BD")
  'a = dico2.Range("A1").CurrentRegion.Value
  '***************************************************************
 
  a = Range("A1").CurrentRegion.Value
  'La base est stockée dans le tableau a()

  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  ligne = 1
 
  For i = 1 To UBound(a)
    If Not mondico.Exists(a(i, 1)) Then
      mondico.Add a(i, 1), 1
      For k = 1 To UBound(a, 2): c(ligne, k) = a(i, k): Next k
      ligne = ligne + 1
    End If
  Next

  Sheets("BD-2").[A1].Resize(mondico.Count, UBound(a, 2)) = c
'il faut recopier le tableau c() sur la feuille de calcul "BD-2"
End Sub

Bonne lecture et bon courage pour vos adaptations...

Partager |

VBA : Repérer les doublons

Nous allons consacrés deux articles à la problématique des doublons à l'intérieur
des bases de données. Les doublons pouvant se définir comme deux enregistrement rigoureusement identiques dans une base, les valeurs de tous les champs de l'enregistrement sont communs, ou alors seulement les données d'une colonne  (généralement la colonne qui doit servir de clé unique) sont communes (Numéro de client, E-mail...). Enfin par doublons il faut entendre que la valeur n'est pas forcément doublée, elle peut être triplée etc...

En général nous souhaitons pouvoir repérer les informations présentes en surnombre, cela fera l'objet de cet article. La suppression définitive des enregistrements redondants sera l'objet de l'article suivant.
Il existe plusieurs méthodes et techniques pour intervenir sur cette problématique, j'ai notament montré dans une vidéo du 12/04/2012 comment travailler grâce à la fonction =nb.si() et la commande Validation des données de l'onglet Données.
 En terme de programmation le bon réflexe est de penser Collection et Dictionary. Le premier objet étant réputé très lent en terme de résultat, nous allons ici présenter l'objet Dictionary.
 Cet objet associe des valeurs (nommées Items) à des clés (Keys). Il s'agit en fait d'un tableau à une dimension dont on accède aux éléments par une clé et non par un indice.

Soit un Dictionnaire MonDico :

Keys	Items
bleu	2
rouge	6
vert	9
blanc	4
noir	7

 
Les métodhes et propriétés suivantes peuvent être associés au Dictionnaire :

Add clé,élément	Ajoute une clé et la valeur associée
Exists(clé)	Teste l'existence d'une clé
Tbl=Items	Donne dans un tableau les éléments
Tbl=Keys	Donne dans un tableau les clés
Remove (clé)	Suprime la clé
Removeall	Supprime tous les éléments
Count	Donne le nombre d'éléments
Item(clé) =valeur	Modifie la valeur de la clé
Item(clé)	Donne la valeur associée à la clé
CompareMode=vbTextCompare	Ignore la casse

 
Attention ! Vous ne pourrez pas utiliser l'objet Dictionary si vous n'avez pas chargé la bibliothéque "Scripting". Cocher la ligne "MicroSoft Scripting Runtime" dans la commande Outils / références... de votre éditeur VBA.

La procédure ci dessous va vous permettre de vous familiariser avec les manipulations de base en matière de dictionnaire. Pour ceux qui veulent étudier cette question de manière plus approfondie, lisez l'excellent article de Jacques Boisgontier.

Option Explicit
Option Base 1
Sub ListeDictionnaire()

Dim mondico As Object, cle
Set mondico = CreateObject("Scripting.Dictionary")
'déclaration classique de l'objet dictionnaire notez toutefois le ,cle
'qui servira de variable (type range) pour la colonne clé
Dim TabC()
'tableau non typé et non dimensionné pour recevoir les clés du dictionnaire
Dim TabI()
'tableau non typé et non dimensionné pour recevoir les items du dictionnaire

'*** Les trois méthodes permettant l'association des items aux clés ***
  mondico.item("bleu") = 2
  mondico.item("rouge") = 6
  mondico("vert") = 9
  If Not mondico.Exists("violet") Then mondico.Add "blanc", 4
  mondico.item("noir") = 7
 
 '*** vérifions l'item pour une clé ***
  cle = "noir"
  MsgBox cle & " : " & mondico.item(cle), vbOKOnly + vbInformation
 
  '*** vérifions tous les items de  toutes les clés ***
  For Each cle In mondico.Keys
     MsgBox cle & " : " & mondico.item(cle), _
     vbOKOnly + vbInformation, vbOKOnly + vbInformation, _
     " Clés + Item : "
  Next cle
 
  '*** pour passer des éléments clés et items dans des tableaux ***
  TabC = mondico.Keys
  TabI = mondico.Items
  MsgBox TabC(3) & " : " & TabI(3), _
  vbOKOnly + vbInformation, vbOKOnly + vbInformation, _
  " Clés + Item : "
   
 '*** faire des Statistiques sur les items ***
MsgBox "Total : " & Application.Sum(mondico.Items) _
 & " - Moyenne : " & Application.Average(mondico.Items), _
 vbOKOnly + vbInformation, "Statistiques : "
End Sub

Venons en maintenant au probléme de repérage des doublons, analysons ensemble la procédure suivante qui va repérer par un code couleur touts les doublons de ma base sur la colonne que j'ai choisi comme étant la clé de mon dictionnaire.

Sub Doublons_repere()

Dim mondico As Object, cle
Set mondico = CreateObject("Scripting.Dictionary")
 
Columns("A:A").Select
Selection.Interior.ColorIndex = xlNone
'nous travaillons donc sur la colonne A
'pour chaque clé de A2 jusqu'à la fin de la colonne
  For Each cle In Range("a2", Range("a2").End(xlDown))
  'on va affecter à chaque clé du dictionnaire un item = à 1
     mondico.item(cle.Value) = mondico.item(cle.Value) + 1
     'si la clé est présente 2 fois ou plus on aura un item > à 1
  Next cle
 
  For Each cle In Range("a2", Range("a2").End(xlDown))
   'reste alors le coloriage du fond des cellules pour les item
      'présents 2 fois ou plus
    If mondico.item(cle.Value) > 1 Then cle.Interior.ColorIndex = 6
  Next cle
End Sub

Bonne lecture et bon courage pour vos adaptations...

 

Partager |

VBA : Exporter vers MySQL

Comment réussir l'exportation de données contenues dans une feuille de calcul Excel à l'intérieur d'une table MySQL. A ma grande surprise, j'ai lu à ce sujet plusieurs avis ou articles sur le web expliquant que la chose était impossible. Mais ce n'est pas le moment d'exprimer tout le bien que je pense de certains forums généralistes. Donc ne nous égarons pas, et regardons cette vidéo qui nous permet de comprendre sur un exemple très simple, une procédure VBA qui réalise ce bien modeste exploit....






Bonne consultation...

Partager |

Créer un tachymètre

A la suite de l'article du 12 Mars 2012 sur la création des jauges, nous allons dans cette vidéo, expliquer comment créer un indicateur visuel de type tachymètre grâce à un graphique de type anneaux. Des astuces, encore des astuces toujours des astuces...







Bonne consultation...

Partager |

VBA : L'objet Erreur

Pour en finir avec le sujet des objets, cette vidéo nous explique les bases de la gestion des erreurs en VBA. L'erreur retournée par le système étant considérée aussi comme un objet, nous pouvons alors manipuler ces méthodes et propriétés.

Partager |

VBA : L'explorateur d'objets (2)

A la suite de la vidéo précédente, nous allons découvrir ici, comment utiliser la fonction de recherche de notre explorateur d'objets afin de s'assurer de bien disposer de toutes les informations utiles à notre programmation VBA...

Partager |

VBA : Utiliser l'explorateur d'objets

Dans cette vidéo nous allons découvrir les manipulations de bases permettant de trouver une fonction vba

à l'intérieur de l'explorateur d'objets. Bibliothèques et classes d'objets n'auront plus de secrets pour vous...

Partager |

VBA : Les collections d'objets

Des objets identiques sont regroupés par collections il est important de comprendre cette notion pour faciliter votre programmation. Cette vidéo vous explique toutes les subtilités de cette notion de collection.

Partager |

VBA : Objets et hiérarchie

Cette petite vidéo nous explique rapidement comment ne pas se tromper de cible, c'est à dire de bien programmer le bon objet et d'être capable de lever l’ambiguïté en cas de doute. Bien sûr pour cela vous aurez compris la notion de hiérarchie.

Partager |

VBA : Les objets et leurs méthodes

A la suite de l'article précédent, c'est aux méthodes des objets que nous allons nous intéresser dans cette nouvelle vidéo. De sorte à bien faire la différence entre propriétés et méthodes.

Partager |

VBA : Les objets et leurs propriétés

Nous démarrons aujourd’hui un ensemble de séquences vidéos sur le thème des objets.

Dans cette première leçon comprenons la notion de propriétés et la manière

dont ces propriétés sont rattachées aux objets.

Partager |

VBA : L’objet WebBrowser

En réponse à l’article du 2 avril 2009 sur l’importation de tableau HTML, un lecteur me demande comment on pourrait récupérer des images se trouvant dans le dit tableau ? Evidement la méthode des données externes ne peut convenir ici, mais plusieurs méthodes permettent de réaliser ce travail…Aujourd’hui laissez moi vous présenter l’objet WebBrowser !

- 1 – Avant tous vous allez charger dans votre éditeur VBA, les bibliothèques Microsoft HTML Object Library et Microsoft Internet Controls. (Si vous n’êtes pas sur de la démarche, lisez l’article du 9 Mai 2009)

- 2 – Placez dans votre feuille de calcul, deux objets WebBrower. Pour cela utiliser la commande Développeur / Insérer / Autres contrôles…Dans la liste sélectionnez le contrôle Microsoft Web Brower, cliquez sur le bouton OK et glisser pour tracer votre contrôle. La taille et la position de l’objet n’ont pas d’importance pour l’instant.

- 3 –Comme son nom l’indique un Web Brower est simplement un navigateur Internet que l’on a ici incorporé dans notre feuille de calcul. Donc il va nous permettre d’afficher des pages web et d’en étudier le contenu…

- 4 – La première partie du programme a pour objet d’afficher une page web dans le premier web browser et de trouver des informations sur cette page, notamment le nombre et la liste des images qu’elle contient.

- 5 – La seconde partie ramène dans la feuille une image précise en adaptant la taille du webbrowser à la taille de l’image.

- 6 – Voici le code, puisse t’il vous inspirer des améliorations…

Option Explicit

Private Sub Worksheet_Activate()
'ici je lance ma procédure à l'ouverture du classeur
'à vous de choisir éventuellement un autre événements et (ou) un autre objet
Dim maPageHtml As HTMLDocument
Dim imgHtml As HTMLImg
Dim resultat As String
Dim I As Integer
Dim haut As Integer
Dim larg As Integer
'position et taille du premier webbrowser
Feuil2.WebBrowser1.Left = 1
Feuil2.WebBrowser1.Top = 1
Feuil2.WebBrowser1.Width = 400
Feuil2.WebBrowser1.Height = 200
'position du seond webbrowser
Feuil2.WebBrowser2.Left = 500
Feuil2.WebBrowser2.Top = 1
'affichons une page dans le premier webbrower
Feuil2.WebBrowser1.Navigate "http://www.olivier-picot.fr/pagetest.html"
'analysons le contenu de cette page et retournons
'son url + date de modification + taille + nombre d'images
Set maPageHtml = WebBrowser1.Document
resultat = "adresse de la source : " & maPageHtml.URL & vbLf & _
"derniere modification de la page : " & maPageHtml.LastModified & vbLf & _
"taille de la page : " & maPageHtml.fileSize & " octets " & vbLf & _
"nombre d'images dans la page : " & maPageHtml.images.Length & "."
'affichons cette information
MsgBox resultat, , "Information"
'boucle sur les images pour récupérer leurs noms et écriture dans ma feuille de cacul
For I = 0 To maPageHtml.images.Length - 1
Set imgHtml = maPageHtml.images.Item(I)
Feuil2.Cells(I + 15, 1) = imgHtml.src
Next I
'**************************************
'je recupere la taille de la seconde image de ma page
Set imgHtml = maPageHtml.images.Item(1)
haut = imgHtml.Height
larg = imgHtml.Width
'pour adapter la taille de mon webbrowser à celle de mon image
Feuil2.WebBrowser2.Width = larg
Feuil2.WebBrowser2.Height = haut
'un peu de html et l'image est récupérée
.WebBrowser2.Navigate "about:&amp;amp;amp;lt;img src="http://www.olivier-picot.fr/album2.png" /&amp;amp;amp;gt;"
'il est impossible de passer des variables VBA au code HTML aussi cette méthode ne ‘permet de récupérer toutes les images d'une page sans les nommer une à une et les ‘afficher dans des webbrowser différents
End Sub

Partager |