Creation d'une macro dans un fichier Excel depuis Delphi

Version imprimable

Bonjour,

Je crée depuis Delphi (2007), un fichier Excel (Excel2007) avec les résultats de mon application : multiples pages, tableaux, graphiques.

Je souhaiterais créer sur une des pages un bouton qui me permette de modifier 2 séries de données sur un graph (ces séries me permettent de définir un gabarit dans lequel doit se trouver mes résultats)

Pour la création du bouton, c'est bon avec :
Code:

1 2 3 MaShape:=MaFeuille.Shapes.AddOLEObject('Forms.CommandButton.1', EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, 10,10, 110,40);
Avec

Code:

MaShape: Shape;

Par contre, je n'ai pas trouver le moyen de modifier le 'caption' du bouton

Pour ce qui est de la macro, j'ai trouve l'exemple suivant (ne serait-ce que pour tester) :
Code:

1 2 3 4 Module := ExcelApplication.VBE.ActiveVBProject.VBComponents.Add(1); Module.CodeModule.InsertLines(1,'Public sub test()'); Module.CodeModule.InsertLines(2,'ShowMsg("Bonjour")'); Module.CodeModule.InsertLines(3,'End Sub');
avec :

Code:

Module: Variant;

seulement a ce moment la, j'ai l'erreur suivante :
'Programmatic access to visual Basic Project is not trusted'

en cherchant sur le forum, j'ai trouve qu'il fallait activer sous Visual Basic la référence :
Microsoft Visual Basic for Applications Extensibility x.xx

J'ai bien cette référence mais je ne peux l'activer que manuellement et pour un fichier a la fois.

Est-ce qu'il existe un moyen d'activer cette référence a chaque fichier que je crée ? ou existe il un manière détournée de creer un macro dynamiquement et de la lier au bouton crée ?

Par avance, merci de vos réponses.

11/10/2011, 12h16
fab256

Regarde du coté des parametres du centre de sécurité d'Excel dans le menu option. (désolé j'ai pas la version francaise)
Excel Options > Trust Center > Trust Center Settings... > Check Trust access to the VBA project object model

vérifie aussi la base de registre pour la clé HKCU\Software\Microsoft\Office\" + oExcel.Application.Version + @"\Excel\Security
11/10/2011, 12h28
drxel

Merci pour l'info :ccool:,

Avec la modification dans le trust center je n'ai plus le problème avec la référence

Maintenant, j'ai l'erreur suivante qui apparait 'subscript out of range'
11/10/2011, 12h41
drxel

en fait je n'ai plus le problème avec la référence
mais il n’écris rien dans module1 quand je vais voir sous visual basic

quelqu'un a-t'il une solution ?
11/10/2011, 13h12
fab256
Tu arrive a exécuter la macro ?

Code:

ExcelApplication.Run('test');

Il faut donner un nom aussi a ton module après le add

Code:

Module.Name := 'MonModule1';

Sinon si ton module existe déja
Code:

1 2 VBProj := ExcelApplication.VBE.ActiveVBProject; Module := VBProj.VBComponents('Module1');

en écrivant le code comme je le mettais plus haut, il n'ecrivait rien dans le module mais en mettant la macro dans un Memo et en le passant comme suit, ça fonctionne :
Code:

1 2 3 4 5 6 7 Module := ExcelApplication.VBE.ActiveVBProject.VBComponents.Add(1); Module.name:='MonModule'; for I :=0 to Form1.Memo1.Lines.Count-1 do begin Module.CodeModule.InsertLines(I+1,Form1.Memo1.Lines.Strings[I]); end; ExcelApplication.Run('MaMacro');
avec dans le Memo :
Code:

1 2 3 4 5 6 7 Sub MaMacro() ' 'MaMacro Macro ' ShowMsg("Bonjour") ' End Sub
Saurais-tu comment attacher ma macro MaMacro au click de mon bouton et comment changer le nom de ce bouton ?

Si ton Bouton s'appelle MaMacro alors son evenement click doit se nommer MaMacro_Click()
Code:

1 2 3 4 MaShape:=MaFeuille.Shapes.AddOLEObject('Forms.CommandButton.1', EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, 10,10, 110,40); MaShape.Name := 'MaMacro' MaShape.Object.Caption := 'Cliquer ici'
et ta macro devra etre comme ceci
Code:

1 2 3 4 5 6 7 Private Sub MaMacro_Click() ' 'MaMacro Macro ' ShowMsg("Bonjour") ' End Sub

11/10/2011, 14h56
drxel

dans,

Code:

MaShape.Object.Caption := 'Cliquer ici'

Il ne connait pas 'Object'. Quand je fais, 'MaShape.' le menu déroulant qui s'affiche ne propose pas 'Object'

J'avais trouve une autre façon de faire :

Code:

MaShape.TextFrame.Characters.Text := 'Cliquer ici';

mais il veut des paramètres a 'Characters' : Start et length :

Code:

MaShape.TextFrame.Characters(Start:=1, length:=11).Text := 'Cliquer ici';

et en les tournant dans tous les sens je n'arrive pas a le faire fonctionner : par exemple :

Code:

MaShape.TextFrame.Characters(1,11).Text := 'Cliquer ici';

Ca veut bien compiler mais a l’exécution il marque 'Member not found'

Tu peux créer et controller ton bouton dans une macro VBA et tu fait l'appel a partir de Delphi

Code:

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
Sub CreerBoutonEtCode() 
     ' Declarer les variables
    Dim i As Long, Hght As Long 
    Dim Name As String, NName As String 
     ' Definir les propriétés du button
    i = 0 
    Hght = 305.25 
     ' Definir le nom du bouton
    NName = "cmdAction" & i 
     ' Tester si le bouton existe alors incrementer son nom
    For Each OLEObject In ActiveSheet.OLEObjects 
        If Left(OLEObject.Name, 9) = "cmdAction" Then 
            Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9) 
            If Name >= i Then 
                i = Name + 1 
            End If 
            NName = "cmdAction" & i 
            Hght = Hght + 27 
        End If 
    Next 
     ' Ajouter button
    Dim myCmdObj As OLEObject, N% 
    Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ 
    Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _ 
    Width:=202.5, Height:=26.25) 
     ' Definir le nom du bouton
    myCmdObj.Name = NName 
     ' Define le text du buttons
    myCmdObj.Object.Caption = "Cliquer ici" 
     ' Inserer le code du clique pour le bouton
    With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule 
        N = .CountOfLines 
        .InsertLines N + 1, "Private Sub " & NName & "_Click()" 
        .InsertLines N + 2, vbNewLine 
        .InsertLines N + 3, vbTab & "MsgBox(" & """" & "Bouton ok!" & """" & " & vbCrLf &" & _ 
        """" & "Mettre le code ici!" & """" & " & vbCrLf & " & """" & "This is " & """" & _ 
        "& " & """" & NName & """" & ")" 
        .InsertLines N + 4, vbNewLine 
        .InsertLines N + 5, "End Sub" 
    End With 
End Sub

11/10/2011, 15h35
drxel

Effectivement, pourquoi pas le créer par une macro

Merci de m'avoir répondu.

Je marque le sujet comme résolu, mais si quelqu'un sait comment le faire depuis delphi sans passer par une macro (modifier le nom du bouton créé depuis delphi et lui affecter une macro), je suis preneur : juste pour avoir la solution.
11/10/2011, 15h54
fab256

Essai ceci :

Code:

MaShape.TextFrame.Characters(emptyparam,emptyparam).Caption := 'Cliquer ici';
11/10/2011, 16h06
drxel

J'ai toujours l'erreur : 'Member not found'

Dans ta macro précédente qui fonctionne bien, comment je peux lui dire de lancer une macro 'MaMacro()' que j'aurais défini préalablement au lieu de définir une macro complète
par exemple :

Code:

.InsertLines N + 3, vbTab & "MaMacro()"

ne fonctionne pas
11/10/2011, 16h13
fab256

Dans le code VBA, fait l'appel a ta macro avec Call ou bien juste le nom de la macro

Code:

.InsertLines N + 3, vbTab & "Call MaMacro()"

ou bien directement

Code:

.InsertLines N + 3, vbTab & "MaMacro()"
11/10/2011, 16h22
drxel

Code:

.InsertLines N + 3, vbTab & "Call MaMacro()"

fonctionne.

merci.
11/10/2011, 16h26
fab256

Je comprends pas pourquoi tu as l'erreur, quand tu écris dans l'éditeur de Delphi cette ligne

Code:

MaShape.TextFrame.Characters(emptyparam,emptyparam).

L'éditeur te propose une liste de propriétés, non? entre autre Caption dans cette liste ou non?
11/10/2011, 16h44
drxel

il me propose bien caption, je peux compiler mais a l’exécution il m'indique toujours 'member not found'
11/10/2011, 16h48
fab256

Peut etre il faut faire un cast, je ne suis pas sur mais a tester :

Code:

(MaShape as button).caption:='Cliquer ici';
12/10/2011, 08h37
drxel

Cette fois-ci j'ai l'erreur : 'Interface not supported'

Ca devrait marcher de cette facon :

Code:

1
2
3
4
5
6
7
var MaShape : variant;
...
...
MaShape := Excel.Application.Worksheets[1].Shapes.AddShape(1, 0, 0, 450, 200);
MaShape.textframe.characters.text:='Cliquer Ici';
...
...

Ou bien d'une autre maniere

Code:

1
2
3
4
5
6
7
8
9
10
var MaShape : variant;
...
...
MaShape := Excel.Application.Worksheets[1].ActiveSheet.Buttons.Add(159, 48, 132, 33);
MaShape.name:='MonBouton';
MaShape.OnAction = 'MaMacro';
 
Excel.Application.Worksheets[1].ActiveSheet.Shapes('MonBouton').Characters.Text = 'Cliquer Ici';
...
...

12/10/2011, 11h12
drxel

la première solution ne fonctionne pas

pour la 2eme la commande 'add' de 'buttons' n'est pas disponible

Tant pis ce n'est pas grave ça marche déjà très bien par ta macro

Merci
12/10/2011, 11h54
fab256
Pour le deuxieme code, tu peux le convertir en VBA comme cela
Code:

1 2 3 4 5 6 Sub CreerBoutonEtCode() Set btn = ActiveSheet.Buttons.Add(100, 200 , 100, 25) btn.name = "MonBouton" btn.OnAction = "MaMacro" btn.Characters.Text = "Cliquer ici " End Sub
Je pense que du point de vue conception, la solution d'utiliser Delphi seulement pour l'appel de la macro est meilleur, il vaut mieux garder tout ce qui est code VBA a l'interieur d'Excel (Code plus lisible, facile a écrire et a déboguer et a entretenir plus tard) vu que beaucoup de fonctions VBA ne sont pas implémentées sur Delphi.
Dans Excel, tu Fait enregistrer Macro, tu fait les taches que tu veux automatiser, ensuite fin enregistrement, tu analyse le code généré et tu l'optimise.

Je pense que finalement on pourrais faire comme cela plus simplement:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 var btn, xlApp, xlWorkBook, Module : variant; N: integer; begin xlApp := CreateOLEObject('Excel.Application'); xlApp.Visible := True; xlWorkBook := xlApp.Workbooks.Add; btn:=xlWorkBook.ActiveSheet.Buttons.Add(100, 200, 100, 25); btn.OnAction := 'MaMacro'; btn.Characters.Text := 'Cliquer ici '; Module := xlApp.VBE.ActiveVBProject.VBComponents.Add(1); Module.name:='MonModule'; N:=Module.CodeModule.CountOfLines; Module.CodeModule.InsertLines(N+1,'Private Sub MaMacro'); Module.CodeModule.InsertLines(N+2,'MsgBox "Bonjour"'); Module.CodeModule.InsertLines(N+3,'End Sub'); end;
Code testé dans D7 avec succés, un bouton est crée, nomé et la macro MaMacro est attaché au clic. Le source de MaMacro est crée dynamiquement dans Delphi, et tu peux le voir dans Excel, Editeur VBA dans la branche Modules sous le nom de MonModule.

12/10/2011, 16h15
drxel
Excuse de te déranger encore mais j'ai un dernier soucis

dans le code suivant
Code:

1 2 3 MonClasseur.SaveAs(Nomfichier, EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam, xlNoChange,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
comment je peux lui dire de sauvegarder dans le format 'Excel Macro Enabled Workbook'

Je suppose que tu parle du format xlsm
Code:

1 2 3 Nomfichier:='Fichier1.xlsm'; MonClasseur.SaveAs(Nomfichier, xlOpenXMLWorkbookMacroEnabled, EmptyParam,EmptyParam,EmptyParam,EmptyParam, xlNoChange,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
Si tu n'a pas la constante xlOpenXMLWorkbookMacroEnabled alors remplace-la par 52.

Mais je pense que tu peux faire plus simple (a tester)

Code:

MonClasseur.Saveas(Nomfichier, 52);

Voici les formats les plus connus d'Excel

Citation:

51 = xlOpenXMLWorkbook (without macro's in 2007, .xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, .xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, .xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, .xls)

13/10/2011, 07h48
drxel

Ça fonctionne.

Merci beaucoup pour toutes tes réponses.
13/10/2011, 08h40
drxel

je pense qu'une partie des problèmes de 'commande non disponible' venaient du fait que tu parlais OLE et variant alors que j'utilisais les composants Excel de l'onglet servers
Désolé de ne pas l'avoir préciser plus tôt

Tout fonctionne bien maintenant
Merci
13/10/2011, 16h25
fab256

Dans Excel, y'a deux facons de créer un bouton, par objet OLE ou par controle module, tu avais exposé la première option qui est plus compliqué et non implémenté completement dans Delphi apparament alors que la deuxieme option était plus simple.