|
XLDnaute Barbatruc
Date d'inscription: février 2005
Messages: 3 829
|
[REF] Wiki 6 de MichelXld (Doublons, Tris, Filtres, Variables, Fichiers fermés, Acces
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) .
Lien vers la wiki page 1 :Les feuilles , Les graphiques , Les images , Les propriétés des classeurs , Les sauvegardes , Les formes automatiques , Aleatoire , Les barres d'outils et les barres de menus , Les boites de dialogues intégrées , Les classeurs . Lien vers la wiki page 2 : Les userforms : Les Checkbox , Les Labels , Les combobox , Les Commandbutton , Les Listbox ,Les Multipages , Les Frames , Les Textbox , Les imagesList , Les Treeview , Les Listview , Les Images , Les Webbrowser , Les calendriers , Les progressbar , Les Spreadsheet , Les Chartspaces , Les commonDialog , Les MSFlexGrid. Lien vers la wiki page 3 :Piloter d'autres applications depuis Excel , Piloter ( Word , Outlook , Power Point ) , Les fichiers texte Lien vers la wiki page 4 : Les fonctions mathématiques et trigonométriques , Les impressions , Les temporisations , Les fonctions , Les evenements , Excel , Les cellules , Copier & Coller , Les dates et les calendriers , Les spécificités Macintosh, Générer des fichiers Flash , Open Office Lien vers la wiki page 5 : Les formules Excel , Les audits de formules , Les répertoires et les fichiers . Lien vers la wiki page 6 : Les doublons , Les tris et les filtres , Les variables , Piloter les fichiers fermés (Excel , Access ,les fichiers DBF) . Lien vers la wiki page 7 : Les commentaires , La gestion des erreurs , L'aide en ligne Excel , Les recherches dans un classeur, Les tableaux , Les pages html et internet , Windows Media Player , Le PC et le systême d'exploitation ,Piloter Flash , les types de boucles. Lien vers la wiki page 8 : Piloter MSN Messenger et Windows Messenger , Les objets dans le feuille , Les liens hypertextes , Les formats , Visual basic editor , Les chaines de caractères , Les modules de classe. Lien vers la wiki page 9 : Les mises en forme conditionnelles , Les Tableaux et graphiques Croisés Dynamiques , Gérer les fichiers XML depuis Excel , Piloter Open Office depuis Excel. Lien vers la wiki page 10 : Le Publipostage Word / Excel. Lien vers la wiki page 11 : Utiliser la librairie Windows Image Acquisition Automation Library v2.0 depuis Excel.
Les doublons- Deux classeurs démo regroupant plusieurs exemples
Les classeurs macrosDoublons dans la zone de telechargement XLD 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 Zon Option Explicit 'adapté pour XL97, V2 application.transopose remplacé 'Zon le 23.04.2004 Option Base 1 Const 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 listBox1 Select Case .listIndex Case -1: Exit Sub Case 0: Insertion (2): Princ "C", 3 Case 1: Insertion (1): Princ "C", 3 Case Else: Princ Right(.List(.listIndex), 1), .listIndex + 1 End Select Unload Me End With End Sub Private Sub userForm_Initialize() Me.Caption = Titre With Label1 .Caption = Lab .autoSize = True End With With listBox1 .List = Array("Colonne A", "Colonne B", "Colonne C", _ Colonne D, "Colonne E", "Colonne F") End With End Sub Sub Princ(K As String, T As Byte) Dim L1&, L2&, C&, Plage As Range On Error Resume Next Application.screenUpdating = False L1 = Range(K & "1").End(xlDown).Row + 1 L2 = Range(K & 65536).End(xlUp).Row C = Range("IV" & L1).End(xlToLeft).Column Set Plage = Range(Range("A" & L1), Cells(L2, C)) Plage.Columns("A:B").clearContents Tri Plage, T Tablo = transposeGrandTab(Plage.Columns(T).Value) Doublons Plage.Columns(1) = transposeGrandTab(Tablo2) Plage.Columns(2) = transposeGrandTab(Tablo3) On Error goTo 0 End Sub Private Function Tri(Plage As Range, C As Byte) With Plage .Sort .Cells(C), xlAscending, , , , , , xlNo End With End Function Private Sub Doublons() Dim I&, J&, K&, L&, Item reDim Tablo2(UBound(Tablo, 2)): reDim Tablo3(UBound(Tablo, 2)) J = 1: L = 1: K = 1 For I = LBound(Tablo, 2) To UBound(Tablo, 2) If Item = Tablo(1, I) Then J = J + 1: Tablo3(K) = J: K = K + 1 Else Item = Tablo(1, I): J = 1 Tablo2(K) = L: Tablo3(K) = J L = L + 1: K = K + 1 End If Next I End Sub Private Function Insertion(Nb As Byte) Dim I As Byte For I = 1 To Nb Columns(1).Insert Next I End Function Function transposeGrandTab(T) 'Zon 'Application.transpose est limité à 5000 et qques jusqu'à XL2002 Dim Temp, I&, J&, Z As Byte, Nb As Byte On Error Resume Next Do Nb = Nb + 1 Z = UBound(T, Nb + 1) Loop Until Err If Nb = 1 Then reDim Temp(UBound(T), 1 To 1) For I = LBound(T) To UBound(T) Temp(I, 1) = T(I) Next I Else reDim 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 J Next I End If transposeGrandTab = Temp End Function
- Un questionnaire de satisfaction clients avec gestion des statistques
Le lien sur le forum XLD Le fichier zippé
- Regrouper les Chiffres d'affaire par ville
Le lien sur le forum XLD Le fichier zippé
- Une macro complémentaire de myDearFriend pour supprimer les doublons dans une feuille
Le lien sur le forum XLD Le fichier zippé
- 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 Range Dim Resultat As String Set Cible = Rows(1).rowDifferences(Comparison:=Range("D1")) For Each Cell In Cible Resultat = Resultat & Cell & vbLf Next Cell msgBox Resultat End Sub Utiliser 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ées menu Données Validation onglet "Options" Sélectionnes "Personnalisé" dans la liste de choix Saisis la formule est : =NB.SI($A$1:$A$10;A1)<2 Dans l'onglet "Alerte d'erreur" tu peux ensuite personnaliser le message d'alerte cliques 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 String Dim i As Byte Dim Un As New Collection 'découpage en fonction du séparateur "," Tableau = Split(Range("A1"), ",") 'filtre doublons On Error Resume Next For i = 0 To UBound(Tableau) Un.Add Tableau(i), Tableau(i) Next i On Error goTo 0 'réinsertion des donnees dans la cellule B1 sans doublons For i = 1 To Un.Count Range("B1") = Range("B1") & Un(i) & "," Next i Range("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 Then If Application.worksheetFunction.countIf(Range("A1:A5 000"), Target.Value) > 1 Then msgBox "ce nom existe déja" End If End Sub
Les tris et les filtres- Des information générales sur les filtres automatiques
AutoFilter
- Extraire les donnees d'une cellule et les trier par ordre alphabetique
Le lien sur le forum XLD
- Compter le nombre de lignes visibles aprés l'application d'un filtre automatique
Le lien sur le forum XLD
- Afficher tous les critères des filtres actifs , dans une feuille
Le lien sur le forum XLD
- 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(xlCellTypeVis ible).Address End 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 automatique Range("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 filtre Range("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 Lisbox ATTENTION : 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
Le lien sur le forum XLD Le fichier zippé
Les variables
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 uniques codé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 à 6 Long : 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 pour lesquels 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écision sous 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écision compris 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 forme d'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ères et 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 entre 0 et environ 63 Ko de caractères et des chaînes dynamiques dont la longueur est comprise entre 0 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ées sous 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 Object Set 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 :
Le lien sur le forum XLD 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 peut contenir la même plage de données que le type Decimal, ou d'une taille de stockage de caractè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ées n'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 String Dans 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 dans la 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 macros Menu Outils Options Dans 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 appelante L'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 String Xld= "Coucou ! " Msgbox Xld End Sub Pour que la variable soit utilisable dans toutes macros du module , celle-ci doit etre déclarée avant le Sub : Dim Xld As String Sub Test() … End Sub Pour 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 String Sub 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
Le lien sur le forum XLD
- 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 , Nothing Si 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 Currency Dim arrayVar (1 To 5) As Integer nullVar = 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 Textbox
Dim Ctrl As Control For Each Ctrl In userform1.Controls If typeName(Ctrl) = "textBox" Then '......... End If Next Ctrl 'Vérifier si la variable Nb est de type Integer
If typeName(Nb) = "integer" Then 'Vérifier le type de donnée contenu dans la cellule A1 msgBox 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 - Description vbEmpty - 0 - Empty (non initialisée) vbNull - 1 - Null (aucune donnée valide) vbInteger - 2 - Entier vbLong - 3 - Entier long vbSingle - 4 - Nombre à virgule flottante en simple précision vbDouble - 5 - Nombre à virgule flottante en double précision vbCurrency - 6 - Valeur monétaire vbDate - 7 - Valeur de date vbString - 8 - Chaîne vbObject - 9 - Objet vbError - 10 - Valeur d'erreur vbBoolean - 11 - Valeur booléenne vbVariant - 12 - Variant (utilisée seulement avec des tableaux de variants) vbDataObject - 13 - Objet d'accès aux données vbDecimal - 14 - Valeur décimale vbByte - 17 - Octet vbUserDefinedType - 36 - Variant contenant des types définis par l'utilisateur vbArray - 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 Inputbox Dim Reponse Reponse = Application.inputBox("Saisissez vos données") If varType(Reponse) = vbBoolean Then msgBox " opération annulée" 'Vérifier si la cellule A1 est vide If 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 actif Dim Sh As Object For Each Sh In activeWorkbook.Sheets If typeOf Sh Is Chart Then msgBox Sh.Name Next 'Vérifier si une cellule est sélectionnée dans la feuille If typeOf Selection Is Range Then 'Boucler sur les checkBoxes d'un Userform et leur attribuer la valeur Vrai Dim Ctrl As Control For Each Ctrl In Me.Controls If typeOf Ctrl Is MSForms.checkBox Then Ctrl.Value = True Next
- 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 module Type Voiture Couleur As String Cylindree As Long anneeAchat (1 to 5) As Date End Type Sub testVariable() Dim X As Voiture 'Déclaration de la variable personnalisée X.Couleur = "Rouge" 'Ecriture dans les variables X.Cylindree = 2000 msgBox "Cette voiture " & X.Couleur & " a une cylindrée de " & X.Cylindree & " cc" 'lecture variable End Sub Il est possible de déclarer un tableau multi-dimensionnel : Dim Tableau(1 to 5, 1 to 10) As Voiture et 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 Library Private Sub commandButton1_Click() 'utilisation de la methode Run depuis Excel , 'pour déclencher une macro Word contenant un paramètre Dim wordApp As Word.Application Dim wordDoc As Word.Document Dim monParametreVB As String Set wordApp = createObject("Word.Application") wordApp.Visible = True Set wordDoc = wordApp.Documents.Open("C:\monDocument.doc") 'ouverture doc Word monParametreVB = "azerty" 'déclenchement de la macro Word 'Remarque : la macro Word doit etre placée au niveau de thisDocument wordDoc.laMacro monParametreVB End Sub 'La procédure dans Word, à placer au niveau de thisDocument Option Explicit Sub laMacro(maVariableWord As String) thisDocument.Range.Text = maVariableWord End Sub
Utiliser la saisie semi automatique des variables (une astuce donnée par Denis)
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
Le lien sur le forum XLD
- Un autre exemple
Le lien sur le forum XLD Le fichier zippé
- Recherche dans des classeurs fermés , avec choix de l'onglet et affichage des résultats multicolonnes dans un USF
Le lien sur le forum XLD
- Piloter une base Access en utilisant l'ActiveX Data Objects (ADO)
Lister les tables de la base Access Lister les champs d'une table Créer une nouvelle table dans la base Access Afficher les données d'une table , dans une listBox Effectuer une jointure entre 2 tables Afficher la liste des métaDonnées de la base Acces Le lien sur le forum XLD Le fichier zippé La base Access ( à placer dans le meme repertoire que le fichier zippé
- Additionner les tableaux de plusieurs classeurs fermés
Le fichier zippé
- Vérifier si une valeur existe dans le champ "numeroRemorque" , d'une table Access "Remorque"
Sub controleValeurTable() Dim Conn As ADODB.Connection Dim rsT As ADODB.Recordset Dim Fichier As String, rSQL As String Dim Valeur As Integer Fichier = "C:\maBase.mdb" Valeur = inputBox("Saisir le numero à rechercher dans la table") Set Conn = New ADODB.Connection Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Fichier & ";" rSQL = "SELECT Remorque.numeroRemorque" & _ " FROM Remorque" & _ " WHERE numeroRemorque=" Set rsT = New ADODB.Recordset With rsT .activeConnection = Conn .Open rSQL & Valeur, , adOpenStatic, adLockOptimistic, adCmdText End With If rsT.EOF Then msgBox "Le numéro " & Valeur & " n'existe pas dans la table Remorque . " Else msgBox "Le numéro " & Valeur & " existe la table Remorque . " End If rsT.Close Conn.Close End Sub
- Extraire la valeur Maxi ( ou Mini) dans le champ "nbHeuresAstreinte" de la Table2 , d'une base Access
Sub extraireValeurMaxTableAccess() Dim cn As ADODB.Connection Dim Rs As ADODB.Recordset Dim Fichier As String Fichier = thisWorkbook.Path & "\maBase_V01.mdb" Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Fichier & ";" Set Rs = New ADODB.Recordset With Rs .activeConnection = cn .Open "SELECT max(nbHeuresAstreinte) FROM Table2", , adOpenStatic, adLockOptimistic, adCmdText '.Open "SELECT min(nbHeuresAstreinte) FROM Table2", , adOpenStatic, adLockOptimistic, adCmdText End With msgBox Rs(0) Rs.Close Set Rs = Nothing cn.Close Set cn = Nothing End 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.Connection Dim rsT As ADODB.Recordset Dim Fichier As String, rSQL As String Fichier = thisWorkbook.Path & "\maBase_V01.mdb" Set Conn = New ADODB.Connection Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Fichier & ";" rSQL = "SELECT DISTINCT Matricule" & _ "FROM Table2 " Set rsT = New ADODB.Recordset With rsT .activeConnection = Conn .Open rSQL, , adOpenStatic, adLockOptimistic, adCmdText End With If rsT.EOF Then rsT.Close Conn.Close Exit Sub End If Feuil1.Range("A1").copyFromRecordset rsT rsT.Close Conn.Close End Sub
- Exporter la Feuil1 d'un classeur fermé , dans un fichier texte
Sub excelVersFichierTexte() Dim Rs As New ADODB.Recordset Dim Fichier As String, Feuille As String Dim xConnect As String, xSql As String Fichier = "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.Recordset Rs.Open xSql, xConnect, adOpenForwardOnly, adLockReadOnly, adCmdText Open "C:\Documents and Settings\michel\essai.txt" For Output As #1 Do Until Rs.EOF Print #1, Rs.getString(, 600, ";", vbCrLf, ""); 'exemple avec séparateur ";" (point virgule) Loop Close #1 End Sub Les 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 possible Pas d'espace Pas d'accent Pas 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.Connection Dim rsT As ADODB.Recordset Dim Fichier As String, rSQL As String Fichier = "C:\maBase_V01.mdb" Set Conn = New ADODB.Connection Conn.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.Recordset With rsT .activeConnection = Conn .Open rSQL, , adOpenStatic, adLockOptimistic, adCmdText End With If rsT.EOF Then rsT.Close Conn.Close Exit Sub End If Feuil1.Range("A1").copyFromRecordset rsT rsT.Close Conn.Close End Sub
- Récupérer les informations d'un classeur corrompu
Le lien sur le forum XLD Une autre possibilité sans macro ( à partir d'Excel2000 ?) menu Fichier Ouvrir Sélectionnes le classeur qui pose probleme dans la boite de dialogue cliques sur la droite du bouton "Ouvrir" , en bas dans la boite de dialogue Sélectionnes l'option "Ouvrir et réparer" Tu peux aussi essayer d'ouvrir ton classeur avec Open Office Il est parfois possible d'ouvrir les classeurs corrompus grace à cette suite bureautique D'autres informations issues de l'aide en ligne Microsoft Le lien sur le site Microsoft
- Récupérer les macros dans un classeur corrompu
Le lien sur le forum XLD
- Créer une nouvelle base Access
Sub creerNouvelleBaseDeDonnees() 'activer la reference microsoft ADO ext x.x for DLL and Security Dim Cat As ADOX.Catalog Set Cat = createObject("ADOX.Catalog") Cat.Create _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\maNouvelleBase.mdb" End Sub Deux 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é Le lien sur le forum XLD Le fichier zippé Le lien sur le forum XLD Le fichier zippé Le fichier zippé
- Remplacer les constantes par leur valeur lors de la création d'une table Acces , en VBA
Le lien sur le forum XLD
- Ecrire dans un classeur fermé , en insérant les données à la suite des valeurs existantes
Le lien sur le forum XLD Le fichier zippé
- 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.Connection Dim rsT As ADODB.Recordset Dim Fichier As String, rSQL As String Fichier = thisWorkbook.Path & "\maBase_V01.mdb" Set Conn = New ADODB.Connection Conn.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.Recordset With rsT .activeConnection = Conn .Open rSQL, , adOpenStatic, adLockOptimistic, adCmdText End With msgBox rsT.Fields(0).Value rsT.Close Conn.Close End Sub
- Compacter une base Access
Sub compacterBaseAccess() 'necessite d'activer la reference Microsoft Jet and Replication Objects 2.6 Library Dim bdCompact As String, Fichier As String Dim Source As String, Destination As String Dim jtEng As JRO.jetEngine Fichier = "C:\Documents and Settings\michel\maBase_V01.mdb" 'la base existante bdCompact = "C:\Documents and Settings\michel\maBaseCompactee.mdb" 'la nouvelle base créée Source = "Data Source=" & Fichier Destination = "Data Source=" & bdCompact & ";" & "Jet OLEDB:Encrypt Database=True" Set jtEng = New JRO.jetEngine jtEng.compactDatabase Source, Destination Set jtEng = Nothing End 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.Connection Dim Cd As ADODB.Command Dim Rst As ADODB.Recordset Dim Fichier As String Fichier = "C:\dossier\monClasseur.xls" 'adapter le chemin des fichiers fermés Set Cn = New ADODB.Connection Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Fichier & ";" & _ "Extended Properties=""Excel 8.0;HDR=No;"";" Set Cd = New ADODB.Command Cd.activeConnection = Cn Cd.commandText = "SELECT * from `Feuil1$A1:A1`" Set Rst = New ADODB.Recordset Rst.Open Cd, , adOpenKeyset, adLockOptimistic Rst(0).Value = " donnée test" Rst.Update Cn.Close Set Cn = Nothing Set Cd = Nothing Set Rst = Nothing End Sub
- Lire une cellule spécifique d'un classeur fermé
Le lien sur Internet
- 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 Library Dim Rs As ADODB.Recordset Dim Cn As String Dim Cible As String Dim Fichier As String Fichier = "C:\Fichier.xls" Cn = "DRIVER={Microsoft Excel Driver (*.xls)};" & _ "readOnly=1;DBQ=" & Fichier Cible = "SELECT * FROM [Feuil1$];" Set Rs = New ADODB.Recordset Rs.Open Cible, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText 'les données à récuperer sont dans la colonne A If Not Rs.EOF Then comboBox1.addItem Rs.Fields(0).Name 'entete Do While Not Rs.EOF comboBox1.addItem Rs.Fields(0).Value Rs.moveNext Loop End If Rs.Close Set Rs = Nothing End 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 Library Dim Conn As New ADODB.Connection Dim rsT As New ADODB.Recordset Dim maTable As String maTable = "Table1" With Conn .Provider = "Microsoft.JET.OLEDB.4.0" .Open "C:\Documents and Settings\michel\Excel\maBase_V01.mdb" End With With rsT .activeConnection = Conn .Open maTable, lockType:=adLockOptimistic End With With rsT .addNew .Fields("Nom").Value = Range("A1") .Fields("prixUnit").Value = Range("B1") .Fields("Matricule").Value = Range("C1") .Update End With rsT.Close Conn.Close End Sub
- Suppression conditionnelle d'enregistrements , dans une table Access
Sub suppressionEnregistrementTable() Dim Cn As ADODB.Connection Dim Rst As ADODB.Recordset Dim Requete As String, maTable As String, Donnee As String Dim Valeur As Integer Set Cn = New ADODB.Connection Set Rst = New ADODB.Recordset Cn.Provider = "Microsoft.Jet.Oledb.4.0" Cn.connectionString = "C:\maBase_V02.mdb" Cn.Open maTable = "lesPoints" ' '****pour des valeurs numeriques **** 'suppression des enregistrements si le champ "nbPoints" est égal à 5 , dans la table "lesPoints" ' Valeur = 5 Requete = "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 Requete Cn.Close End Sub
- Importer les donnees de tous les onglets d'un classeur fermé , vers la feuille active
Le lien sur internet
- 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 discontinues Importer 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ées Transférer une Table Access dans un classeur fermé Le fichier zippé
- 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.Recordset Dim oConn As ADODB.Connection Dim maFeuille As String, prepaTable As String Dim 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ées prepaTable = prepaTable & "Colonne" & i & " Memo ," 'adapter les types de données Next i prepaTable = Left(prepaTable, Len(prepaTable) - 1) 'creation nouvelle Feuille Excel oConn.Execute "create table " & maFeuille & "(" & prepaTable & ")" Set oRS = New ADODB.Recordset oRS.Open "Select * from " & maFeuille, oConn, adOpenKeyset, adLockOptimistic For j = 1 To 40 'nombre de lignes à transferer oRS.addNew For i = 1 To 10 'nombre de colonnes à transférer oRS.Fields(i - 1) = activeSheet.Cells(j, i) Next i oRS.Update Next j oRS.Close oConn.Close End Sub
- Importer les feuilles complètes de plusieurs classeurs fermés
Le fichier zippé
- 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.Connection Dim rsT As ADODB.Recordset Dim fld As ADODB.Field Set Conn = New ADODB.Connection With 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 With Set rsT = New ADODB.Recordset 'table nommée "maTable" rsT.Open "maTable", Conn, adOpenKeyset, adLockOptimistic With 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" .Update End With Conn.Close End Sub
- Requète dans un classeur fermé : Additionner les valeurs d'un champ avec critères filtres
Le fichier zippé
- Ajouter une colonne dans une table Access existante
Sub ajoutColonne_tableAccessExistante() Dim Cnn As New ADODB.Connection Dim Cat As New ADOX.Catalog Dim Rst As New Recordset Dim Reponse As String, Fichier As String On Error goTo Fin Fichier = "C:\maBase_V02.mdb" Cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _ Data Source= ' & Fichier & "';" Set Cat.activeConnection = Cnn Cat.Tables("lesPoints").Columns.Append "nouveauChamp", adWChar, 50 Rst.Open "lesPoints", Cnn, adOpenKeyset, adLockOptimistic, adCmdTable Fin: Cnn.Close Set Rst = Nothing Set Cat = Nothing Set nouvelleColonne = Nothing Set Cnn = Nothing End 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.Connection Dim Cat As New ADOX.Catalog Dim indexTri As New ADOX.Index Dim Rst As New ADODB.Recordset Dim Fichier As String On Error goTo Fin Fichier = "C:\maBase_V03.mdb" Cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _ Data Source=' & Fichier & "';" Set Cat.activeConnection = Cnn With indexTri .Columns.Append "Nom" .Columns("Nom").sortOrder = adSortAscending 'tri croissant .Name = "Ascending" '.Columns("Nom").sortOrder = adSortDescending 'pour les tris décroissants '.Name = "Descending" .indexNulls = adIndexNullsAllow End With 'ajout d'un index pour la table "lesPoints" 'attention : renvoie une erreur si un index existe déjà Cat.Tables("lesPoints").Indexes.Append indexTri Rst.Index = indexTri.Name Rst.Open "lesPoints", Cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect 'import dans la feuille Excel des données triées Feuil1.Range("A1").copyFromRecordset Rst Cat.Tables("lesPoints").Indexes.Delete indexTri.Name 'suppression index Fin: Cnn.Close Set Cat = Nothing Set indexTri = Nothing Set Rst = Nothing Set Cnn = Nothing End Sub
- Lister les utilisateurs connectés à une base Access
Sub listerConnectesBaseAccess() Dim Cible As String Dim Fso As Object Cible = "J:\maBase.ldb" Set Fso = createObject("Scripting.fileSystemObject") If Fso.fileExists(Cible) = False Then Exit Sub Open Cible For Input As #1 Input #1, Cible Close #1 msgBox Cible End Sub Une autre solution Sub listerConnectesBaseAccess_V02() Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}" Dim Cnn As New ADODB.Connection Dim Rst As ADODB.Recordset Dim Fichier As String Fichier = "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.getString Cnn.Close End Sub
- Lister le nom des feuilles d'un classeur fermé
Le lien sur le forum XLD 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 onglets Le fichier zippé
- Récupérer des plages de cellules précises dans plusieurs classeurs fermés ( une démo de @+Thierry )
Le lien sur le forum XLD Le fichier zippé Une adaptation de michel_M pour les versions d'office antérieures Le fichier zippé Une nouvelle version de @+Thierry qui permet d'importer plusieurs plages de cellules Discontinues Le fichier zippé
- Exporter des images dans une base Access puis les réimporter dans un userForm Excel
Le fichier zippé Un autre exemple qui utilise l'objet Stream (uniquement disponible à partir de la version ADO 2.5) Le fichier zippé
- Exporter une image placée dans une Feuille , vers une base Access
Le lien sur internet
- Supprimer les enregistrements d'une table Access , si le champ "cityName" est vide
Sub supprimerEnregistrements_siChampVide() Dim Cn As ADODB.Connection Dim Rst As ADODB.Recordset Dim Requete As String, maTable As String Set Cn = New ADODB.Connection Set Rst = New ADODB.Recordset Cn.Provider = "Microsoft.Jet.Oledb.4.0" Cn.connectionString = "C:\maBase.mdb" Cn.Open maTable = "Table1" 'supprime l'enregistrement si le champ "cityName" est vide Requete = "DELETE * FROM " & maTable & " WHERE cityName IS NULL" Cn.Execute Requete Cn.Close End Sub
- Afficher la version MDAC du poste ( Microsoft Data Access Components )
Sub VersionMDAC() Dim Cn As ADODB.Connection Set Cn = createObject("ADODB.Connection") msgBox "Version MDAC : " & Cn.Version Set Cn = Nothing End 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 cellules Vous 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 classeur Les requetes répétées peuvent entrainer des problemes de mémoire disponible dans Excel http://support.microsoft.com/kb/319998 Il n'est pas possible d'utiliser un classeur protégé par un mot de passe Il n'est pas possible d'utiliser le classeur si la feuille contenant les données est protégée La 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$1265 Nota : 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étique Par 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 . http://support.microsoft.com/kb/189897/ 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 filtre lien sur le forum XLD Le fichier zippé
- Insérer des formules de liaison dans un classeur fermé
Une démo de michel_M Le lien sur le forum XLD Le fichier zippé
- 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 enregistrement si 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.Connection Dim maBase As String maBase = "C:\Documents and Settings\michel\dossier\maBase.mdb" Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & maBase Cn.Execute "DROP TABLE laTable" Cn.Close Set Cn = Nothing End Sub
- Requête filtrée sur des dates , des nombres ou du texte
Le lien sur le forum XLD Le fichier zippé
- 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 Table1 rSQL = "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.Connection Dim rsT As ADODB.Recordset Dim Fichier As String, rSQL As String Fichier = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb " Set Cn = New ADODB.Connection Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Fichier & ";" rSQL = "SELECT * FROM Table1 WHERE nomVille IN ('Vienne','Condrieu','xldCity')" Set rsT = New ADODB.Recordset rsT.Open rSQL, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText If Not rsT.EOF Then Range("A1").copyFromRecordset rsT rsT.Close Cn.Close End 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 Dales Dim Cn As ADODB.Connection Dim xSQL As String Set Cn = New ADODB.Connection Cn.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 xSQL Cn.Close Set Cn = Nothing End 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 numeroPeriode2 L'objectif est de comparer les 2 colonnes , puis de lister les données de la Feuil2 qui apparaissent aussi dans la Feuil1 Sub requeteControleDoublons() Dim Source As ADODB.Connection Dim Requete As ADODB.Recordset Dim Fichier As String, xSQL As String Dim i As Long Fichier = "C:\maBase.xls" Set Source = New ADODB.Connection Source.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.Recordset Set Requete = Source.Execute(xSQL) If Requete.EOF Then msgBox "Il n'y a pas de doublons" Else 'msgBox "il y a des doublons ." Range("A1").copyFromRecordset Requete End If Requete.Close Source.Close End Sub
- Vérifier si la table "Table1" existe dans une base Access
Sub verifierSiTableAccessExiste() Dim Cat As ADOX.Catalog Dim Table As ADOX.Table Dim Fichier As String, xConnect As String Dim Cn As ADODB.Connection Fichier = ("C:\Documents and Settings\michel\dossier\dataBase.mdb") Set Cn = New Connection With Cn .Provider = "Microsoft.Jet.OLEDB.4.0" .Open Fichier End With Set Cat = createObject("ADOX.Catalog") Set Cat.activeConnection = Cn On Error Resume Next Set Table = Cat.tables("Table1") If Table Is Nothing Then msgBox "La table n'existe pas ." Else msgBox "La table existe ." End If Set Cn = Nothing Set Cat = Nothing End Sub Une autre méthode , en bouclant sur toutes les tables Le lien sur internet
- Compter le nombre d'enregistrements total dans la table "Table1"
Sub nombreEnregistrementsTotal_dansTable() Dim Cn As New ADODB.Connection Dim Rs As ADODB.Recordset Dim maBase As String maBase = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb " Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & maBase Set Rs = Cn.Execute("SELECT COUNT(*)FROM Table1") msgBox Rs(0) Cn.Close Set Cn = Nothing End 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.Recordset Dim cn As String, Chemin As String, Fichier As String Dim i As Long Chemin = "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.Recordset Rc.Open Source:="SELECT * FROM " & Fichier, activeConnection:=cn If Not Rc.EOF Then For i = 0 To Rc.Fields.Count - 1 'recuperation entetes Cells(1, 1).Offset(0, i) = Rc.Fields(i).Name Next Range("A2").copyFromRecordset Rc End If Rc.Close End Sub
- Comment piloter un fichier DBase (.dbf) depuis Excel
Sub piloterDBase_ajoutEnregistrement() 'necessite d'activer la reference Microsoft ActiveX Data Objects x.x Library Dim Cn As ADODB.Connection Dim Rs As ADODB.Recordset Dim Chemin As String, Cible As String, laBase As String Chemin = "C:\Documents and Settings\michel\dossier" laBase = "maBase.dbf" Set Cn = New ADODB.Connection Cn.Open _ "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & _ Chemin & ";" Cible = "SELECT * FROM " & laBase & ";" Set Rs = New Recordset Rs.Open Cible, Cn, adOpenKeyset, adLockOptimistic With Rs .addNew .Fields(0) = "Texte" .Fields(1) = CDate("2005-07-04") .Fields(2) = 10001 .Fields(3) = "un commentaire" .Update End With Rs.Close Cn.Close End Sub
- Importer dans Excel les données contenues dans un champ d'une base type Dbase
Le fichier zippé
- Effectuer une jointure entre 2 fichiers DBase (.dbf)
Le lien sur internet
- Créer un fichier DBF par macro .
Dim Cn As ADODB.Connection Dim Fichier As String, Chemin As String Chemin = "C:\Documents and Settings\michel\dossier\general\excel" Fichier = "maBase" Set Cn = New ADODB.Connection Cn.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.Close Set 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.Connection Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Fichier & ";" rSQL = "SELECT codePostal FROM Table1 WHERE nomVille=" Set rsT = New ADODB.Recordset With rsT .activeConnection = Conn .Open rSQL & donneeCible, , adOpenStatic, adLockOptimistic, adCmdText End 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 crochets Le fichier zippé 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
Le lien sur le forum XLD
- Mettre à jour un enregistrement dans une table Access
Sub miseAJour_Enregistrement_tableAccess() Dim Cn As ADODB.Connection Dim Requete As String Set Cn = New ADODB.Connection Cn.Provider = "Microsoft.Jet.Oledb.4.0" Cn.connectionString = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb " Cn.Open Requete = "UPDATE Table1 Set nombrePieces = laValeur, nomUser = 'leNom', laDate = #10/11/2005# WHERE numSerie = 8" Cn.Execute Requete Cn.Close End 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.Connection Dim Requete As String Set Cn = New ADODB.Connection Cn.Provider = "Microsoft.Jet.Oledb.4.0" Cn.connectionString = "C:\Documents and Settings\michel\dossier\dataBase.mdb" Cn.Open Requete = "UPDATE Table1 SET Table1.Origine = Table1.Destination" Cn.Execute Requete Cn.Close End Sub
- Transposer les données des Champ1 et Champ2 , dans la Table1 d'une base Access
Sub transposerDonnees_deuxChamps_tableAccess() Dim Cn As ADODB.Connection Dim Requete As String Set Cn = New ADODB.Connection Cn.Provider = "Microsoft.Jet.Oledb.4.0" Cn.connectionString = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb " Cn.Open Requete = "UPDATE Table1 SET Table1.Champ1 = Table1.Champ2 , " & _ "Table1.Champ2 = Table1.Champ1" Cn.Execute Requete Cn.Close End Sub
- Informations sur l'utilisation de la propriété recordCount
Le lien sur le forum XLD
- Récupérer uniquement les 10 premiers enregistrements
Set rsT = New ADODB.Recordset With rsT .activeConnection = Cn .Open "SELECT TOP 10 * FROM maTable" End With
- Récupérer uniquement 50 % des premiers enregistrements
Set rsT = New ADODB.Recordset With 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
Le lien sur le forum XLD
- Effectuer une jointure entre 3 tables d'une base Access
Le lien sur le forum XLD Le fichier zippé
- Parametrer les colonnes d'une table Access pour qu'elles acceptent des enregistrements Vides / Null
Dim tbl As ADOX.Table Cat.activeConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\dataBase.mdb;" Set Tbl = Cat.Tables("nomTable") For j = 0 To Tbl.Columns.Count - 1 Tbl.Columns(j).Attributes = adColNullable Next j Une démo de @+Thierry pour exporter une plage de données contenant des cellules vides d'Excel vers une Base Access La procedure montre aussi comment parametrer les types de données ( Numerique ) de certains champs , lors de la creation d'une nouvelle table Le fichier zippé
- Se connecter à une base Access protégée par un mot de passe
Dim Conn As New ADODB.Connection Const Path As String = "C:\maBase.mdb" With Conn .Provider = "Microsoft.JET.OLEDB.4.0" .Properties("Jet OLEDB atabase Password") = "monMotDePasse" .Open Path End With
- Afficher la date de la derniere modification effectuée dans une table Access
Sub dateDerniereModificationTable_baseAccess() Dim Cat As ADOX.Catalog Dim laTable As ADOX.Table Dim Fichier As String Dim Cn As ADODB.Connection Set Cn = New Connection With Cn .Provider = "Microsoft.Jet.OLEDB.4.0" .Open Fichier End With Set Cat = createObject("ADOX.Catalog") Set Cat.activeConnection = Cn Set laTable = Cat.Tables("nomTable") msgBox laTable.dateModified Cn.Close Set Cn = Nothing Set Cat = Nothing End 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 requete Classeur2_Fermé.xls" est le classeur destination . les données recuperees sont ajoutées a la suite des enregistrements existants Le classeur contenant la macro et les 2 classeurs fermés sont dans le meme repertoire Sub tranfertEntreClasseursFermes() Dim Cn As New ADODB.Connection Dim oProdRS As New ADODB.Recordset, oRS As ADODB.Recordset Dim oConn As ADODB.Connection Dim j As Integer '------------------------------------------------------------------ ' "Classeur1_Fermé.xls" est le classeur source Cn.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 destination Set oConn = New ADODB.Connection oConn.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.Recordset oRS.Open "Select * from [Feuil1$]", oConn, adOpenKeyset, adLockOptimistic '------------------------------------------------------------------ 'transfert des données Do While Not (oProdRS.EOF) oRS.addNew For j = 0 To oRS.Fields.Count - 1 oRS.Fields(j) = oProdRS.Fields(j).Value Next j oRS.Update oProdRS.moveNext Loop oProdRS.Close Cn.Close oRS.Close oConn.Close End Sub Téléchargez la démo zippée
- Exporter une table Access au format XML
Vous trouverez un exemple qui réimporte le fichier XML vers la base Access dans la WikiPage9 : MichelXldPageNeuf Sub exportTableAccess_XML() Dim Cn As ADODB.Connection Dim Rs As ADODB.Recordset Dim Fichier As String Fichier = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb " Set Cn = New ADODB.Connection Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Fichier & ";" Set Rs = New ADODB.Recordset With Rs .activeConnection = Cn .Open "SELECT * FROM Table1", , adOpenStatic, adLockOptimistic, adCmdText End With Rs.Save "C:\exportTableTemporaire.xml", adPersistXML 'autre possibilité 'Rs.Save "C:\tableTemporaire.xls", adPersistXML Cn.Close End 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.Connection Dim Fichier As String, Direction As String, rSQL As String Direction = thisWorkbook.Path Fichier = "monClasseur.xls" Application.screenUpdating = False Set Conn = New ADODB.Connection With Conn .Provider = "Microsoft.Jet.OLEDB.4.0" .connectionString = "Data Source=" & Direction & "\" & Fichier & _ ";Extended Properties=Excel 8.0;" .Open End With rSQL = "UPDATE [Feuil1$] SET Champ3 = Champ1 + Champ2" Conn.Execute Rsql Conn.Close Application.screenUpdating = True End Sub
- Utiliser l'opérateur LIKE et les signes génériques .
Le lien sur le forum XLD Le fichier zippé
- Créer une page HTML qui affiche le résultat d'une requete ADO
Visualiser la macro
- Créer une table liée
Sub creerTableLiee() Dim Cat As ADOX.Catalog Dim Tbl As ADOX.Table Dim Cn As ADODB.Connection Dim Fichier As String 'nom de la 1ere base contenant la table à lier Fichier = "C:\Documents and Settings\michel\dossier\dataBase.mdb" Set Cn = New ADODB.Connection Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier Set Cat = New ADOX.Catalog Set Tbl = New ADOX.Table Cat.activeConnection = Cn Tbl.Name = "tableLiee" 'nom de la table à lier Set Tbl.parentCatalog = Cat 'Parametres de la 2eme base et la table qui vont etre liées Tbl.Properties("Jet OLEDB:Link Datasource") = "C:\dataBase_V02.mdb" Tbl.Properties("Jet OLEDB:Remote Table Name") = "Table1" Tbl.Properties("Jet OLEDB:Create Link") = True Cat.Tables.Append Tbl Set Cat = Nothing Cn.Close Set Cn = Nothing End Sub
- Compiler et trier les données de plusieurs fichier textes .txt
Visualiser la macro
- Executer une macro Access depuis Excel: une solution donnée par Chris
Le lien sur le forum XLD
Effectuer une importation d'Access de plus de 65536 lignes: une solution donnée par Chris
Le lien sur le forum XLD - Ajouter une feuille dans un classeur fermé
Le lien sur le forum XLD
- Transférer plusieurs classeurs vers une base Access (Voir les messages du 31/10/2006 à 17h35 et 19h07)
|
08/03/2008, 22h36
![[REF] Wiki 6 de MichelXld (Doublons, Tris, Filtres, Variables, Fichiers fermés, Acces](iconimages/questions-les-plus-fr-quentes-faq-et-didacticiels/ref-wiki-6-de-michelxld-doublons-tris-filtres-variables-fichiers-ferm-s-acces_ltr.gif)