Les sujets abordés dans cette page Les doublons , Les tris et les filtres , Les variables , Piloter les fichiers fermés (Excel , Access ,les fichiers DBF) . |
Généralités Excel - page 1
Ce qui touche aux userforms - page 2
Piloter d'autres applications depuis Excel - page 3
Fonctions, événements, dates et calendriers - page 4
Formules, audits, répertoires et fichiers - page 5
Doublons, tris et filtres, variables, fichiers fermés, Access - page 6
Commentaires, gestion des erreurs, aide en ligne, recherches, tableaux, pages html, PC et système d'exploitation - page 7
Les objets dans le feuille, liens hypertextes, formats, Visual basic editor, chaines de caractères, modules de classe- page 8
Les Tableaux et graphiques Croisés Dynamiques, fichiers XML - page 9
Le Publipostage Word / Excel - page 10
Librairie Windows Image Acquisition Automation Library v2.0 - page 11
Ce qui touche aux userforms - page 2
Piloter d'autres applications depuis Excel - page 3
Fonctions, événements, dates et calendriers - page 4
Formules, audits, répertoires et fichiers - page 5
Doublons, tris et filtres, variables, fichiers fermés, Access - page 6
Commentaires, gestion des erreurs, aide en ligne, recherches, tableaux, pages html, PC et système d'exploitation - page 7
Les objets dans le feuille, liens hypertextes, formats, Visual basic editor, chaines de caractères, modules de classe- page 8
Les Tableaux et graphiques Croisés Dynamiques, fichiers XML - page 9
Le Publipostage Word / Excel - page 10
Librairie Windows Image Acquisition Automation Library v2.0 - page 11
Les doublons
- Deux classeurs démo regroupant plusieurs exemples
Petite modification apportée par Zon pour le fichier DoublonsMacros2 ,onglet: incrémentation1.Car application.transpose a une limite à 5000 et quelques lignes.Voici le code à mettre derrière l'userform Incrémentation : Merci ZonOption Explicit'adapté pour XL97, V2 application.transopose remplacé'Zon le 23.04.2004Option Base 1Const Titre = "Faites votre choix"Const Lab = "Cliquez sur la colonne où se situent les noms des équipements"Dim Tablo(), Tablo2(), Tablo3()Private Sub commandButton1_Click()With listBox1Select Case .listIndexCase -1: Exit SubCase 0: Insertion (2): Princ "C", 3Case 1: Insertion (1): Princ "C", 3Case Else: Princ Right(.List(.listIndex), 1), .listIndex + 1End SelectUnload MeEnd WithEnd SubPrivate Sub userForm_Initialize()Me.Caption = TitreWith Label1.Caption = Lab.autoSize = TrueEnd WithWith listBox1.List = Array("Colonne A", "Colonne B", "Colonne C", _Colonne D, "Colonne E", "Colonne F")End WithEnd SubSub Princ(K As String, T As Byte)Dim L1&, L2&, C&, Plage As RangeOn Error Resume NextApplication.screenUpdating = FalseL1 = Range(K & "1").End(xlDown).Row + 1L2 = Range(K & 65536).End(xlUp).RowC = Range("IV" & L1).End(xlToLeft).ColumnSet Plage = Range(Range("A" & L1), Cells(L2, C))Plage.Columns("A:B").clearContentsTri Plage, TTablo = transposeGrandTab(Plage.Columns(T).Value)DoublonsPlage.Columns(1) = transposeGrandTab(Tablo2)Plage.Columns(2) = transposeGrandTab(Tablo3)On Error goTo 0End SubPrivate Function Tri(Plage As Range, C As Byte)With Plage.Sort .Cells(C), xlAscending, , , , , , xlNoEnd WithEnd FunctionPrivate Sub Doublons()Dim I&, J&, K&, L&, ItemreDim Tablo2(UBound(Tablo, 2)): reDim Tablo3(UBound(Tablo, 2))J = 1: L = 1: K = 1For I = LBound(Tablo, 2) To UBound(Tablo, 2)If Item = Tablo(1, I) ThenJ = J + 1: Tablo3(K) = J: K = K + 1ElseItem = Tablo(1, I): J = 1Tablo2(K) = L: Tablo3(K) = JL = L + 1: K = K + 1End IfNext IEnd SubPrivate Function Insertion(Nb As Byte)Dim I As ByteFor I = 1 To NbColumns(1).InsertNext IEnd FunctionFunction transposeGrandTab(T) 'Zon'Application.transpose est limité à 5000 et qques jusqu'à XL2002Dim Temp, I&, J&, Z As Byte, Nb As ByteOn Error Resume NextDoNb = Nb + 1Z = UBound(T, Nb + 1)Loop Until ErrIf Nb = 1 ThenreDim Temp(UBound(T), 1 To 1)For I = LBound(T) To UBound(T)Temp(I, 1) = T(I)Next IElsereDim Temp(UBound(T, 2), UBound(T, 1))For I = LBound(T, 2) To UBound(T, 2)For J = LBound(T, 1) To UBound(T, 1)Temp(I, J) = T(J, I)Next JNext IEnd IftransposeGrandTab = TempEnd Function - Un questionnaire de satisfaction clients avec gestion des statistques
Lien supprimé - Regrouper les Chiffres d'affaire par ville
Lien supprimé - Une macro complémentaire de myDearFriend pour supprimer les doublons dans une feuille
Lien supprimé - La méthode rowDifferences :
Lister toutes les cellules d'une ligne , dont le contenu est différent de celui de la cellule de comparaison (la cellule D1 dans l'exemple)Sub Test()Dim Cible As Range, Cell As RangeDim Resultat As StringSet Cible = Rows(1).rowDifferences(Comparison:=Range("D1"))For Each Cell In CibleResultat = Resultat & Cell & vbLfNext CellmsgBox ResultatEnd SubUtiliser la méthode columnDifferences pour effectuer la meme recherche dans une colonne - Empecher la saisie de doublons dans une plage de cellules ( exemple A1:A10)
Sélectionnes la plage de cellules concernéesmenu DonnéesValidationonglet "Options"Sélectionnes "Personnalisé" dans la liste de choixSaisis la formule est : =NB.SI($A$1:$A$10;A1)<2Dans l'onglet "Alerte d'erreur" tu peux ensuite personnaliser le message d'alertecliques sur OK pour valider - Extraire les données de la cellule A1 séparées par une virgule , et les réinsérer dans la cellule B1 sans les doublons
Sub extraireDonneesCellule_A1()Dim Tableau() As StringDim i As ByteDim Un As New Collection'découpage en fonction du séparateur ","Tableau = Split(Range("A1"), ",")'filtre doublonsOn Error Resume NextFor i = 0 To UBound(Tableau)Un.Add Tableau(i), Tableau(i)Next iOn Error goTo 0'réinsertion des donnees dans la cellule B1 sans doublonsFor i = 1 To Un.CountRange("B1") = Range("B1") & Un(i) & ","Next iRange("B1") = Left(Range("B1"), Len(Range("B1")) - 1)End Sub - Etre averti lors de la saisie des doublons dans la plage A1:A5000
Procédure à placer au niveau de la feuille en utilisant l'evenement "Change"Private Sub Worksheet_Change(byVal Target As Excel.Range)If Target.Column = 1 ThenIf Application.worksheetFunction.countIf(Range("A1:A5000"), Target.Value) > 1 Then msgBox "ce nom existe déja"End IfEnd Sub
- Des information générales sur les filtres automatiques
- Extraire les donnees d'une cellule et les trier par ordre alphabetique
- Compter le nombre de lignes visibles aprés l'application d'un filtre automatique
- Afficher tous les critères des filtres actifs , dans une feuille
- Effectuer une somme sur un filtre automatique
Application.worksheetFunction.Subtotal(9, Range(maPlage)) - Nommer des cellules non adjacentes , issues d'un filtre automatique
Sub nommerZoneFiltree()activeWorkbook.Names.Add Name:="Zone1", _refersTo:="=Feuil1!" & Feuil1.autoFilter.Range.specialCells(xlCellTypeVisible).AddressEnd Sub - Appliquer un filtre dans le 4eme champ
Range("A1").autoFilter Field:=4, Criteria1:="mot cible" - Appliquer l'opérateur "différent de" dans un filtre automatique
Exemple : Afficher les données differentes de la valeur 100 dans le 1er champ du filtre automatiqueRange("A1").autoFilter Field:=1, Criteria1:="<>100" - Appliquer l'opérateur "contient" dans un filtre automatique
Exemple : Afficher les données contenant la donnée "XLD" dans le 1er champ du filtreRange("A1").autoFilter Field:=1, Criteria1:=" = * XLD * " - Colorier uniquement les cellules visibles apres l'application d'un filtre automatique
Range("A1:F" & Range("F65536").End(xlUp).Row). _specialCells(xlVisible).Interior.colorIndex = 6 - Vérifier si les flèches du menu déroulant du filtre automatique sont affichées ( Vrai / Faux )
msgBox Worksheets(1).autoFilterMode - Enlever les flèches du menu déroulant d'un filtre automatique
Worksheets(1).autoFilterMode = False - Filtrer une Date dans un filtre automatique
Dans cet exemple les dates sont dans la colonne A , à partir de la cellule A2 . La date à filtrer est issue d'une LisboxATTENTION :Les cellules à filtrer doivent toutes etre du meme format (identique à la cellule A2)Private Sub listBox1_Click()Range("A1").autoFilter 1, Format(listBox, Range("A2").numberFormat)End Sub - Exporter des données de façon conditionnelle vers plusieurs classeurs
Lien supprimé
Les variables servent à enregistrer temporairement des données dans une macro .
par exemple affecter un texte (coucou le forum xld ! ) à une variable (maVariable) ,
et l'afficher dans un Msgbox :
Option Explicit
Sub afficherMessage()
Dim maVariable As String
maVariable = "coucou le forum xld ! "
msgBox maVariable
End Sub
Les données peuvent etre modifiées pendant l'execution de la macro .
par exemple affecter un nombre à une variable , l'afficher dans une Msgbox ,
ajouter la valeur de la cellule A1 à cette variable , puis afficher le nouveau resultat :
Sub afficherValeur()
Dim maVariable As Integer
maVariable = 10
msgBox maVariable
maVariable = maVariable + Range("A1")
msgBox maVariable
End Sub
Une variable possede :
un nom qui permet d'acceder aux donnees qu'elle contient :"maVariable"
et un type de données : String et Integer dans les exemples ci dessus
Le type de données doit etre défini en fonction de la valeur prise par la variable .
Chaque type de donnée utilise un espace mémoire ( de 1 octet pour les types de données Byte jusqu'à 22 octets et plus , pour les types de données Variant ) . Il est donc important de définir le bon type de données pour libérer de l'espace mémoire et ne pas ralentir inutilement le traitement de la macro .
- Les types de données ( informations issues de l'aide en ligne Excel )
Byte :utilisé pour stocker des nombres entiers positifs compris entre 0 et 255.Les variables de type Byte sont stockées sous la forme de nombres uniquescodés sur 8 bits (1 octet), sans signe .Boolean :données pouvant prendre exclusivement les valeurs True (-1) et False (0).Les variables Boolean sont stockées sous la forme de nombres codés sur 16 bits (2 octets).Integer :données contenant des nombres entiers stockés sous forme d'entiers de 2 octets compris entre -32 768 et 32 767.Le type de données Integer permet également de représenter des valeurs énumérées.Dans Visual Basic, le signe % est le caractère de déclaration du type Integer .Remarque :Si vous écrivez "Dim X As Integer" , alors que la valeur est décimale ( par exemple X=5,9 ) , la valeur renvoyée sera égale à 6Long :Nombre entier codé sur 4 octets (32 bits) et dont la valeur est comprise entre -2 147 483 648 et 2 147 483 647.Dans Visual Basic, le signe et commercial (&) est le caractère de déclaration du type Long.Currency :données dont la plage de valeurs s'étend de -922 337 203 685 477,5808 à 922 337 203 685 477,5807.Ce type de données est utilisé dans les calculs monétaires ou dans les calculs à virgule fixe pourlesquels une grande précision est requise. Le signe @ est le caractère de déclaration du type Currency.Single :Type de données qui regroupe des variables à virgule flottante en simple précisionsous forme de nombres à virgule flottante codés sur 32 bits (4 octets),dont la valeur est comprise entre -3,402823E38 et -1,401298E-45 pour les valeurs négatives ,et entre 1,401298E-45 et 3,402823E38 pour les valeurs positives.Dans Visual Basic, le point d'exclamation (!) est le caractère de déclaration du type Single.Double :Type de données stockant sur 64 bits les nombres à virgule flottante en double précisioncompris entre -1,79769313486231E308 et -4,94065645841247E-324 pour les valeurs négatives,et entre 4,94065645841247E-324 et 1,79769313486232E308 pour les valeurs positives.Dans Visual Basic, le signe dièse (#) est le caractère de déclaration du type Double.Date :Type de données utilisé pour stocker les dates et les heures sous la formed'un nombre réel codé sur 64 bits (8 octets). La partie située àgauche du séparateur décimal représente la date, et la partie droite l'heure.String :Type de données composé d'une séquence de caractères contigus interprétés en tant que caractèreset non en tant que valeurs numériques.Une donnée de type String peut inclure lettres, nombres, espaces et signes de ponctuation.Le type de données String peut stocker des chaînes de longueur fixe dont la longueur est comprise entre0 et environ 63 Ko de caractères et des chaînes dynamiques dont la longueur est comprise entre0 et environ 2 milliards de caractères.Dans Visual Basic, le signe dollar ($) est le caractère de déclaration du type String.Object :Type de données représentant toute référence Object. Les variables Object sont stockéessous forme d'adresses codées sur 32 bits (4 octets) faisant référence à des objets. L'instruction Set permet d'attribuer une référence d'objet à la variable.Un exemple pour utiliser une variable Object :Dim appWrd As ObjectSet appWrd = createObject("Word.Application")Pour libérer l'espace mémoire d'une variable Object en fin de procédure , utilisez :Set appWrd = Nothing
Range :Variant :Type de données particulier pouvant contenir des données numériques, des chaînes ou des dates,des types définis par l'utilisateur ainsi que les valeurs spéciales Empty et Null.Le type de données Variant est doté d'une taille de stockage numérique de 16 octets et peutcontenir la même plage de données que le type Decimal, ou d'une taille de stockage decaractère de 22 octets (plus la longueur de la chaîne) ; dans ce dernier cas, il peut stocker tout texte.IMPORTANT : Toutes les variables sont converties en type Variant si aucun autre type de donnéesn'est explicitement déclaré.TRES IMPORTANT : En cas de déclaration de plusieurs variables avec le meme Dim , il faut indiquer le type de donnee pour chaque variable .Exemple :Si pour définir 3 variables des type String (Var1,Var2 et Var3) vous écrivez :Dim Xld1 , Xld2 , Xld3 As StringDans ce cas Xld1 et Xld2 seront de type Variant . Pour y remédier Il faut écrire :Dim Xld1 As String , Xld2 As String , Xld3 As String - Option Explicit
Insérez l'instruction Option Explicit en tout début de procédure .Grâce à cette instruction, vous aurez un message d’erreur qui identifiera toute variable non définie, ou mal orthographiée.Il est conseillé d'avoir une majuscule dans la variable déclarée : Ensuite lors de la saisie de la variable dansla macro , celle-ci reprendra automatiquement la majuscule : cela permet de vérifier les fautes d'orthographeéventuelles .Pour qu'Option Explicit s'insère automatiquement dans chaque nouveau classeur :Allez dans l'éditeur de macrosMenu OutilsOptionsDans l'onglet Editeur , cochez l'option "Déclaration Explicite des variables" ou "Déclaration des variables obligatoire" (en fonction des versions d'Office) - Byref
Moyen permettant de passer à une procédure l'adresse d'un argument plutôt que sa valeur. La procédure agit directement sur la valeur de la variable. La procédure peut ainsi accéder à la variable proprement dite. La valeur réelle de cette dernière peut, de ce fait, être modifiée par la procédure à laquelle elle a été passée. Par défaut, tous les arguments spécifiés dans une procédure sont de type Byref.Si la procédure appelée change la valeur de ces variables, elle changeront au retour dans la procédure appelante. - Byval
Moyen permettant de passer à une procédure la valeur d'un argument plutôt que son adresse. La procédure créée une copie de la variable , cette copie étant supprimée en fin de la procédure. La variable originale n'est donc pas modifiée par la procédure à laquelle elle est passée.Si la procédure appelée change les valeurs des variables , elles ne changeront pas dans la procédure appelanteL'utilisation de byVal implique un temps de calcul plus long et nécessite un espace mémoire plus important. - Les niveaux de variables
Une Variable déclarée à l'interieur d'une macro ne sera utilisable qu'à l'intérieur de celle-ci :Sub Test()Dim Xld As StringXld= "Coucou ! "Msgbox XldEnd SubPour que la variable soit utilisable dans toutes macros du module ,celle-ci doit etre déclarée avant le Sub :Dim Xld As StringSub Test()…End SubPour que la variable soit utilisable pour toutes les macros du projet il faut utiliser l'instruction Public :(La variable doit etre placée dans un module standard)Public Xld As StringSub Test()…End Sub - L'instruction Const : Déclaration de constantes
Une constante est un élément nommé conservant une valeur constante pendant toute l'exécution d'un programme. Il peut s'agir d'une chaîne, d'un littéral numérique, d'une autre constante ou d'une combinaison contenant des opérateurs logiques ou arithmétiques à l'exception de Is et de l'opérateur d'élévation à une puissance. Des constantes supplémentaires peuvent être définies par l'utilisateur via l'instruction Const. Les constantes peuvent remplacer des valeurs réelles partout dans votre code.Il y a plusieurs avantages à utiliser une constante dans votre projet :Eviter de saisir plusieurs fois des textes identiques dans une macro , et donc éviter les erreurs de saisie .Si le texte doit etre modifié dans une macro complexe, vous n'avez plus besoin de parcourir toute la procédure pour la retrouver : il suffit de modifier la constante .Les constantes permettent aussi d'améliorer la lisibilité d'une macro - Les fonctions de conversion des types de données
CBool(expression)CByte(expression)CCur(expression)CDate(expression)CDbl(expression)CDec(expression)CInt(expression)CLng(expression)CSng(expression)CStr(expression)CVar(expression)Consultez l'aide en ligne Excel pour obtenir plus d'informations sur chacune de ces fonctions . - Des informations sur les variables Objet Collection
- La fonction typeName : afficher des informations sur une variable.
(Informations issues de l'aide en ligne Excel)typeName(varname)L'argument varname est une valeur de type Variant pouvant contenir toute variable à l'exception d'une variable de type défini par l'utilisateur.La fonction typeName peut renvoyer l'une des chaînes suivantes :Byte , Integer , Long , Single , Double , Currency , Decimal , Date , String , Boolean , Error , Empty , Null , Unknown , NothingSi l'argument est un tableau, la chaîne renvoyée peut être n'importe laquelle des chaînes possibles (ou Variant) suivie de parenthèses vides. Par exemple, si l'argument varname est un tableau de nombres entiers, la fonction typeName renvoie la valeur "Integer()".Quelques exemples d'utilisation :Dim nullVar, myType, strVar As String, intVar As Integer, curVar As CurrencyDim arrayVar (1 To 5) As IntegernullVar = Null ' Affectation d'une valeur Null.myType = typeName(strVar) ' Renvoie "String".myType = typeName(intVar) ' Renvoie "Integer".myType = typeName(curVar) ' Renvoie "Currency".myType = typeName(nullVar)' Renvoie "Null".myType = typeName(arrayVar)' Renvoie "Integer()".
'Boucler sur les contrôles d'un userForm et vérifier s'il s'agit d'un TextboxDim Ctrl As ControlFor Each Ctrl In userform1.ControlsIf typeName(Ctrl) = "textBox" Then'.........End IfNext Ctrl
'Vérifier si la variable Nb est de type IntegerIf typeName(Nb) = "integer" Then'Vérifier le type de donnée contenu dans la cellule A1msgBox typeName(Range("A1").Value)
- La fonction varType: contrôler le type de variable qui est appliqué dans la procédure .
(Informations issues de l'aide en ligne Excel)varType(varname)L'argument varname est une valeur de type Variant pouvant contenir toute variable à l'exception d'une variable de type défini par l'utilisateur.Constante - Valeur - DescriptionvbEmpty - 0 - Empty (non initialisée)vbNull - 1 - Null (aucune donnée valide)vbInteger - 2 - EntiervbLong - 3 - Entier longvbSingle - 4 - Nombre à virgule flottante en simple précisionvbDouble - 5 - Nombre à virgule flottante en double précisionvbCurrency - 6 - Valeur monétairevbDate - 7 - Valeur de datevbString - 8 - ChaînevbObject - 9 - ObjetvbError - 10 - Valeur d'erreurvbBoolean - 11 - Valeur booléennevbVariant - 12 - Variant (utilisée seulement avec des tableaux de variants)vbDataObject - 13 - Objet d'accès aux donnéesvbDecimal - 14 - Valeur décimalevbByte - 17 - OctetvbUserDefinedType - 36 - Variant contenant des types définis par l'utilisateurvbArray - 8192 - Tableau
Les constantes mentionnées dans ce tableau sont spécifiées par Visual Basic pour Applications. Vous pouvez par conséquent utiliser leur nom n'importe où dans votre code à la place des valeurs réelles correspondantes.Remarque :La fonction varType ne renvoie jamais la valeur pour la constante vbArray elle-même. Elle est toujours ajoutée à une autre valeur pour indiquer un tableau d'un type particulier. La constante vbVariant n'est renvoyée que lorsqu'elle est associée à vbArray pour indiquer que l'argument de la fonction varType est un tableau de type Variant. Par exemple, la valeur renvoyée pour un tableau de nombres entiers est le résultat de vbInteger + vbArray, ou 8194. Si un objet possède une propriété par défaut, varType (object) renvoie le type de celle-ci.Quelques exemples'Intercepter l'utilisation du bouton "Annuler" et la croix de fermeture d'un InputboxDim ReponseReponse = Application.inputBox("Saisissez vos données")If varType(Reponse) = vbBoolean Then msgBox " opération annulée"'Vérifier si la cellule A1 est videIf varType(Range("A1")) = 0 Then msgBox "La cellule est vide"If varType(Range("A1")) = vbEmpty Then msgBox "La cellule est vide"
- If typeOf End If : Contrôler vers quel objet de l'application pointe la variable.
Quelques exemples :'Retrouver le nom de toutes les feuilles graphiques dans le classeur actifDim Sh As ObjectFor Each Sh In activeWorkbook.SheetsIf typeOf Sh Is Chart Then msgBox Sh.NameNext'Vérifier si une cellule est sélectionnée dans la feuilleIf typeOf Selection Is Range Then'Boucler sur les checkBoxes d'un Userform et leur attribuer la valeur VraiDim Ctrl As ControlFor Each Ctrl In Me.ControlsIf typeOf Ctrl Is MSForms.checkBox Then Ctrl.Value = TrueNext - Les types de données définis par l'utilisateur .
Vous pouvez utiliser l'instruction Type afin de déclarer un type de variable personnalisé .Les types de données définis par l'utilisateur peuvent contenir un ou plusieurs éléments de n'importe quel type de données .Il est possible de créer des types adaptés à un projet pour en faciliter le traitement .Les types définis par l'utilisateur placés dans un module standard sont Public par défaut. Ils peuvent etre redéfinis en Private. Les types utilisés dans les modules de classe sont toujours Private et ne peuvent pas être modifiés en Public.'Ces lignes sont sont à placer tout en haut du moduleType VoitureCouleur As StringCylindree As LonganneeAchat (1 to 5) As DateEnd TypeSub testVariable()Dim X As Voiture 'Déclaration de la variable personnaliséeX.Couleur = "Rouge" 'Ecriture dans les variablesX.Cylindree = 2000msgBox "Cette voiture " & X.Couleur & " a une cylindrée de " & X.Cylindree & " cc" 'lecture variableEnd SubIl est possible de déclarer un tableau multi-dimensionnel :Dim Tableau(1 to 5, 1 to 10) As Voitureet ensuite, d'écrire ou lire chaque information en utilisant la synthaxe :Tableau(1,5).Couleur - Passer une information Excel dans une Variable Word
'La procédure dans Excel'nécessite d'activer la reference Microsoft Word xx.x Object LibraryPrivate Sub commandButton1_Click()'utilisation de la methode Run depuis Excel ,'pour déclencher une macro Word contenant un paramètreDim wordApp As Word.ApplicationDim wordDoc As Word.DocumentDim monParametreVB As StringSet wordApp = createObject("Word.Application")wordApp.Visible = TrueSet wordDoc = wordApp.Documents.Open("C:\monDocument.doc") 'ouverture doc WordmonParametreVB = "azerty"'déclenchement de la macro Word'Remarque : la macro Word doit etre placée au niveau de thisDocumentwordDoc.laMacro monParametreVBEnd Sub'La procédure dans Word, à placer au niveau de thisDocumentOption ExplicitSub laMacro(maVariableWord As String)thisDocument.Range.Text = maVariableWordEnd Sub
Dans les variables, un petit truc pour éviter les erreurs, après les premiers caractères de la variable, faire Ctrl et Espace, donne soit la variable, soit un choix dans la liste des variables .
Définir un groupe de constantes liées : Utiliser l'instruction ENUM pour créer une énumeration .
Public Enum Coeff
Coeff_2 = 2
Coeff_3 = 3
Coeff_4 = 4
End Enum
Sub Test()
msgBox 500 * Coeff.Coeff_2 'résultat = 1000
End Sub
La saisie de Coeff. Lors de l'écriture d'une macro , permet d'afficher rapidement la liste des coefficients disponibles
Piloter les fichiers fermés (Excel , Access ,les fichiers DBF)
- Lister tous les classeurs d'un répertoire et récupérer les données de la cellule A1 dans chaque fichier sans l'ouvrir
- Un autre exemple
Lien supprimé - Recherche dans des classeurs fermés , avec choix de l'onglet et affichage des résultats multicolonnes dans un USF
- Piloter une base Access en utilisant l'ActiveX Data Objects (ADO)
Lister les tables de la base AccessLister les champs d'une tableCréer une nouvelle table dans la base AccessAfficher les données d'une table , dans une listBoxEffectuer une jointure entre 2 tablesAfficher la liste des métaDonnées de la base AccesLien supprimé - Additionner les tableaux de plusieurs classeurs fermés
Lien supprimé - Vérifier si une valeur existe dans le champ "numeroRemorque" , d'une table Access "Remorque"
Sub controleValeurTable()Dim Conn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim Fichier As String, rSQL As StringDim Valeur As IntegerFichier = "C:\maBase.mdb"Valeur = inputBox("Saisir le numero à rechercher dans la table")Set Conn = New ADODB.ConnectionConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _Fichier & ";"rSQL = "SELECT Remorque.numeroRemorque" & _" FROM Remorque" & _" WHERE numeroRemorque="Set rsT = New ADODB.RecordsetWith rsT.activeConnection = Conn.Open rSQL & Valeur, , adOpenStatic, adLockOptimistic, adCmdTextEnd WithIf rsT.EOF ThenmsgBox "Le numéro " & Valeur & " n'existe pas dans la table Remorque . "ElsemsgBox "Le numéro " & Valeur & " existe la table Remorque . "End IfrsT.CloseConn.CloseEnd Sub - Extraire la valeur Maxi ( ou Mini) dans le champ "nbHeuresAstreinte" de la Table2 , d'une base Access
Sub extraireValeurMaxTableAccess()Dim cn As ADODB.ConnectionDim Rs As ADODB.RecordsetDim Fichier As StringFichier = thisWorkbook.Path & "\maBase_V01.mdb"Set cn = New ADODB.Connectioncn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _Fichier & ";"Set Rs = New ADODB.RecordsetWith Rs.activeConnection = cn.Open "SELECT max(nbHeuresAstreinte) FROM Table2", , adOpenStatic, adLockOptimistic, adCmdText'.Open "SELECT min(nbHeuresAstreinte) FROM Table2", , adOpenStatic, adLockOptimistic, adCmdTextEnd WithmsgBox Rs(0)Rs.CloseSet Rs = Nothingcn.CloseSet cn = NothingEnd Sub - Lister les éléments du champ "Matricule" d'une table nommée "Table2" , sans afficher de doublons
Sub listeElementsChamp_sansDoublon()Dim Conn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim Fichier As String, rSQL As StringFichier = thisWorkbook.Path & "\maBase_V01.mdb"Set Conn = New ADODB.ConnectionConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _Fichier & ";"rSQL = "SELECT DISTINCT Matricule" & _"FROM Table2 "Set rsT = New ADODB.RecordsetWith rsT.activeConnection = Conn.Open rSQL, , adOpenStatic, adLockOptimistic, adCmdTextEnd WithIf rsT.EOF ThenrsT.CloseConn.CloseExit SubEnd IfFeuil1.Range("A1").copyFromRecordset rsTrsT.CloseConn.CloseEnd Sub - Exporter la Feuil1 d'un classeur fermé , dans un fichier texte
Sub excelVersFichierTexte()Dim Rs As New ADODB.RecordsetDim Fichier As String, Feuille As StringDim xConnect As String, xSql As StringFichier = "C:\Documents and Settings\michel\monClasseur.xls"Feuille = "Feuil1"xConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier & ";" & _"Extended Properties=Excel 8.0;"xSql = "SELECT * FROM [" & Feuille & "$];"Set Rs = New ADODB.RecordsetRs.Open xSql, xConnect, adOpenForwardOnly, adLockReadOnly, adCmdTextOpen "C:\Documents and Settings\michel\essai.txt" For Output As #1Do Until Rs.EOFPrint #1, Rs.getString(, 600, ";", vbCrLf, ""); 'exemple avec séparateur ";" (point virgule)LoopClose #1End SubLes 5 arguments de la la methode getString:1. adClipString définit le format du recordset :le format est de type chaîne de cararacteres.2. le nombre d'enregistrements à récupérer .Par défaut tous les enregistrements sont récupérés .3. le délimiteur de colonnes.4. le délimiteur d'enregistrements.5. indique comment représenter des valeurs nulles .
- Les bonnes pratiques lors de la création des noms de champs :
Nom le plus court possiblePas d'espacePas d'accentPas de caractères spéciaux - Lister les éléments du champ "Matricule" de la "Table1" qui n'existent pas dans le champ "Matricule" de la "Table2"
Sub listeElementsChamp_InexistantsDans2emeTable()Dim Conn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim Fichier As String, rSQL As StringFichier = "C:\maBase_V01.mdb"Set Conn = New ADODB.ConnectionConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _Fichier & ";"rSQL = "SELECT Table1.Matricule,Table1.Nom" & _" FROM Table1 WHERE Table1.Matricule NOT IN (SELECT Table2.Matricule" & _" FROM Table2 )"Set rsT = New ADODB.RecordsetWith rsT.activeConnection = Conn.Open rSQL, , adOpenStatic, adLockOptimistic, adCmdTextEnd WithIf rsT.EOF ThenrsT.CloseConn.CloseExit SubEnd IfFeuil1.Range("A1").copyFromRecordset rsTrsT.CloseConn.CloseEnd Sub - Récupérer les informations d'un classeur corrompu
Une autre possibilité sans macro ( à partir d'Excel2000 ?)menu FichierOuvrirSélectionnes le classeur qui pose probleme dans la boite de dialoguecliques sur la droite du bouton "Ouvrir" , en bas dans la boite de dialogueSélectionnes l'option "Ouvrir et réparer"Tu peux aussi essayer d'ouvrir ton classeur avec Open OfficeIl est parfois possible d'ouvrir les classeurs corrompus grace à cette suite bureautiqueD'autres informations issues de l'aide en ligne Microsoft - Récupérer les macros dans un classeur corrompu
- Créer une nouvelle base Access
Sub creerNouvelleBaseDeDonnees()'activer la reference microsoft ADO ext x.x for DLL and SecurityDim Cat As ADOX.CatalogSet Cat = createObject("ADOX.Catalog")Cat.Create _"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\maNouvelleBase.mdb"End SubDeux autres Démos de michel_M qui permettent de créer et gérer une base de données Access , sans avoir MS Access d'installéLien suppriméLien suppriméLien supprimé - Remplacer les constantes par leur valeur lors de la création d'une table Acces , en VBA
- Ecrire dans un classeur fermé , en insérant les données à la suite des valeurs existantes
Lien supprimé - Afficher la deuxieme valeur la plus elevee du champ ""nbHeuresAstreinte" de la table "Table2"
Sub deuxiemeValeurPlusElevee()'Afficher la 2eme valeur la plus élevée dans'le champ "nbHeuresAstreinte" de la table "Table2"Dim Conn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim Fichier As String, rSQL As StringFichier = thisWorkbook.Path & "\maBase_V01.mdb"Set Conn = New ADODB.ConnectionConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _Fichier & ";"rSQL = "SELECT MAX(nbHeuresAstreinte) AS nbHeuresAstreinte FROM Table2" & _" WHERE(nbHeuresAstreinte <(SELECT MAX(nbHeuresAstreinte) AS nbHeuresAstreinte FROM Table2))"Set rsT = New ADODB.RecordsetWith rsT.activeConnection = Conn.Open rSQL, , adOpenStatic, adLockOptimistic, adCmdTextEnd WithmsgBox rsT.Fields(0).ValuersT.CloseConn.CloseEnd Sub - Compacter une base Access
Sub compacterBaseAccess()'necessite d'activer la reference Microsoft Jet and Replication Objects 2.6 LibraryDim bdCompact As String, Fichier As StringDim Source As String, Destination As StringDim jtEng As JRO.jetEngineFichier = "C:\Documents and Settings\michel\maBase_V01.mdb" 'la base existantebdCompact = "C:\Documents and Settings\michel\maBaseCompactee.mdb" 'la nouvelle base crééeSource = "Data Source=" & FichierDestination = "Data Source=" & bdCompact & ";" & "Jet OLEDB:Encrypt Database=True"Set jtEng = New JRO.jetEnginejtEng.compactDatabase Source, DestinationSet jtEng = NothingEnd Sub - Ecrire dans une cellule spécifique d'un classeur fermé ( exemple dans la cellule A1 de la Feuil1 )
Sub exportDonneeDansCellule()Dim Cn As ADODB.ConnectionDim Cd As ADODB.CommandDim Rst As ADODB.RecordsetDim Fichier As StringFichier = "C:\dossier\monClasseur.xls" 'adapter le chemin des fichiers fermésSet Cn = New ADODB.ConnectionCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=" & Fichier & ";" & _"Extended Properties=""Excel 8.0;HDR=No;"";"Set Cd = New ADODB.CommandCd.activeConnection = CnCd.commandText = "SELECT * fromFeuil1$A1:A1
"Set Rst = New ADODB.RecordsetRst.Open Cd, , adOpenKeyset, adLockOptimisticRst(0).Value = " donnée test"Rst.UpdateCn.CloseSet Cn = NothingSet Cd = NothingSet Rst = NothingEnd Sub - Lire une cellule spécifique d'un classeur fermé
- Extraire uniquement les données de la colonne A , d'un classeur fermé
Private Sub userForm_Initialize()'nécessite d'activer la référence Microsoft activeX Data Object 2.x LibraryDim Rs As ADODB.RecordsetDim Cn As StringDim Cible As StringDim Fichier As StringFichier = "C:\Fichier.xls"Cn = "DRIVER={Microsoft Excel Driver (*.xls)};" & _"readOnly=1;DBQ=" & FichierCible = "SELECT * FROM [Feuil1$];"Set Rs = New ADODB.RecordsetRs.Open Cible, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText'les données à récuperer sont dans la colonne AIf Not Rs.EOF ThencomboBox1.addItem Rs.Fields(0).Name 'enteteDo While Not Rs.EOFcomboBox1.addItem Rs.Fields(0).ValueRs.moveNextLoopEnd IfRs.CloseSet Rs = NothingEnd Sub - Ajouter un enregistrement dans une table Access
Sub exportDonnees_Excel_Vers_Access()'ajouter un enregistrement dans une table Access'necessite d'activer la reference Microsoft ActiveX Data Objects x.x LibraryDim Conn As New ADODB.ConnectionDim rsT As New ADODB.RecordsetDim maTable As StringmaTable = "Table1"With Conn.Provider = "Microsoft.JET.OLEDB.4.0".Open "C:\Documents and Settings\michel\Excel\maBase_V01.mdb"End WithWith rsT.activeConnection = Conn.Open maTable, lockType:=adLockOptimisticEnd WithWith rsT.addNew.Fields("Nom").Value = Range("A1").Fields("prixUnit").Value = Range("B1").Fields("Matricule").Value = Range("C1").UpdateEnd WithrsT.CloseConn.CloseEnd Sub - Suppression conditionnelle d'enregistrements , dans une table Access
Sub suppressionEnregistrementTable()Dim Cn As ADODB.ConnectionDim Rst As ADODB.RecordsetDim Requete As String, maTable As String, Donnee As StringDim Valeur As IntegerSet Cn = New ADODB.ConnectionSet Rst = New ADODB.RecordsetCn.Provider = "Microsoft.Jet.Oledb.4.0"Cn.connectionString = "C:\maBase_V02.mdb"Cn.OpenmaTable = "lesPoints"''*pour des valeurs numeriques *'suppression des enregistrements si le champ "nbPoints" est égal à 5 , dans la table "lesPoints"'Valeur = 5Requete = "DELETE * FROM " & maTable & " WHERE nbPoints=" & Valeur''** pour des données texte **'suppression des lignes si le champ "Nom" est égal à "Nom03" , dans la table "lesPoints"'Donnee = "Nom03"'Requete = "DELETE * FROM " & maTable & " WHERE ?[Nom]='" & Donnee & "'"'****Cn.Execute RequeteCn.CloseEnd Sub - Importer les donnees de tous les onglets d'un classeur fermé , vers la feuille active
- Quelques exemples actions dans des classeurs fermés et des bases Access
Boucler sur tous les classeurs fermés d'un répertoire , et importer les données de cellules discontinuesImporter dans la feuille active les données de tous les onglets d'un classeur ferméAjouter un nouvel onglet dans un classeur fermé , et y insérer des donnéesTransférer une Table Access dans un classeur ferméLien supprimé - Créer une nouvelle feuille dans un classeur fermé et y exporter les données de la feuille active
Sub Export_versNouvelleFeuille_classeurExcelFerme()'transférer la feuille "devis" dans un nouvel onglet d'un classeur ferméDim oRS As ADODB.RecordsetDim oConn As ADODB.ConnectionDim maFeuille As String, prepaTable As StringDim j As Integer, i As Integer'nom(sans espace!) de la feuille Excel qui va etre créée dans le classeur fermémaFeuille = "archiveDevis001"Set oConn = New ADODB.Connection oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=C:\classeur1_Fermé.xls;" & _"Extended Properties=""Excel 8.0;HDR=NO;"""For i = 1 To 10 'nombre de colonnes à transférer'paramétrage entêtes de colonnes et types de donnéesprepaTable = prepaTable & "Colonne" & i & " Memo ," 'adapter les types de donnéesNext iprepaTable = Left(prepaTable, Len(prepaTable) - 1)'creation nouvelle Feuille ExceloConn.Execute "create table " & maFeuille & "(" & prepaTable & ")"Set oRS = New ADODB.RecordsetoRS.Open "Select * from " & maFeuille, oConn, adOpenKeyset, adLockOptimisticFor j = 1 To 40 'nombre de lignes à transfereroRS.addNewFor i = 1 To 10 'nombre de colonnes à transféreroRS.Fields(i - 1) = activeSheet.Cells(j, i)Next ioRS.UpdateNext joRS.CloseoConn.CloseEnd Sub - Importer les feuilles complètes de plusieurs classeurs fermés
Lien supprimé - Modifier un champ d'une table Access
'Rechercher la valeur de la cellule A1 dans le champ "Matricule" de la table "maTable" , et modifier le champ "leChamp"Sub test()Dim Conn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim fld As ADODB.FieldSet Conn = New ADODB.ConnectionWith Conn' Définition du fournisseur OleDB pour la connexion.Provider = "Microsoft.JET.OLEDB.4.0"' Ouverture d'une connexion.Open thisWorkbook.Path & "\maBase_V01.mdb"End WithSet rsT = New ADODB.Recordset'table nommée "maTable"rsT.Open "maTable", Conn, adOpenKeyset, adLockOptimisticWith rsT.moveFirst'recherche la valeur de la cellule A1 dans champ "Matricule".Find ("Matricule=" & Cells(1, 1))'quand la valeur est trouvée , on modifie le champ "leChamp".Fields("leChamp") = "xxxx".UpdateEnd WithConn.CloseEnd Sub - Requète dans un classeur fermé : Additionner les valeurs d'un champ avec critères filtres
Lien supprimé - Ajouter une colonne dans une table Access existante
Sub ajoutColonne_tableAccessExistante()Dim Cnn As New ADODB.ConnectionDim Cat As New ADOX.CatalogDim Rst As New RecordsetDim Reponse As String, Fichier As StringOn Error goTo FinFichier = "C:\maBase_V02.mdb"Cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _Data Source= ' & Fichier & "';"Set Cat.activeConnection = CnnCat.Tables("lesPoints").Columns.Append "nouveauChamp", adWChar, 50Rst.Open "lesPoints", Cnn, adOpenKeyset, adLockOptimistic, adCmdTableFin:Cnn.CloseSet Rst = NothingSet Cat = NothingSet nouvelleColonne = NothingSet Cnn = NothingEnd Sub - Effectuer un tri croissant dans le champ "Nom" d'une table Access
Sub tri_Croissant_Champ_baseAccess()'effectuer un tri croissant dans la colonne "Nom" de la table "lesPoints"Dim Cnn As New ADODB.ConnectionDim Cat As New ADOX.CatalogDim indexTri As New ADOX.IndexDim Rst As New ADODB.RecordsetDim Fichier As StringOn Error goTo FinFichier = "C:\maBase_V03.mdb"Cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _Data Source=' & Fichier & "';"Set Cat.activeConnection = CnnWith indexTri.Columns.Append "Nom".Columns("Nom").sortOrder = adSortAscending 'tri croissant.Name = "Ascending"'.Columns("Nom").sortOrder = adSortDescending 'pour les tris décroissants'.Name = "Descending".indexNulls = adIndexNullsAllowEnd With'ajout d'un index pour la table "lesPoints"'attention : renvoie une erreur si un index existe déjàCat.Tables("lesPoints").Indexes.Append indexTriRst.Index = indexTri.NameRst.Open "lesPoints", Cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect'import dans la feuille Excel des données triéesFeuil1.Range("A1").copyFromRecordset RstCat.Tables("lesPoints").Indexes.Delete indexTri.Name 'suppression indexFin:Cnn.CloseSet Cat = NothingSet indexTri = NothingSet Rst = NothingSet Cnn = NothingEnd Sub - Lister les utilisateurs connectés à une base Access
Sub listerConnectesBaseAccess()Dim Cible As StringDim Fso As ObjectCible = "J:\maBase.ldb"Set Fso = createObject("Scripting.fileSystemObject")If Fso.fileExists(Cible) = False Then Exit SubOpen Cible For Input As #1Input #1, CibleClose #1msgBox CibleEnd SubUne autre solutionSub listerConnectesBaseAccess_V02()Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"Dim Cnn As New ADODB.ConnectionDim Rst As ADODB.RecordsetDim Fichier As StringFichier = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _Data Source= & Fichier & ";"Set Rst = Cnn.openSchema(adSchemaProviderSpecific, , JET_SCHEMA_USERROSTER)Debug.Print Rst.getStringCnn.CloseEnd Sub - Lister le nom des feuilles d'un classeur fermé
Remarque :Si vous avez des plages de cellules nommées dans les classeurs fermés , il est possible de les filtrer (elles ne possèdent pas le symbole $ dans leur nom ) - Créer des liens hypertextes vers chaque onglet de classeurs fermés
Cet exemple boucle sur tous les classeurs fermés d'un repertoire , pour lister le nom des feuilles et créer un lien hypertexte vers chacun des ongletsLien supprimé - Récupérer des plages de cellules précises dans plusieurs classeurs fermés ( une démo de @+Thierry )
Lien suppriméUne adaptation de michel_M pour les versions d'office antérieuresLien suppriméUne nouvelle version de @+Thierry qui permet d'importer plusieurs plages de cellules DiscontinuesLien supprimé - Exporter des images dans une base Access puis les réimporter dans un userForm Excel
Lien suppriméUn autre exemple qui utilise l'objet Stream (uniquement disponible à partir de la version ADO 2.5)Lien supprimé - Exporter une image placée dans une Feuille , vers une base Access
- Supprimer les enregistrements d'une table Access , si le champ "cityName" est vide
Sub supprimerEnregistrements_siChampVide()Dim Cn As ADODB.ConnectionDim Rst As ADODB.RecordsetDim Requete As String, maTable As StringSet Cn = New ADODB.ConnectionSet Rst = New ADODB.RecordsetCn.Provider = "Microsoft.Jet.Oledb.4.0"Cn.connectionString = "C:\maBase.mdb"Cn.OpenmaTable = "Table1"'supprime l'enregistrement si le champ "cityName" est videRequete = "DELETE * FROM " & maTable & " WHERE cityName IS NULL"Cn.Execute RequeteCn.CloseEnd Sub - Afficher la version MDAC du poste ( Microsoft Data Access Components )
Sub VersionMDAC()Dim Cn As ADODB.ConnectionSet Cn = createObject("ADODB.Connection")msgBox "Version MDAC : " & Cn.VersionSet Cn = NothingEnd Sub - Les limitations d'Excel , utilisé comme une base de données
Il n'est pas possible de supprimer les lignes complétes dans un classeur fermé (enregistrements )Vous obtiendrez un message d'erreur "La suppression des données dans un table attachée n'est pas géré par le pilote ISAM"Vous pourrez uniquement vider les cellulesVous ne pourrez pas supprimer les lignes vides qui contenaient les données supprimées et les requetes continueront d'afficher l'es enregistrements vides correspondant à ces lignes vides.Il n'est pas possible de supprimer une cellule contenant une formule :Vous aurez un message d'erreur "L'opération demandée n'est pas autorisée dans ce contexte"Excel ne peut pas gérer les connections multiples et simultanées à un meme classeurLes requetes répétées peuvent entrainer des problemes de mémoire disponible dans ExcelIl n'est pas possible d'utiliser un classeur protégé par un mot de passeIl n'est pas possible d'utiliser le classeur si la feuille contenant les données est protégéeLa gestion des tables :Les onglets ( les tables ) contiennent le symbole $ en fin de nom , ce qui n'est pas le cas des plages de cellules nommées ( pourtant aussi considérées comme des tables lors des requetes )Par contre si vous avez ajouté une table dynamiquement dans un classeur ( en utilisant par exemple "Create Table" ou "SELECT INTO" ) , 2 noms différents sont renvoyés pour cette nouvelle table : avec et sans $En fait si vous ouvrez le classeur manuellement vous constaterez que l'onglet est bien ajouté mais aussi une plage de cellules nommées correspondant à la plage de données insérées dynamiquement ( voir Insertion/nom/definir ) ar exemple =maNouvelleFeuille!$A$1:$C$1265Nota :Lors des requètes pour lister le nom des onglets d'un classeur fermé , par ADOX ou ADO(méthode openSchema) , les noms sont renvoyés par ordre alphabétiquePar défaut , le pilote ODBC analyse uniquement les 8 premieres lignes du classeur fermé pour déterminer le type de données dans chaque colonne.Cela peut entrainer 2 types de problemes :1. Dans certains cas particuliers , les données exportées vers un classeur fermé peuvent etre tronquées . Si , par exemple , les 8 premiers enregistrements d'un champ contiennent des données texte inférieur ou égal à 255 caractères , le champ sera considéré de type Texte . Si ensuite vous ajoutez des enregistrements de longueur plus importante ils seront tronqués .2. Si vous voulez importer les informations d'une colonne qui contient à la fois des données numériques et texte , c'est le type majoritaire dans les 8 premiere lignes qui définira le type de données à récupérer : les autres données de la colonnes seront considérées comme NULL (vide)Si la colonne contient 4 valeurs numériques et 4 valeurs texte , la requete renvoie 4 nombres et 4 valeurs NULL.La seule solution consiste à activer l'option d'importation "IMEX=1" ( exemple : "extended properties=""Excel 8.0;IMEX=1""" ) . Les données numériques seront importées comme du texte .Je n'ai pas vérifié le point suivant , mais l'aide MSDN indique :Avertissement concernant la modification de données Excel à l'aide d'ADO : Lorsque vous insérez des données texte , la valeur de texte est précédée d'une apostrophe. Ceci peut provoquer des problèmes par la suite lors du travail avec les nouvelles données. - Publipostage ciblé Word Excel
Cette démo de michel_M permet d'effectuer un publipostage ciblé avec plusieurs options de filtreLien supprimé - Insérer des formules de liaison dans un classeur fermé
Une démo de michel_MLien supprimé - Information sur la propriété HDR , pour la connection à un classeur fermé
par exemple : "Extended Properties=""Excel 8.0;HDR=Yes"""si HDR =No , la premiere ligne est considérée comme un enregistrementsi HDR=Yes , la premiere ligne est considérée comme un entête - Supprimer une table dans une base Access
Sub supprimerTableAccess()Dim Cn As New ADODB.ConnectionDim maBase As StringmaBase = "C:\Documents and Settings\michel\dossier\maBase.mdb"Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & maBaseCn.Execute "DROP TABLE laTable"Cn.CloseSet Cn = NothingEnd Sub - Requête filtrée sur des dates , des nombres ou du texte
Lien supprimé - Filtrer une plage de valeurs à importer : Récupérer les données comprises entre 3 et 5
exemple : récupérer uniquement les valeurs entre 3 et 5 dans le champ "numeroType" de la Table1rSQL = "SELECT * FROM Table1 WHERE numeroType BETWEEN 3 AND 5" - Lister les enregistrements si le champ "nomVille" contient les données Vienne, Condrieu ou xldCity
Sub requeteFiltreSurListeDonnees()Dim Cn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim Fichier As String, rSQL As StringFichier = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"Set Cn = New ADODB.ConnectionCn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Fichier & ";"rSQL = "SELECT * FROM Table1 WHERE nomVille IN ('Vienne','Condrieu','xldCity')"Set rsT = New ADODB.RecordsetrsT.Open rSQL, Cn, adOpenForwardOnly, adLockReadOnly, adCmdTextIf Not rsT.EOF Then Range("A1").copyFromRecordset rsTrsT.CloseCn.CloseEnd Sub - Et inversement , pour lister les enregistrements qui ne sont pas compris dans la liste de données
rSQL = "SELECT * FROM Table1 WHERE nomVille NOT IN ('Vienne','Condrieu','xldCity')" - Des informations sur l'opérateur LIKE
Le lien vers l'aide en ligne Microsoft - Une autre méthode pour ajouter un enregistrement dans une table Access
Sub ajoutEnregistrementTableAccess()'source : K DalesDim Cn As ADODB.ConnectionDim xSQL As StringSet Cn = New ADODB.ConnectionCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _"DBQ=C:\Documents and Settings\michel\dossier\general\excel\Database.mdb"xSQL = "INSERT INTO maTable " _& "VALUES ('" & Range("A1").Value & "', '" & Range("A2").Value & "', '" _& Range("A3").Value & "', '" & Range("A4").Value & "')"Cn.Execute xSQLCn.CloseSet Cn = NothingEnd Sub - Comparer 2 colonnes dans des feuilles différentes , et lister les données communes
La Feuil1 contient une colonne dont l'entete s'appelle numeroPeriode1 .La Feuil2 contient une colonne dont l'entete s'appelle numeroPeriode2L'objectif est de comparer les 2 colonnes , puis de lister les données de la Feuil2 qui apparaissent aussi dans la Feuil1Sub requeteControleDoublons()Dim Source As ADODB.ConnectionDim Requete As ADODB.RecordsetDim Fichier As String, xSQL As StringDim i As LongFichier = "C:\maBase.xls"Set Source = New ADODB.ConnectionSource.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _"data source=" & Fichier & ";" & _"extended properties=""Excel 8.0;HDR=Yes"""xSQL = "SELECT DISTINCT Feuil2$.numeroPeriode2 " & _"FROM [Feuil2$] " & _"INNER JOIN [Feuil1$] ON Feuil2$.numeroPeriode2 = Feuil1$.numeroPeriode1"Set Requete = New ADODB.RecordsetSet Requete = Source.Execute(xSQL)If Requete.EOF ThenmsgBox "Il n'y a pas de doublons"Else'msgBox "il y a des doublons ."Range("A1").copyFromRecordset RequeteEnd IfRequete.CloseSource.CloseEnd Sub - Vérifier si la table "Table1" existe dans une base Access
Sub verifierSiTableAccessExiste()Dim Cat As ADOX.CatalogDim Table As ADOX.TableDim Fichier As String, xConnect As StringDim Cn As ADODB.ConnectionFichier = ("C:\Documents and Settings\michel\dossier\dataBase.mdb")Set Cn = New ConnectionWith Cn.Provider = "Microsoft.Jet.OLEDB.4.0".Open FichierEnd WithSet Cat = createObject("ADOX.Catalog")Set Cat.activeConnection = CnOn Error Resume NextSet Table = Cat.tables("Table1")If Table Is Nothing ThenmsgBox "La table n'existe pas ."ElsemsgBox "La table existe ."End IfSet Cn = NothingSet Cat = NothingEnd SubUne autre méthode , en bouclant sur toutes les tables - Compter le nombre d'enregistrements total dans la table "Table1"
Sub nombreEnregistrementsTotal_dansTable()Dim Cn As New ADODB.ConnectionDim Rs As ADODB.RecordsetDim maBase As StringmaBase = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & maBaseSet Rs = Cn.Execute("SELECT COUNT(*)FROM Table1")msgBox Rs(0)Cn.CloseSet Cn = NothingEnd Sub - Compter le nombre d'enregistrements conditionnel
(Exemple : le nombre d'enregistrements dont le champ "nombreHeures" est superieur ou égal à 7)Set Rs = Cn.Execute("SELECT COUNT(*)FROM Table1 WHERE nombreHeures >= " & 7) - Importer un fichier texte par la methode ADO
Sub importFichierTexte_ADO()Dim Rc As ADODB.RecordsetDim cn As String, Chemin As String, Fichier As StringDim i As LongChemin = "C:\Documents and Settings\michel\dossier\general\excel"Fichier = "monFichier.txt"cn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _"Dbq=" & Chemin & ";Extensions=asc,csv,tab,txt"Set Rc = New ADODB.RecordsetRc.Open Source:="SELECT * FROM " & Fichier, activeConnection:=cnIf Not Rc.EOF ThenFor i = 0 To Rc.Fields.Count - 1 'recuperation entetesCells(1, 1).Offset(0, i) = Rc.Fields(i).NameNextRange("A2").copyFromRecordset RcEnd IfRc.CloseEnd Sub - Comment piloter un fichier DBase (.dbf) depuis Excel
Sub piloterDBase_ajoutEnregistrement()'necessite d'activer la reference Microsoft ActiveX Data Objects x.x LibraryDim Cn As ADODB.ConnectionDim Rs As ADODB.RecordsetDim Chemin As String, Cible As String, laBase As StringChemin = "C:\Documents and Settings\michel\dossier"laBase = "maBase.dbf"Set Cn = New ADODB.ConnectionCn.Open _"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & _Chemin & ";"Cible = "SELECT * FROM " & laBase & ";"Set Rs = New RecordsetRs.Open Cible, Cn, adOpenKeyset, adLockOptimisticWith Rs.addNew.Fields(0) = "Texte".Fields(1) = CDate("2005-07-04").Fields(2) = 10001.Fields(3) = "un commentaire".UpdateEnd WithRs.CloseCn.CloseEnd Sub - Importer dans Excel les données contenues dans un champ d'une base type Dbase
Lien supprimé - Effectuer une jointure entre 2 fichiers DBase (.dbf)
- Créer un fichier DBF par macro .
Dim Cn As ADODB.ConnectionDim Fichier As String, Chemin As StringChemin = "C:\Documents and Settings\michel\dossier\general\excel"Fichier = "maBase"Set Cn = New ADODB.ConnectionCn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & _Chemin & ";"Cn.Execute "Create Table " & Fichier & " (champTexte TEXT(30), champNum INTEGER)"Cn.Execute "Insert Into " & Fichier & " (champTexte, champNum) Values ('Donnee01', 1004599)"Cn.Execute "Insert Into " & Fichier & " (champTexte, champNum) Values ('Donnee02', 435455)"Cn.CloseSet Cn = Nothing - Gérer les apostrophes contenus dans les enregistrements lors d'une requete
'l'apostrophe du mot à rechercher dans la table doit etre doublédonneeCible = "'Saint Jean D''Angely'"Fichier = "C:\dataBase.mdb"Set Conn = New ADODB.ConnectionConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Fichier & ";"rSQL = "SELECT codePostal FROM Table1 WHERE nomVille="Set rsT = New ADODB.RecordsetWith rsT.activeConnection = Conn.Open rSQL & donneeCible, , adOpenStatic, adLockOptimistic, adCmdTextEnd With - Gérer des noms de tables et des noms de champs qui contiennent des espaces
Pour que les requetes fonctionnent , il faut encadrer les noms par des crochetsLien suppriméRemarque:Il est tout de meme préférable d'utiliser des noms de champs sans espace, quand cela est possible - Vérifier l'état de la connection à une base de données
- Mettre à jour un enregistrement dans une table Access
Sub miseAJour_Enregistrement_tableAccess()Dim Cn As ADODB.ConnectionDim Requete As StringSet Cn = New ADODB.ConnectionCn.Provider = "Microsoft.Jet.Oledb.4.0"Cn.connectionString = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"Cn.OpenRequete = "UPDATE Table1 Set nombrePieces = laValeur, nomUser = 'leNom', laDate = #10/11/2005# WHERE numSerie = 8"Cn.Execute RequeteCn.CloseEnd Sub - Copier les données du champ "Origine" vers le champ "Destination" , dans la Table1 d'une base Access
Sub modificationChampTableAccess()Dim Cn As ADODB.ConnectionDim Requete As StringSet Cn = New ADODB.ConnectionCn.Provider = "Microsoft.Jet.Oledb.4.0"Cn.connectionString = "C:\Documents and Settings\michel\dossier\dataBase.mdb"Cn.OpenRequete = "UPDATE Table1 SET Table1.Origine = Table1.Destination"Cn.Execute RequeteCn.CloseEnd Sub - Transposer les données des Champ1 et Champ2 , dans la Table1 d'une base Access
Sub transposerDonnees_deuxChamps_tableAccess()Dim Cn As ADODB.ConnectionDim Requete As StringSet Cn = New ADODB.ConnectionCn.Provider = "Microsoft.Jet.Oledb.4.0"Cn.connectionString = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"Cn.OpenRequete = "UPDATE Table1 SET Table1.Champ1 = Table1.Champ2 , " & _"Table1.Champ2 = Table1.Champ1"Cn.Execute RequeteCn.CloseEnd Sub - Informations sur l'utilisation de la propriété recordCount
- Récupérer uniquement les 10 premiers enregistrements
Set rsT = New ADODB.RecordsetWith rsT.activeConnection = Cn.Open "SELECT TOP 10 * FROM maTable"End With - Récupérer uniquement 50 % des premiers enregistrements
Set rsT = New ADODB.RecordsetWith rsT.activeConnection = Cn.Open "SELECT TOP 50 PERCENT * FROM maTable"End With - Gérer les retours à la lignes dans les enregistrements lors des imports / exports entre Excel et Access
- Effectuer une jointure entre 3 tables d'une base Access
Lien supprimé - Parametrer les colonnes d'une table Access pour qu'elles acceptent des enregistrements Vides / Null
Dim tbl As ADOX.TableCat.activeConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=C:\dataBase.mdb;"Set Tbl = Cat.Tables("nomTable")For j = 0 To Tbl.Columns.Count - 1Tbl.Columns(j).Attributes = adColNullableNext jUne démo de @+Thierry pour exporter une plage de données contenant des cellules vides d'Excel vers une Base AccessLa procedure montre aussi comment parametrer les types de données ( Numerique ) de certains champs , lors de la creation d'une nouvelle tableLien supprimé - Se connecter à une base Access protégée par un mot de passe
Dim Conn As New ADODB.ConnectionConst Path As String = "C:\maBase.mdb"With Conn.Provider = "Microsoft.JET.OLEDB.4.0".Properties("Jet OLEDBatabase Password") = "monMotDePasse".Open PathEnd With - Afficher la date de la derniere modification effectuée dans une table Access
Sub dateDerniereModificationTable_baseAccess()Dim Cat As ADOX.CatalogDim laTable As ADOX.TableDim Fichier As StringDim Cn As ADODB.ConnectionSet Cn = New ConnectionWith Cn.Provider = "Microsoft.Jet.OLEDB.4.0".Open FichierEnd WithSet Cat = createObject("ADOX.Catalog")Set Cat.activeConnection = CnSet laTable = Cat.Tables("nomTable")msgBox laTable.dateModifiedCn.CloseSet Cn = NothingSet Cat = NothingEnd Sub - Transférer des enregistrements entre 2 classeurs fermés
"Classeur1_Fermé.xls" est le classeur source . toutes les données de la Feuil1 sont récuperees dans la requeteClasseur2_Fermé.xls" est le classeur destination . les données recuperees sont ajoutées a la suite des enregistrements existantsLe classeur contenant la macro et les 2 classeurs fermés sont dans le meme repertoireSub tranfertEntreClasseursFermes()Dim Cn As New ADODB.ConnectionDim oProdRS As New ADODB.Recordset, oRS As ADODB.RecordsetDim oConn As ADODB.ConnectionDim j As Integer'------------------------------------------------------------------' "Classeur1_Fermé.xls" est le classeur sourceCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=" & thisWorkbook.Path & "\Classeur1_Fermé.xls;" & _"Extended Properties=""Excel 8.0;HDR=NO;"""'les donnees sources sont dans la Feuil1 du classeur "Classeur1_Fermé.xls"oProdRS.Open "SELECT * FROM [Feuil1$]", Cn, adOpenStatic'------------------------------------------------------------------' "Classeur2_Fermé.xls" est le classeur destinationSet oConn = New ADODB.ConnectionoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=" & thisWorkbook.Path & "\Classeur2_Fermé.xls;" & _"Extended Properties=""Excel 8.0;HDR=NO;"""'les donnees sont à placer dans la Feuil1 du classeur "Classeur2_Fermé.xls"Set oRS = New ADODB.RecordsetoRS.Open "Select * from [Feuil1$]", oConn, adOpenKeyset, adLockOptimistic'------------------------------------------------------------------'transfert des donnéesDo While Not (oProdRS.EOF)oRS.addNewFor j = 0 To oRS.Fields.Count - 1oRS.Fields(j) = oProdRS.Fields(j).ValueNext joRS.UpdateoProdRS.moveNextLoopoProdRS.CloseCn.CloseoRS.CloseoConn.CloseEnd SubLien supprimé - Exporter une table Access au format XML
Vous trouverez un exemple qui réimporte le fichier XML vers la base Access dans la WikiPage9 : MichelXldPageNeufSub exportTableAccess_XML()Dim Cn As ADODB.ConnectionDim Rs As ADODB.RecordsetDim Fichier As StringFichier = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"Set Cn = New ADODB.ConnectionCn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _Fichier & ";"Set Rs = New ADODB.RecordsetWith Rs.activeConnection = Cn.Open "SELECT * FROM Table1", , adOpenStatic, adLockOptimistic, adCmdTextEnd WithRs.Save "C:\exportTableTemporaire.xml", adPersistXML'autre possibilité'Rs.Save "C:\tableTemporaire.xls", adPersistXMLCn.CloseEnd Sub - Concaténer les données de 2 colonnes ( Champ1 et Champ2 ) vers une 3eme (Champ3) , dans un classeur fermé
Private Sub concatenationDeuxColonnesClasseurFerme()Dim Conn As ADODB.ConnectionDim Fichier As String, Direction As String, rSQL As StringDirection = thisWorkbook.PathFichier = "monClasseur.xls"Application.screenUpdating = FalseSet Conn = New ADODB.ConnectionWith Conn.Provider = "Microsoft.Jet.OLEDB.4.0".connectionString = "Data Source=" & Direction & "\" & Fichier & _";Extended Properties=Excel 8.0;".OpenEnd WithrSQL = "UPDATE [Feuil1$] SET Champ3 = Champ1 + Champ2"Conn.Execute RsqlConn.CloseApplication.screenUpdating = TrueEnd Sub - Utiliser l'opérateur LIKE et les signes génériques .
Lien supprimé - Créer une page HTML qui affiche le résultat d'une requete ADO
Ce lien n'existe plus - Créer une table liée
Sub creerTableLiee()Dim Cat As ADOX.CatalogDim Tbl As ADOX.TableDim Cn As ADODB.ConnectionDim Fichier As String'nom de la 1ere base contenant la table à lierFichier = "C:\Documents and Settings\michel\dossier\dataBase.mdb"Set Cn = New ADODB.ConnectionCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FichierSet Cat = New ADOX.CatalogSet Tbl = New ADOX.TableCat.activeConnection = CnTbl.Name = "tableLiee" 'nom de la table à lierSet Tbl.parentCatalog = Cat'Parametres de la 2eme base et la table qui vont etre liéesTbl.Properties("Jet OLEDB:Link Datasource") = "C:\dataBase_V02.mdb"Tbl.Properties("Jet OLEDB:Remote Table Name") = "Table1"Tbl.Properties("Jet OLEDB:Create Link") = TrueCat.Tables.Append TblSet Cat = NothingCn.CloseSet Cn = NothingEnd Sub - Compiler et trier les données de plusieurs fichier textes .txt
Ce lien n'existe plus - Executer une macro Access depuis Excel: une solution donnée par Chris
- Ajouter une feuille dans un classeur fermé
- Transférer plusieurs classeurs vers une base Access (Voir les messages du 31/10/2006 à 17h35 et 19h07)
Toutes vos idees sont les bienvenues .
Michel , Mise à jour le 25 Novembre 2006
Dernière modification par un modérateur: