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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
' --- ' AUTOMATION AVEC EXCEL ' --- ' Function ExcelAutomation01() Dim xlApp As Excel.Application Dim wbk As Excel.Workbook Dim sht As Excel.Worksheet ' Créer un objet Excel ' (ce qui équivaut à démarrer Excel à distance) Set xlApp = CreateObject("Excel.Application") With xlApp ' Rendre Excel visible .Visible = True ' Créer un nouveau classeur Set wbk = .Workbooks.Add ' Instancier la feuille active dans le classeur Set sht = wbk.ActiveSheet ' Ecrire quelques valeurs With sht .Range("A1").Value = 10 .Range("A2").Value = 20 .Range("A3").Value = 30 .Range("A4").FormulaLocal = "=somme(A1:A3)" End With ' Sauvegarder et fermer le classeur wbk.SaveAs "C:Mes documentstest.xls" wbk.Close ' Quitter Excel .Quit End With ' Libérer les variables objet Set sht = Nothing Set wbk = Nothing Set xlApp = Nothing End Function |
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.
- Ouvrez Visual Basic Editor (ou, dans Access 97, ouvrez un module quelconque).
- Cliquez sur le menu Outils / Références.
- 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 viaCtrl
+Alt
+Suppr
).
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. 😉
xlApp.Workbooks("temp1.xlsx")
(tous ?) peut être remplacée parwbk
. Moins de code redondant = moins de modifications à gérer.DisplayAlerts
, au moins tant que le programme ne marche pas. Ça permettra de mieux détecter les erreurs.c
sert comme variable de boucle (dans leFor 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.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
christophe vdw > Super. 🙂
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
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.
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
Gaalad > Je poste un petit article là-dessus dès que j’ai un moment. Stay tuned!
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
tiyo > Merci pour le retour, quelqu’un y trouvera peut-être de l’inspiration ! 🙂
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
tiyo > On ne sait jamais, ça peut toujours aider quelqu’un. Merci.
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.
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) ?
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 ^^