Excel Automation

Comment piloter Excel à distance ?

Excel peut être démarré et manipulé comme tout objet Access. La technique est nommée Automation (Automatisation) par Microsoft ; elle permet depuis Access de réaliser quasiment n’importe quelle action Excel (ceci suppose surtout une connaissance des objets Excel).

Voici un exemple assez simple qui effectue les actions suivantes :

  • Démarrer Excel et le rendre visible.
  • Créer un nouveau classeur.
  • Ecrire 3 chiffres et une somme dans la feuille active.
  • Enregistrer et fermer le classeur.

Recopiez ce code dans un module standard d’Access puis exécutez-le via l’icône Exécuter Sub/UserForm ou la fenêtre Exécution.

Important
Pour que les objets Excel soient utilisables dans Access, leur bibliothèque doit être associée à votre base de données.

  1. Ouvrez Visual Basic Editor (ou, dans Access 97, ouvrez un module quelconque).
  2. Cliquez sur le menu Outils / Références.
  3. Dans la boîte de dialogue, cochez la référence Microsoft Excel Object Library.

Pour plus de détails sur les références, consultez cette page.

Plusieurs remarques

  • Il est toujours conseillé de libérer de la mémoire les objets que vous avez manipulés. D’où les Set xxx = Nothing.
  • Un classeur Excel est créé sur le disque dur. Si un classeur de même nom existe déjà, le message classique d’Excel (« Ecraser Oui/Non ») s’affiche normalement.
  • Vous pouvez tout à fait ne pas afficher Excel (supprimez la ligne xlApp.Visible = True du programme. Dans ce cas, en cas de problème, sachez qu’un Excel restera toujours en tâche de fond (vous le détectez via Ctrl+Alt+Suppr).
Astuce
Automation nécessite une bonne connaissance du modèle objet du logiciel à programmer. Excel dispose d’un enregistreur de macros qui peut vous faciliter la tâche en traduisant vos actions clavier/souris en code VBA Excel (code que vous pourrez ensuite analyser ou adapter). Cet enregistreur se déclenche via le menu Outils / Macro / Nouvelle macro d’Excel.

Vous aimerez aussi...

14 réponses

  1. Hervé Inisan dit :

    xavier 69 > Difficile de trouver le bug sans les données de test. Mais en simplifiant le code, on devrait déjà y voir plus clair. 😉

    • Notamment, je pense que la plupart des xlApp.Workbooks("temp1.xlsx") (tous ?) peut être remplacée par wbk. Moins de code redondant = moins de modifications à gérer.
    • D’autre part, j’enlèverais la ligne DisplayAlerts, au moins tant que le programme ne marche pas. Ça permettra de mieux détecter les erreurs.
    • Je vois aussi que la variable c sert comme variable de boucle (dans le For Each), mais qu’elle est modifiée ensuite par une autre valeur. Du coup, ça doit donner des effets imprévisibles. Il faudrait sans doute 2 variables distinctes.
  2. xavier 69 dit :

    Bonjour,

    J’essaie d’exécuter depuis Access en Automation un module écrit dans Excel qui permet de créer des onglets par « contact » (dans la colonne A) dans un fichier Excel à partir d’une base qui contient plusieurs lignes pour chaque contact.
    Le code est le suivant dans Excel :

    [A1:av10].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[a20], Unique:=True

    For Each c In Range([a21], [a30].End(xlUp))
    Sheets(« base »).[a21] = c
    c = Replace(c, « ‘ »,  » « , 1, -1, vbTextCompare)
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = c
    Sheets(« base »).[A1:av10].AdvancedFilter , CriteriaRange:=Sheets(« base »).[a20:a21], Action:=xlFilterCopy, CopyToRange:=[a2]
    Columns(« A:A »).Select
    Selection.Delete Shift:=xlToLeft
    Range(« A1 »).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= » », SubAddress:= »‘Index’!A1″, TextToDisplay:=Sheets(« Index »).Name
    Range(« A2 »).Select
    Selection.AutoFilter
    Next c

    Malheureusement, la façon dont je l’ai transcrit dans Access ne fonctionne pas, il ne créer qu’un onglet avec la première ligne de la base qui ne contient que l’intitulé de la colonne. Merci par avance pour votre aide,

    Essai de code Automation Access :
    Dim xlApp As Excel.Application
    Dim wbk As Excel.Workbook
    Dim strClasseur As String

    strClasseur = « C: » & Environ(« HOMEPATH ») & « Documentstemp1.xlsx »
    Set xlApp = CreateObject(« Excel.Application »)
    Set wbk = xlApp.Workbooks.Open(strClasseur)
    xlApp.DisplayAlerts = False

    xlApp.Workbooks(« temp1.xlsx »).Sheets(« base »).Range(« A1:av10 »).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=xlApp.Workbooks(« temp1.xlsx »).Sheets(« base »).Range(« A20 »), Unique:=True

    For Each c In Range(xlApp.Workbooks(« temp1.xlsx »).Sheets(« base »).Cells(21, 1), xlApp.Workbooks(« temp1.xlsx »).Sheets(« base »).Cells(30, 1)).End(xlUp)
    xlApp.Workbooks(« temp1.xlsx »).Sheets(« base »).Cells(21, 1) = c
    c = Replace(c, « ‘ »,  » « , 1, -1, vbTextCompare)
    xlApp.Workbooks(« temp1.xlsx »).Worksheets.Add after:=Sheets(xlApp.Workbooks(« temp1.xlsx »).Sheets.Count)
    xlApp.Workbooks(« temp1.xlsx »).ActiveSheet.Name = c
    xlApp.Workbooks(« temp1.xlsx »).Sheets(« base »).Range(« A1:AV10 »).AdvancedFilter , CriteriaRange:=xlApp.Workbooks(« temp1.xlsx »).Sheets(« SélectionparticipantsExcelparco »).Range(« A20:A21 »), Action:=xlFilterCopy, CopyToRange:=xlApp.Workbooks(« temp1.xlsx »).Sheets(c).Range(« A2 »)
    xlApp.Workbooks(« temp1.xlsx »).Sheets(c).Columns(1).Delete Shift:=xlToLeft

    xlApp.Workbooks(« temp1.xlsx »).Sheets(c).Range(« A1 »).Select
    xlApp.Workbooks(« temp1.xlsx »).ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= » », SubAddress:= »‘Index’!A1″, TextToDisplay:=Sheets(« Index »).Name
    xlApp.Workbooks(« temp1.xlsx »).Sheets(c).Range(« A2 »).Select
    xlApp.Workbooks(« temp1.xlsx »).Sheets(c).Range(« A2 »).Selection.AutoFilter
    Next c

    wbk.Close True
    Set wbk = Nothing
    xlApp.Quit
    Set xlApp = Nothing

  3. Hervé Inisan dit :

    christophe vdw > Super. 🙂

  4. CHRISTOPHE VDW dit :

    bonjour
    OK ca marche, je n’avais pas placer le code ou il faut.ca marche beaucoup mieux en utilisant ce code dans événement sur clic d’un bouton!!!!!!!!!
    encore merci

  5. Hervé Inisan dit :

    christophe vdw > Essaie dans un premier temps de reprendre le code de tiyo, sans la partie « Export du sous-formulaire » (et en adaptant les noms de feuilles Excel et les noms de champs Access). Ça permettra déjà de tester.

  6. christophe vdw dit :

    bonsoir
    d’abord merci pour les 2 premier episode « exportation CSV »
    encore une nouvelle question!!!
    je veux faire « presque »la même chose que tiyo mais n’arrive pas a replacer sont code!!
    mon projet: un classeur prédéfini, avec 2 feuille
    FEUILLE 1:commande je doit « remplir » des cellule avec le nom, code client,No d’appel d’offre. le reste de la feuille est un tableau qui sera remplis par le client
    FEUILLE 2:liting des produit commandable avec 3 champ
    produit,conditionnement,formulation
    cette feuille sera la source des liste déroulante de de feuille 1
    le but de ce classeur est un bon de commande qui sera après retour du client importer dans access donc chaque classeur devra être nommé différament exemple par No appel d’offre&code client
    merci d’avance si vous pouvez m’aider dans ce projet

  7. Hervé Inisan dit :

    Gaalad > Je poste un petit article là-dessus dès que j’ai un moment. Stay tuned!

  8. Gaalad dit :

    Bonjour,
    Je trouve le travail fait dans ce site vraiment exceptionnel.
    Merci à toi Hervé.
    J’ai bien compris avec ton exemple et celui de Tiyo comment exporter quelques données d’Access (que ce soit une table ou un formulaire simple)
    Par contre, je suis un peu noyé lorsqu’il s’agit d’une requête.
    J’ai trouvé une série d’exemples sur le net mais je dois y avouer que j’y comprends pas toujours grand chose.
    Serait-il possible d’avoir un exemple simple d’exportation d’une requête vers Excel.
    Merci beaucoup

  9. Hervé Inisan dit :

    tiyo > Merci pour le retour, quelqu’un y trouvera peut-être de l’inspiration ! 🙂

  10. tiyo dit :

    Voila mon code d’export access vers excel
    Ce n’est pas un code très professionnel mais il marche.

    ‘Création des différents Objets
    Dim appExcel As Object
    Dim wbExcel As Object
    Dim wsExcel As Object

    ‘EXPORT DU FORMULAIRE PRINCIPAL
    ‘Création du fichier .xls et ouverture de la feuille
    Set appExcel = CreateObject(« Excel.Application »)
    Set wbExcel = appExcel.Workbooks.Open(« C:Documents and SettingsproprietaireMes documentsfa-0000.xlsx »)
    appExcel.Sheets(« Feuil1 »).Select
    Set wsExcel = wbExcel.ActiveSheet

    ‘Remplissage des champ du modele Excel avec les données d’access
    wbExcel.Sheets(1).Range(« E13 »).Value = Form![Nom] ‘Nom
    wbExcel.Sheets(1).Range(« A16 »).Value = Form![Date] ‘Date
    wbExcel.Sheets(1).Range(« E15 »).Value = Form![Adresse] ‘Adresse
    wbExcel.Sheets(1).Range(« B16:C16 »).Value = « Facture  » & Form![No_Facture] ‘No facture
    wbExcel.Sheets(1).Range(« B49 »).Value = Form![Type_Paiement] ‘Type de paiment
    wbExcel.Sheets(1).Range(« E53 »).Value = Form![Acompte] ‘Acompte

    ‘ FIN D’EXPORT DU FORMULAIRE

    ‘ EXPORT DU SOUS FORMULAIRE

    Dim TaQueryDef As QueryDef
    Dim rst As Recordset

    Set TaQueryDef = CurrentDb.QueryDefs(« RItem »)
    TaQueryDef.Parameters(0) = [No_Facture]
    Set rst = TaQueryDef.OpenRecordset()

    rst.MoveFirst

    Do While Not rst.EOF
    wbExcel.Sheets(1).Range(« A23 »).CopyFromRecordset rst
    Loop

    Set req = Nothing
    Set rst = Nothing

    ‘ FIN D’EXPORT DU SOUS FORMULAIRE

    ‘Fermeture avec sauvegarde
    wbExcel.SaveAs FileName:= »fa- » & [No_Facture]
    wbExcel.Close (False)
    appExcel.Quit

    ‘ Liberation des objets
    Set wsExcel = Nothing
    Set wbExcel = Nothing
    Set appExcel = Nothing

  11. Hervé Inisan dit :

    tiyo > On ne sait jamais, ça peut toujours aider quelqu’un. Merci.

  12. tiyo dit :

    Merci pour la réponse rapide mais j’ai trouvé un truc qui marche donc pas besoin de poster le code. Si tu pense que le code peut intéresser quelqu’un dit le je le posterai .

    Voila merci encore :d Si j’ai d’autre question je redemanderai.

  13. Hervé Inisan dit :

    tiyo > Est-ce que tu peux poster ici le code d’exportation (s’il n’est pas trop long, et un peu formaté de préférence, pour qu’il soit lisible) ?

  14. tiyo dit :

    Bonjour Inisan, voila j’ai envoyé des données d’access ( données issue d’un formulaire + sous formulaire ). Le transfert par automation se fait trés bien pour le formulaire mais j’ai un probleme avec le sous formulaire. C’est un sous-formulaire issu d’une requéte sur 2 tables différentes en feuille de données. Lorsque j’ai plusieurs lignes dans ma feuille de données, mon code transfert qu’une seule ligne.
    Est-ce possible de transférer toute les lignes d’une feuilles de données dans des cellule d’un modele excel ?

    Merci beaucoup ^^

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *