Developpez.com - Microsoft Office
X

Choisissez d'abord la catégorieensuite la rubrique :


Ecrire dans un classeur Excel depuis Access

Par Maxence Hubiche (site) (Blog)
 

"Les Outils" sont des morceaux de code que j'ai développé, et qui sont réutilisables. Tant qu'à faire, autant vous en expliquer les arcanes, le cheminement qui m'a amené à fournir ce code :)

I. Introduction
II. Le B.A-BA
I-A. Les explications
I-B. Le code complet
III. On ne pourrait pas la faire un peu plus courte, monsieur Cadbury ?
III-A. Les explications
III-B. Le code complet
IV. Mais, je veux choisir où mettre quelles données !
IV-A. Les explications
IV-A-1. Les classes
IV-A-2. La classe CELLULE
IV-A-3. La classe CELLULES
IV-A-4. La classe MODELE
IV-B. Le code complet
IV-C. Comment utiliser ce code ?
V. Remerciements


I. Introduction

L'objet de ce tutoriel est de fournir des éléments de réflexion sur l'écriture de données issues d'Access dans Excel

Nous allons essayer de dérouler cet article par des exemples simples. Puis nous irons vers des solutions plus avancées, vous proposant même, à la fin, une solution avec modules de classe.


II. Le B.A-BA


I-A. Les explications

Nous commencerons pas un exercice simple : nous avons des données dans une requête (qrySource), et nous aimerions que les données qu'elle renvoie soient présentées, sous forme de tableau, dans un nouveau classeur Excel. A la fin, le classeur sera affiché.

Nous avons donc besoin d'un jeu d'enregistrement pour les données renvoyées par la requête.

Dim rstDatas as DAO.Recordset
Set rstDatas = Currentdb.Openrecordset("qrySource",dbopenSnapshot)
Nous avons aussi besoin d'une instance d'application Excel. Pour cela, nous utiliserons donc une variable objet. Pour permettre l'exécution de cette application quelle que soit la version installée, nous utiliserons ici le LateBinding(1)

Dim objExcel as Object
Set objExcel = CreateObject("Excel.Application")
Ensuite, il sera nécessaire d'avoir un classeur, sinon, nous aurons forcément du mal à écrire !

Dim objClasseur as Object
Set objClasseur = objExcel.WorkBooks.Add()
A partir de là, il sera nécessaire de parcourir tout le Recordset, afin de pouvoir en récupérer les données

Do Until rstDatas.EOF    'Faire la boucle jusqu'à ce qu'on arrive à la fin (EOF=EndOfFile) du Recordset
    'Mettre ici les informations de traitement des données
    rstDatas.MoveNext    'Passer à l'enregistrement suivant
Loop
Quelles sont les informations de traitement ? Rien de plus simple ! Il suffit de parcourir les champs, pour récupérer les données, et les mettre dans les cellules correspondantes

Dim lngCompteur as Long
With objClasseur.Worksheets(1).Range("A65536").End(xlup)
	For lngCompteur = 0 to rstDatas.Fields.Count-1    'les numéros d'Index des champs commencent à 0
		.Offset(1,lngCompteur).Value=rstDatas.Fields(lngCompteur).Value
	Next
End With
Ces lignes comportent, en l'état, une faille : xlUp. comme son nom l'indique, avec un préfixe en xl, il s'agit d'une constante propre à Excel. Comme il n'a jamais été question de référencer la bibliothèque Excel dans ce tutoriel, nous n'avons pas accès aux constantes des autres bibliothèques. Donc aucun accès aux constantes de la bibliothèque Excel, donc aucun moyen d'accéder à xlUp. Nous devons donc redéfinir la constante xlUP. Pour connaitre sa valeur, il suffit de faire une recherche dans l'explorateur d'objets (F2), sur un projet qui référence la bibliothèque Excel ; Nous trouverions alors la valeur -4162 (&HFFFFEFBE), ce qui nous permettra de créer notre propore constante :

Const xlUp As Long = &HFFFFEFBE
Bien évidemment, il s'agit d'un code exemple. Dans la réalité, il conviendrait de mettre la déclaration de la variable lngCompteur en tête du module, avec les autres déclarations. Nous allons peut-être prendre un peu de temps pour expliquer la ligne de code la plus longue, pour ceux qui n'en ont pas l'habitude. Mais, je ne peux que vous encourager à parcourir le tutoriel concernant les syntaxes de base du langage VBA.

Le Code VBA Les Explications
objClasseur.Worksheets(1).Range("A65536").End(xlup)

End With
On part de l'objet Classeur.
On va dans les WorkSheets (feuilles de calcul), récupérer la première. On obtient en résultat (puisqu'il y a des parenthèses) un objet de type WorkSheet.
Dans cette objet, on va récupérer (parenthèses... comme d'habitude) un objet Range qui est, en fait, la cellule A65536, la dernière de la colonne A.
Maintenant que nous avons cet objet, nous allons à la fin (End = touche du clavier), vers le haut (xlUp = Touche du clavier), ce qui nous renvoie (encore des parenthèses) un nouvel objet de type Range.
La structure With indique que nous souhaitons travailler AVEC cet objet. Toute ligne débutant par un point (.) sera rattachée à cette définition de la Range de référence.
.Offset(1,lngCompteur).Value Enfin, on décale cette cellule de 1 ligne, pour être toujours sous la dernière ligne du tableau, et d'un nombre de colonnes correspondant au n° du champ du Recordset (lngCompteur), ce qui nous renvoie (encore et toujours les parenthèses) un objet de type Range.
Ce qu'on souhaite modifier (à gauche du symbole =) est la valeur de l'objet Range ainsi récupéré, objet Range qui sera forcément situé SOUS le tableau existant, et ce, quel que soit le nombre de lignes du tableau, dans la mesure où le nombre total de lignes n'excède pas 65536.

Il faut noter que cette technique a un autre avantage : Il est impossible d'écrire dans la ligne n°1 de la feuille de calcul, puisque Offset décale systématiquement la plage(Range) renvoyée de 1 ligne. Cela signifie que, lors de la première éxécution de cette ligne de code, avant l'Offset, la Range renvoyée par objClasseur.Worksheets(1).Range("A65536").End(xlup) sera en fait A1. Et puisque qu'on fera alors un Offset(1,0) de cette cellule, nous arriverons sur A2. On ne peut donc pas écrire sur la ligne 1, et ceci nous servira ultérieurement pour positionner les titres du tableau généré.
= Symbole d'affectation (il faut vraiment aller voir le tutoriel sur les syntaxes de base si ceci n'est pas compris !
rstDatas.Fields(lngCompteur).Value On veut récupérer la Valeur du champ n° lngCompteur du Recordset.
Cette syntaxe est la plus conforme à la 'norme VBA'. Cependant, vous trouverez d'autres écritures qui sont des raccourcis, ou des spécificités de DAO et d'Access, comme, par exemple rstDatas(lngCompteur).Value
Avec ce code, on est parvenu à écrire les données, mais il faut maintenant parvenir à écrire les noms des colonnes. Pour ce faire, nous devons à nouveau parcourir les champs, mais cette fois, pour récupérer leurs noms, et non les valeurs. Nous pourrons facilement écrire ces noms dans les cellules de la première ligne de la feuille de calcul :

For lngCompteur = 0 to rstDatas.Fields.Count-1    'les numéros d'Index des champs commencent à 0
	objClasseur.Worksheets(1).cells(1,lngCompteur +1).Value=rstDatas.Fields(lngCompteur).name
Next
Il faut alors enregistrer le fichier. Cependant, comme ce nom servira à deux reprises (enregistrement puis ouverture), nous allons générer un nom que nous stockerons dans une variable

Dim strNomFichier as String
strNomFichier = CurrentProject.Path & "\datas_" & Format(Now(), "yyymmddhhnnss") & ".xls"
Maintenant, on peut facilement enregistrer le classeur, puis tout fermer proprement

    objClasseur.SaveAs strNomFichier
    
    'Tout fermer proprement
    objClasseur.Close
    objExcel.Quit
    Set objClasseur = Nothing
    Set objExcel = Nothing
Pour terminer, il ne reste plus qu'à ouvrir le fichier. J'ai choisi la solution du Shell pour déconnecter l'instance générée par Shell de mon programme, et lui donner sa propre autonomie. Shell lancera l'exécutable Excel.exe, en lui passant comme paramètre le nom du fichier (entre " = Chr(34))

Shell "Excel.exe " & Chr(34) & strNomFichier & Chr(34)

I-B. Le code complet

Précédemment, nous avons détaillé le processus... voici le code complet et testé. Vous pouvez, si vous le voulez télécharger le module directement à cet endroit

Sub TransfererVersExcel()
'---------------------------------------------------------------------------------------
' CopyRight : Ce code est librement ditribuable, copiable et imprimable, sous la seule
'             contrainte de laisser visible la totalité des commentaires identifiant
'             l'auteur de ce code, ses coordonnées, et ce copyright, et ce, sans
'             limitation de durée dans le temps.
'---------------------------------------------------------------------------------------
' Procedure : TransfererVersExcel
' Date      : vendredi 04 janvier 2008 23:13
' Auteur    : Maxence Hubiche (mhubiche@club-internet.fr)
'---------------------------------------------------------------------------------------
'
    Dim objExcel        As Object           'Variable pour l'objet Excel.Application
    Dim objClasseur     As Object           'Variable pour l'objet Excel.WorkBook
    Dim lngCompteur     As Long             'Variable de compteur des champs
    Dim strNomFichier   As String           'Variable pour le nom du fichier
    Dim rstDatas        As DAO.Recordset    'Variable pour le recordset
    
    Const xlUp As Long = &HFFFFEFBE         'Constante pour recréer xlUp  (d'Excel)
    
    'Définir le Recordset
    Set rstDatas = CurrentDb.OpenRecordset("qrySource", dbOpenSnapshot)
     
    'Définir les objets Application et Classeur d'Excel
    Set objExcel = CreateObject("Excel.Application")
    Set objClasseur = objExcel.WorkBooks.Add()
    
    'Faire la boucle jusqu'à ce qu'on arrive à la fin (EOF=EndOfFile) du Recordset
    Do Until rstDatas.EOF
        'remplir les cellules avec les données
        With objClasseur.Worksheets(1).Range("A65536").End(xlUp)
            For lngCompteur = 0 To rstDatas.Fields.Count - 1  'les numéros d'Index des champs commencent à 0
                .Offset(1, lngCompteur).Value = rstDatas.Fields(lngCompteur).Value
            Next
        End With
        rstDatas.MoveNext    'Passer à l'enregistrement suivant
    Loop
    'Remplir les titres des colonnes
    For lngCompteur = 0 To rstDatas.Fields.Count - 1  'les numéros d'Index des champs commencent à 0
        objClasseur.Worksheets(1).cells(1, lngCompteur + 1).Value = rstDatas.Fields(lngCompteur).Name
    Next
    
    'Enregistrer le classeur
    strNomFichier = CurrentProject.Path & "\datas_" & Format(Now(), "yyymmddhhnnss") & ".xls"
    objClasseur.saveas strNomFichier
    
    'Tout fermer proprement
    objClasseur.Close
    objExcel.Quit
    Set objClasseur = Nothing
    Set objExcel = Nothing
    
    'Afficher le classeur
    Shell "Excel.exe " & Chr(34) & strNomFichier & Chr(34)
End Sub

III. On ne pourrait pas la faire un peu plus courte, monsieur Cadbury ?


III-A. Les explications

Le code précédent, comme vous l'avez peut-être testé, fonctionne très bien, MAIS... mais, si votre recordset contient beaucoup de champs et beaucoup de lignes, le traitement itératif risque d'être long, long, long. il existe une méthode des plus intéressantes dans Excel, sur les instances de la classe Range, qui permet en une seule fois de copier tout un recordset. il s'agit de la méthode CopyFromRecordset

Voici le même code, mais revisité, afin de permettre la copy du recordset sans itération. Il suffit de changer cette partie du code précédent :

    Do Until rstDatas.EOF
        'remplir les cellules avec les données
        With objClasseur.Worksheets(1).Range("A65536").End(xlUp)
            For lngCompteur = 0 To rstDatas.Fields.Count - 1  'les numéros d'Index des champs commencent à 0
                .Offset(1, lngCompteur).Value = rstDatas.Fields(lngCompteur).Value
            Next
        End With
        rstDatas.MoveNext    'Passer à l'enregistrement suivant
    Loop
par cette simple ligne :

    objClasseur.Worksheets(1).Range("A2").CopyFromRecordset rstDatas

III-B. Le code complet

Vous pouvez télécharger le fichier contenant le code suivant :

Option Compare Database
Option Explicit

Sub TransfererVersExcel_V2()
'---------------------------------------------------------------------------------------
' CopyRight : Ce code est librement ditribuable, copiable et imprimable, sous la seule
'             contrainte de laisser visible la totalité des commentaires identifiant
'             l'auteur de ce code, ses coordonnées, et ce copyright, et ce, sans
'             limitation de durée dans le temps.
'---------------------------------------------------------------------------------------
' Procedure : TransfererVersExcel_V2
' Date      : vendredi 04 janvier 2008 23:13
' Auteur    : Maxence Hubiche (mhubiche@club-internet.fr)
'---------------------------------------------------------------------------------------
'
    Dim objExcel        As Object           'Variable pour l'objet Excel.Application
    Dim objClasseur     As Object           'Variable pour l'objet Excel.WorkBook
    Dim lngCompteur     As Long             'Variable de compteur des champs
    Dim strNomFichier   As String           'Variable pour le nom du fichier
    Dim rstDatas        As DAO.Recordset    'Variable pour le recordset
    
    Const xlUp As Long = &HFFFFEFBE         'Constante pour recréer xlUp  (d'Excel)
    
    'Définir le Recordset
    Set rstDatas = CurrentDb.OpenRecordset("qrySource", dbOpenSnapshot)
     
    'Définir les objets Application et Classeur d'Excel
    Set objExcel = CreateObject("Excel.Application")
    Set objClasseur = objExcel.WorkBooks.Add()
    
    'remplir les cellules avec les données
    objClasseur.Worksheets(1).Range("A2").CopyFromRecordset rstDatas
    'Remplir les titres des colonnes
    For lngCompteur = 0 To rstDatas.Fields.Count - 1  'les numéros d'Index des champs commencent à 0
        objClasseur.Worksheets(1).cells(1, lngCompteur + 1).Value = rstDatas.Fields(lngCompteur).Name
    Next
    
    'Enregistrer le classeur
    strNomFichier = CurrentProject.Path & "\datas_" & Format(Now(), "yyyymmddhhnnss") & ".xls"
    objClasseur.saveas strNomFichier
    
    'Tout fermer proprement
    objClasseur.Close
    objExcel.Quit
    Set objClasseur = Nothing
    Set objExcel = Nothing
    
    'Afficher le classeur
    Shell "Excel.exe " & Chr(34) & strNomFichier & Chr(34)
End Sub

IV. Mais, je veux choisir où mettre quelles données !


IV-A. Les explications

Nous savons maintenant copier un recordset dans une feuille de calcul ! Yeepee ! Cependant, j'aimerai qu'on se concentre sur un problème beaucoup plus fin, avec des contraintes beaucoup plus élevées. M. Jean-Luc Reichman dirait : "Attention à la marche !"

Je vais tenter d'exprimer succintement les contraintes

Examinons maintenant la manière dont nous allons nous y prendre.

Notre raisonnement doit se faire en deux phases. La première phase est la conception de l'outils que nous allons mettre en oeuvre. La deuxième concerne l'utilisation de l'outil créé.


IV-A-1. Les classes

Nous avons bien mis dans les prérequis de cette solution que nous voulions travailler avec des classes.

La première classe sera la classe CELLULE. Cette classe aura pour but de définir à la fois l'emplacement des données dans le classeur modèle et la donnée à mettre à cet emplacement.

La seconde classe sera la classe CELLULES. Non, il n'y a pas d'erreur. Il s'agit bien de CELLULES, mais avec un S. Il s'agit donc ici de reproduire le comportement de Collection de CELLULE

Enfin, la troisième classe sera la classe MODELE. Il s'agira de la classe fondamentale de l'architecture que nous mettons en place. En effet, dans un MODELE, il y aura une collection de CELLULE à renseigner. Il sera possible de recenser cet ensemble d'objets CELLULE dans la collection CELLULES.


IV-A-2. La classe CELLULE

C'est la classe la plus simple. Elle nécessite 3 propriétés, et nous rajouterons une méthode Add pour simplifier la saisie des trois propriétés.

En ce qui concerne les propriétés, Nous pourrions simplement utiliser des variables publiques

Public NoFeuille		As Byte
Public rngAddress		as String
public rngValue			as String
Mais, j'aimerai forcer l'utilisation de la méthode Add, aussi, j'aimerai que ces trois propriétés soient en lecture seule. Il est impossible de faire une propriété en lecture seule avec une variable, donc, nous allons définir des propriétés de manière plus conventionnelle, et donc correcte. Pour chaque propriété, nous allons définir une propriété Private, puis une procédure Property Get pour chacune des trois propriétés.

Private m_strAddress    As String
Private m_strValue      As String
Private m_bytNoFeuille As Byte

Public Property Get rngAddress() As String
    rngAddress = m_strAddress
End Property
Public Property Get rngValue() As String
    rngValue = m_strValue
End Property
Public Property Get NoFeuille() As Byte
    NoFeuille = m_bytNoFeuille
End Property
Voilà pour ce qui concerne les propriétés de la classe CELLULE. Passons maintenant à la méthode Add. Cette méthode également est très simple, puisqu'il s'agit tout simplement de définir les trois variables privées. Cependant le NoFeuille par défaut sera fixé à 1, afin que, si l'utilisateur ne souhaite pas définir la feuille, on écrive, par défaut, sur la première feuille de calcul.

Public Sub Add(myAddress As String, myValue As String, Optional myNoFeuille As Byte = 1)
    m_strAddress = myAddress
    m_strValue = myValue
    m_bytNoFeuille = myNoFeuille
End Sub

IV-A-3. La classe CELLULES

La classe cellule doit conserver la collection de toutes les cellules dont nous allons avoir besoin. Ce nombre n'est pas défini par avance. Nous allons donc utiliser une instance de classe Collection.

Private m_colCellules       As Collection
Cette ligne ne fait que déclarer une variable de type collection. Mais il va bien falloir l'instancier à un moment donné. Le meilleur moment pour cette opération est l'initialisation de la classe :

Private Sub Class_Initialize()
    Set m_colCellules = New Collection
End Sub
Nous aurons également besoin, ultérieurement, de connaitre le nombre de cellules référencées au sein du modèle que nous utiliserons. Il est donc utile de prévoir une fonction qui nous renverra ce nombre :

Public Function Count() As Long
    Count = m_colCellules.Count
End Function
Que manque-t-il ? Tout simplement la possibilité d'ajouter des objets de type CELLULE dans la collection. et puisqu'il s'agit d'ajouter, une méthode Add fera parfaitement l'affaire. Cette méthode sera une fonction, dont la signature sera très proche de la signature de la méthode Add de la classe CELLULE. Elle aura d'ailleurs à appeler cette méthode. Mais, une fois l'objet correctement renseigné, on l'ajoutera à la collection.

Public Function Add(myAdresse As String, myValue As String, Optional myNoFeuille As Byte = 1) As Cellule
    Dim objCellule As Cellule                       'Déclarer l'objet
    Set objCellule = New Cellule                    'Créer une instance de l'objet
    objCellule.Add myAdresse, myValue, myNoFeuille  'Définir les propriétés de l'objet
    m_colCellules.Add objCellule                    'Ajouter l'objet à la collection
    Set Add = objCellule                            'renvoyer l'objet
End Function

IV-A-4. La classe MODELE

La classe modèle est, je le rappelle, la classe de base. Elle va contenir une collection de cellule, et faire l'ensemble des actions d'écriture sur le fichier.

La première chose à faire est donc de définir cette collection de cellules. Et, puisque nous avons créé une classe CELLULES, nous n'allons pas nous priver et l'utiliser, à travers une variable déclarée en tête de module.

Private m_objCellules As Cellules
Bien sûr, il faudra penser à instancier l'objet m_objCellules à un moment donné. Encore une fois, nous allons utiliser l'évènement Initialize de la classe :

Private Sub Class_Initialize()
    Set m_objCellules = New Cellules
End Sub
Il faut aussi penser à prévoir un membre de la classe qui renvoie cette collection, sinon, il sera difficile de l'utiliser. Nous allons donc créer une propriété en lecture seule qui renverra notre objet de type Cellules.

Public Property Get Cellules() As Cellules
    Set Cellules = m_objCellules
End Property
Passons maintenant à la partie de mise en oeuvre. Nous allons prévoir un membre qui sera la méthode de base des objets de type modèle. L'appel de cette méthode lacera l'exécution que nous attendons tous :

Certaines parties de ce programme ont déja été vues dans les premières parties de ce tutoriel, aussi, nous ne nous apesantirons pas dessus, maintenant.

    Dim objExcel  As Object
    Dim objClasseur  As Object
    Dim lngCompteur As Long
'Définir les deux variables
    Set objExcel = CreateObject("Excel.Application")
    
<<snip>>

    'Enregistrer le fichier
    objClasseur.SaveAs NomFichier
'Tout quitter et Fermer
    objClasseur.Close
    objExcel.Quit
    Set objClasseur = Nothing
    Set objExcel = Nothing
Par contre, vous aurez certainement noté que, lors de l'enregistrement, nous passons à la méthode SaveAs de notre objet Classeur, un paramètre nommé NomFichier. D'où vient cet argument ?

Notre procédure devra avoir deux arguments, le premier étant obligatoire (le nom du fichier que nous souhaitons générer), le seconde étant facultatif (le nom du modèle à utiliser). Bien sûr, s'il n'y a pas de modèle de défini, un document vierge sera généré. Aussi, la ligne de signature de la procédure ressemblera à ceci :

Public Sub Generer(NomFichier As String, Optional myModele As String = "")
Et de ce fait, nous comprenons d'où vient cet argument NomFichier : il s'agit d'une information qui sera fournie à la méthode Generer par le développeur qui l'utilisera.

Par contre, si nous nous arrêtons sur myModele, nous allons constater qu'il est optionnel. Nous allons donc devoir créer le modèle de deux façons différentes, suivant que le modèle est défini, ou pas.

    If myModele = "" Then
        Set objClasseur = objExcel.WorkBooks.Add()
    Else
        Set objClasseur = objExcel.WorkBooks.Add(myModele)
    End If
Enfin, puisque nous avons une collection de cellules, nous allons la parcourir pour que nous puissions ainsi renseigner chaque cellule avec les informations voulues

    For lngCompteur = 1 To m_objCellules.Count
        <<snip>>
    Next
Nous voyons ici pourquoi il était utile de développer un membre Count à notre classe Cellules !

Comment remplir le fichier généré ? Pour faire simple, décomposons. Nous avons une série d'objet cellule dans une collection. On peut parcourir une collection par leur n° d'index. Notre collection étant typée, il sera facile de récupérer les propriétés que nous avons définies en lecture seule, pour chaque objet. Cela donera, en fait, une écriture sous la forme suivante :

ObjetCellules.Cellule(index).Propriété
Puisque la saisie d'une valeur dans une cellule d'une feuille de calcul d'un classeur répond à la syntaxe générale suivante

Classeur.WorkSheets(Numero).Range(Adresse).Formula = Valeur
Eu égard à ce que nous avons fait ici, nous savons que 3 de ces informations sont dans nos objets cellules. il s'avit du numéro de la feuille, de l'adresse de la cellule et de la valeur. Quant à la partie Classeur, nous avons à disposition notre objClasseur. Cette ligne parviendra donc à renseigner la cellule voulue, avec la valeur voulue, et ceci, quel que soit le n° de la cellule, dans l'ordre des cellules référencées dans le modèle

objClasseur.WorkSheets(m_objCellules.Cellule(lngCompteur).NoFeuille).Range(m_objCellules.Cellule(lngCompteur).rngAddress).Formula = m_objCellules.Cellule(lngCompteur).rngValue

IV-B. Le code complet

Vous trouverez ci-dessous la compilation des codes des trois modules de classe que nous venons de détailler :
La classe CELLULE

'---------------------------------------------------------------------------------------
' CopyRight : Ce code est librement ditribuable, copiable et imprimable, sous la seule
'             contrainte de laisser visible la totalité des commentaires identifiant
'             l'auteur de ce code, ses coordonnées, et ce copyright, et ce, sans
'             limitation de durée dans le temps.
'---------------------------------------------------------------------------------------
' Module    : Cellule (Module de classe)
' Date      : samedi 05 janvier 2008 01:49
' Auteur    : Maxence Hubiche (mhubiche@club-internet.fr)
' Objet     : Ce module contient l'ensemble des propriétés et méthodes permettant le
'				Stockage des informations de localisation d'information et de valeur
'---------------------------------------------------------------------------------------
'
Option Explicit


Private m_strAddress    As String
Private m_strValue      As String
Private m_bytNoFeuille As Byte

Public Property Get rngAddress() As String
    rngAddress = m_strAddress
End Property
Public Property Get rngValue() As String
    rngValue = m_strValue
End Property
Public Property Get NoFeuille() As Byte
    NoFeuille = m_bytNoFeuille
End Property
Public Sub Add(myAddress As String, myValue As String, Optional myNoFeuille As Byte = 1)
    m_strAddress = myAddress
    m_strValue = myValue
    m_bytNoFeuille = myNoFeuille
End Sub
La classe CELLULES

'---------------------------------------------------------------------------------------
' CopyRight : Ce code est librement ditribuable, copiable et imprimable, sous la seule
'             contrainte de laisser visible la totalité des commentaires identifiant
'             l'auteur de ce code, ses coordonnées, et ce copyright, et ce, sans
'             limitation de durée dans le temps.
'---------------------------------------------------------------------------------------
' Module    : Cellules (Module de classe)
' Date      : samedi 05 janvier 2008 02:18
' Auteur    : Maxence Hubiche (mhubiche@club-internet.fr)
' Objet     : Collection d'objets issus de la classe Cellule
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Private m_colCellules       As Collection

Public Property Get Cellule(Item As Long) As Cellule
    Set Cellule = m_colCellules(Item)
End Property

Public Function Add(myAdresse As String, myValue As String, Optional myNoFeuille As Byte = 1) As Cellule
    Dim objCellule As Cellule                       'Déclarer l'objet
    Set objCellule = New Cellule                    'Créer une instance de l'objet
    objCellule.Add myAdresse, myValue, myNoFeuille  'Définir les propriétés de l'objet
    m_colCellules.Add objCellule                    'Ajouter l'objet à la collection
    Set Add = objCellule                            'renvoyer l'objet
End Function

Private Sub Class_Initialize()
    Set m_colCellules = New Collection
End Sub

Public Function Count() As Long
    Count = m_colCellules.Count
End Function
La classe MODELE

'---------------------------------------------------------------------------------------
' CopyRight : Ce code est librement ditribuable, copiable et imprimable, sous la seule
'             contrainte de laisser visible la totalité des commentaires identifiant
'             l'auteur de ce code, ses coordonnées, et ce copyright, et ce, sans
'             limitation de durée dans le temps.
'---------------------------------------------------------------------------------------
' Module    : Modele (Module de classe)
' Date      : samedi 05 janvier 2008 02:31
' Auteur    : Maxence Hubiche (mhubiche@club-internet.fr)
' Objet     :
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Private m_objCellules As Cellules

Public Sub Generer(NomFichier As String, Optional myModele As String = "")
'Demander deux variable 'objet' pour pouvoir stocker les instance des
'applications Excel, et du classeur qu'on va créer
    Dim objXLAppli  As Object
    Dim objXLClass  As Object
    Dim lngCompteur As Long
'Définir les deux variables
    Set objXLAppli = CreateObject("Excel.Application")
    If myModele = "" Then
        Set objXLClass = objXLAppli.WorkBooks.Add()
    Else
        Set objXLClass = objXLAppli.WorkBooks.Add(myModele)
    End If
'Remplir le document
    For lngCompteur = 1 To m_objCellules.Count
        objXLClass.WorkSheets(lngCompteur).Range(m_objCellules.Cellule(lngCompteur).rngAddress).Formula = m_objCellules.Cellule(lngCompteur).rngValue
    Next
'Enregistrer le fichier
    objXLClass.SaveAs NomFichier
'Tout quitter et Fermer
    objXLClass.Close
    objXLAppli.Quit
    Set objXLClass = Nothing
    Set objXLAppli = Nothing
End Sub

Public Property Get Cellules() As Cellules
    Set Cellules = m_objCellules
End Property

Private Sub Class_Initialize()
    Set m_objCellules = New Cellules
End Sub

IV-C. Comment utiliser ce code ?

Cette partie est vraiment la plus simple. En effet, tout ce que nous avons fait jusque là ne concerne que la création d'un outil. Voyons maintenant son utilisation. Par exemple, je veux écrire dans A1 de la feuille 1 le texte TOTO, et TITI dans la cellule B3 de la feuille 2. Comment vais-je m'y prendre ?

Sub PreparerEnvoi()
	'Créer un modele
    Dim x As Modele
    Set x = New Modele
    'Remplir les cellules
    x.Cellules.Add "A1", "TOTO"
    x.Cellules.Add "B3", "TITI", 2
    'Générer le fichier, sans aucun modèle
    x.Generer "c:\test.xls"
End Sub
Et si je souhaitais faire la même chose, mais à partir d'un modèle existant ? Il suffit de définir le modèle lors de l'appel de la méthode generer

Sub PreparerEnvoi()
	'Créer un modele
    Dim x As Modele
    Set x = New Modele
    'Remplir les cellules
    x.Cellules.Add "A1", "TOTO"
    x.Cellules.Add "B3", "TITI", 2
    'Générer le fichier, sans aucun modèle
    x.Generer "c:\test.xls", "c:\mon_modele.xlt"
End Sub

V. Remerciements

J'espère que vous aurez apprécié la lecture de ces quelques lignes, et qu'elle vous auront donné l'envie d'améliorer encore le procédé.

Je tiens à remercier le travail d'équipe de tous les bénévoles qui participent à ce site, et, dans le cadre des observations et corrections apportées à ce tutoriel, mes remerciements vont tout particulièrement à

Correction
Commentaires


(1)Le LateBinding et l'EarlyBinding feront l'objet d'un autre tutoriel, actuellement en cours de rédaction

Valid XHTML 1.1!Valid CSS!

Copyright © 2010 Maxence HUBICHE. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Responsables bénévoles de la rubrique Microsoft Office : Pierre Fauconnier - Arkham46 -