Ajouter une macro a un fichier Excel en vbaRésolu

Question

Bonjour à tous!

Je vous met en situation. J'utilise VB6 et Excel pour créer des rapport au format xls.

Je me demandais si il etait possible d'atacher une macro un fichier excel pour que lorsqu'on ouvre le fichier la macro soit dans le fichier.

Afin que le user puisse rouler la macro une fois la manipulation (utilisateur) terminé sur le fichier.

Je ne veux cepandant pas utiliser le classeur de macro personnel.

Merci en avance.

<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

cs_MPi · Accepted Answer

Et si tu tiens tout de même à ajouter du code directement dans le fichier Excel, regarde du côté de VBE

Le sujet revient de temps à autre sur ce site, mais habituellement, les gens l'utilise à partir de VBA et non VB... mais en principe, ça devrait bien fonctionner.

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI

bigfish_le vrai · Answer

Salut,

c'est pas tres claire :
Tu voudrias ecrire une macro dans un fichier excel a l'aide du VB6 ou tu voudrais ouvrir un fichier excel qui contient deja une macro et l'executer ?

A+

zen69 · Answer

Et bien je creer le fichier a partir d'une app VB6, pendant la creation je desire attacher un module de macro au fichier.

<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

zen69 · Answer

Voici plus de détails question d'éclairsir le tout.

J'ai un fichier Excel que j'ai creer a partir d'une app VB6.

Je désire au moment de la création du fichier, attacher une macro qui va ajouter des colonnes de calcul.

Mais le fichier etant volumineux +/- 10 000 lignes, +/- 80 colonnes.
Si j'ajoute les colonnes, directement au fichier lors de la création, il devient infernal d'essayer de travailler avec. (recalcule a chaques filtre insertion de ligne etc).

Donc si je peux attacher un fichier macro au fichier les utilisateurs vont pouvoir une fois la manipulation du fichier terminée, roullé la macro qui ajoute les colonnes de calcul (qui sont utilisé seulement au moment de l'impression du fichier)

J'espere etre plus clair.... si vous ne comprennez pas encore mon charabiat... faites-moi signe :P

<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

us_30 · Answer

... et dans les deux cas, c'est possible. Reste à bien comprendre la situation.

Amicalement,
Us.

bigfish_le vrai · Answer

...
comment tu crees ton fichier excel a partir du VB6 
donne nous juste le bout de code avec le quel tu crees le fichier. Rien de plus je n'ai pas besoin de savoir comment tu le remplis etc

A+

3ddI7IHd

us_30 · Answer

dsl, mon intervention vient en décalage... 

Je ne ferais qu'une remarque, on peut très bien désactiver le calcul automatique... pour le remettre à la fin. Sans compter d'autre possibilité d'optimisation. Ce qui évitera de rajouter une macro, qui de toute manière sera aussi lente que le pilotage par VB6 sans optimisation...

Amicalement,
Us.

zen69 · Answer

us_30 : non je ne peux pas enlever les calcul automatique, car certain champs doivent absolument etre calculer au moment d'utilisation du fichier. D'autre (ceux que je veux mettre dans une macro), ne sont utilisé qu'une fois la manipulation (coté utilisateur) est terminée. 

bigfish_le vrai  : Le code je ne l'ai pas en ce moment, parce que je suis à la maison. Demain je te reviens ladessus demain.

MPi : Merci, je jette un coup d'oeil demain et si ca correspond à ce que je cherche, alors j'accepterai ta réponse.

Merci à tous.

<hr width="100%" size="2" />
  zen69 aka Ortho Le Profett
  [site web]

zen69 · Answer

bigfish_le vrai:



Voici donc la partie creation du fichier:

'CREER LE FICHIER UTILISÉ
    rFile =  App.Path & "\TEMP\QPTEMP.XLS"
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.copyfile emptyXls, rFile, True
  
    .... code tronqué ....
  
    'PRÉPARE EXCEL
    Dim appExcel As Excel.Application
    Dim fichierExcel As Excel.Workbook
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = False
    appXLv = True
    appExcel.AlertBeforeOverwriting = False
    appExcel.DisplayAlerts = False
    Set fichierExcel = appExcel.Workbooks.Open(FileName:=rFile)

 MPi :  je jette un coups d'oeil a VBE

<hr size ="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

bigfish_le vrai · Answer

Bonjour,

ta methode est bonne mais je suis d'accord avec US_30. 
pour desactivé le calcul manuel tu utilises :
appExcel.Application.Calculation =  xlCalculationManual

et si par la suite tu as besoin de calculer tu peux le faire ponctuelement par :

 appExcel.Application.Calculate 

ou

appExcel.Worksheets("sheet1").Calculate

ou, si tu veux localiser les calculs pour gagner en rapidité

appExcel.Worksheets("sheet1").range("A1:C100").Calculate

ou bien encore recalculer que les celules qui contiennes quelque chose dans une zone precise

appExcel.Worksheets("sheet1").UsedRange.Colums("A:C").Calculate

Et pour remetre le calcul en auto :

appExcel.Application.Calculation  = xlCalculationManualAutomatic

Vala,

A+

zen69 · Answer

MPi : VBE semble effectivement etre la solution a mon probleme.

J'ai fait quelque recherches et je suis tombé sur ce bout de code:

Private Function exportMacro()
    'Dimension variables
    Dim strCode As String
    Dim vbCom As VBComponent
    Dim modObj As Object
    
    'Set object to the module you want to export.
    Set modObj = _
    Application.VBE.ActiveVBProject.VBComponents.Item("macroTest")
    'Place code in a string.
    strCode = modObj.CodeModule.Lines(1, modObj.CodeModule.CountOfLines)
    'Create new workbook.
    Application.Workbooks.Add
    'Create a new module in workbook.
    Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
    'Add code to new module from string variable.
    Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _
        .CodeModule.AddFromString (strCode)

End Function

Cependant, chaque fois que je tente d'appeler la fonction, je reçoit une erreur du type "out of range".

Donc selon ce que j'ai compris, ce code prend un module existant dans mon application VB et l'attache a un workbook Excel.

Donc, 

Application.VBE.ActiveVBProject.VBComponents.Item("macroTest")
Ici macroTest est le module que je veux exporter/importer de vb vers excel
Et Application corespond a mon application VB.

Est-ce que je suis dans l'erreur....?

A l'aide svp!

<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

zen69 · Answer

bigfish_le vrai: C'est bien beau tout ca... mais le niveau de comprehension de mes users n'est pas forcément tres elever. Donc je prefere utilisé une macro qui va me permettre de faire cette opération.
<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

zen69 · Answer

Et je prefere la methode que j'avais imaginé, afin d'elargir mes connaissances... et m'initier au VBE.

Merci encore,

<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

bigfish_le vrai · Answer

...

si je reprend ton explication :

J'ai un fichier Excel que j'ai creer a partir d'une app VB6.

Je désire au moment de la création du fichier, attacher une macro qui va ajouter des colonnes de calcul.

Mais le fichier etant volumineux +/- 10 000 lignes, +/- 80 colonnes.
Si
j'ajoute les colonnes, directement au fichier lors de la création, il
devient infernal d'essayer de travailler avec. (recalcule a chaques
filtre insertion de ligne etc).

c'est ton appli ou l'utilisateur qui cree ce fichier ?
Moi ici je ce que je comprend, c'est que c'est ton aplli qui a un probleme de lenteur lors de la creation du fichier. Mon explication cherche a resoudre ce probleme. Je ne t'ai donner que du code pour ton appli VB6 et non pas pour excel meme si ce sont des commandes excel.

appExcel.Application.Calculate ' ici appExcel est bien cree par ton appli VB6... oui/non ?

Tu ne nous a pas tout dis, dans c'est conditions il est difficile de repondre correctement a ton probleme.

A+

us_30 · Answer

Bonsoir,

Bigfish_le_vrai, pour ma part je te suis entiérement sur tes explications... 

Zen69, il est certain que tu manque de précision dans tes explications. De plus tu dérives vers VBE dans ta recherche, et forcément, comme on a pas toutes les billes, on ne sait plus trop où tu vas... mais tu reste actif, ce qui es un très bon point. 

Mais si ton problème est avant tout un problème de rapidité, comme tu nous le disait, (re-souligné par Bigfish), alors la solution passe par l'optimisation, et non par faire une macro dans Excel piloté par VB6...

Piloter une création d'un module dans Excel, par VB6, comme ton titre du post le sugère, (ce qui à conditionné la réponse de MPi), est une chose assez rare, mais surtout très spécifique... il faut déjà possèder une bonne connaissance de la programmation pour que cela soit concluant. (je ne dis pas que tu n'arrivera pas à faire tout de même quelque chose...)... et si possède ces bonnes connaissances, tu saura nécessairement résoudre un problème d'optimisation, beaucoup simple... ce qui semble être ton problème...

Je dis cela, non pas pour te décourager d'aucune manière, dans la découverte de VBE, mais pour te montrer que tes explications amène plus à penser à une solution plus classique...

Bon courage à tous,
Amicalement,
Us.

cs_MPi · Answer

Je suis d'accord avec US et BigFish concernant l'optimisation du code. Ça pourrait régler pas mal le problème, même si je sais que l'Automation n'est pas aussi rapide que l'application elle-même...

Mais bon, j'ai voulu m'amuser un peu du côté de VBE et j'ai modifié un peu ton code. Note que j'ai tout fait à partir d'Excel et non VB6.

Private Function exportMacro()
    'Dimension variables
    Dim strCode As String
    Dim vbCom As VBComponent
    Dim modObj As Object
   
    'Set object to the module you want to export.
    Set modObj = _
    Application.VBE.ActiveVBProject.VBComponents.Item("Module1")
    'Place code in a string.
    'Ici, j'ai mis 2 au lieu de 1 pour ne pas avoir "Option Explicit" en double
    strCode = modObj.CodeModule.Lines(2, modObj.CodeModule.CountOfLines)
    'Create new workbook.
    Application.Workbooks.Add
    'Create a new module in workbook.
    ActiveWorkbook.VBProject.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
    'Add code to new module from string variable.
    'L'utilisation de CodePanes me semble nécessaire ici...
    ActiveWorkbook.VBProject.VBE.CodePanes(1).CodeModule.AddFromString (strCode)

End Function

Je ne sais pas si le Activeworkbook a changé quelque chose, mais le CodePanes oui... Et pour ton erreur, c'est possiblement le nom du module qui n'est pas bon. Il faut faire une distinction entre le module et la procédure contenue dans le module.

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI

zen69 · Answer

bigfish_le vrai: du coté création il n'y a aucun problèmes, la création du fichier prend environ 1:15, dont 55 sec pour la requete SQL. Donc de ce coté tout est ok.

us_30: Le probleme de rapidité est au moment de l'utilisation et non au moment de la création du fichier. Eg, lorsque l'on filtre ou que l'on enleve un filtre la recalculation des colonnes prend un temps fou.

MPi: Tu dis avoir tout fait du coté Excel? Je ne comprend plus alors, car moi du côté excel le code bloque à cette ligne : Dim vbCom As VBComponent. Et si tu a tous fait apartir d'excel c'est donc que tu dois avoir le module deja présent afin de le copier? Si c'est le cas je ne vois pas l'utilité... Svp, eclaire ma lanterne :P

Merci,

<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

zen69 · Answer

MPi: À vrai dire ce code n'est pas le mien, et je ne comprends pas tout a fait son fonctionnement. 

Le code viens de Microsoft: 
http://support.microsoft.com/kb/245801/appliesto

Peux-tu m'aider un peu avec ce VBE qui m'est étranger?

<hr size="2" width="100%" />  zen69 aka Ortho Le Profett
  [site web]

cs_MPi · Answer

Je ne suis pas certain de comprendre le sens de ta question (?)

Si tu es sous VB6, tu as besoin de cette référence pour utiliser VBE. Une fois ton classeur Excel créé avec le module et son code ajouté, c'en est fini de VBE et de cette référence. Le classeur Excel, de son côté, n'en aura pas besoin.

Si tu n'utilises pas VB6, mais tout à partir de VBA Excel, tu auras besoin de la référence seulement dans le classeur principal qui sert à créer le nouveau classeur...

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI

cs_MPi · Answer

Tu n'as pas besoin de créer un module vide pour y insérer du code.
Crée le module sous VB6 puis importe-le directement sous Excel avec la méthode AddFromFile. Si comme moi, Option Explicit s'inscrit par défaut, enlève-le pour éviter les doublons à la création dans le classeur.

Private Sub Command1_Click()
    Dim xlApp As New Excel.Application
   
    'Créer un nouveau classeur
    xlApp.Workbooks.Add
    
    'Ajouter un module à ce classeur (actif)
    xlApp.VBE.ActiveVBProject.VBComponents.Add _
            (vbext_ct_StdModule)
    
    'Insérer dans Excel un module déjà créé sous VB6
    xlApp.VBE.CodePanes(1).CodeModule.AddFromFile _
            App.Path & "\Module1.bas"
    
    'Sauvegarde du classeur
    xlApp.ActiveWorkbook.SaveAs "C:\TestVBE.xls"
    
    'Fermer l'application
    xlApp.Quit
    Set xlApp = Nothing

End Sub

Et pour continuer la discussion sur la nécessité de ce code VBE, est-ce que tu as fais des tests sous VB et sous VBA pour comparer la vitesse d'exécution dans les 2 cas ? Est-ce que c'est vraiment plus rapide sous VBA avec un code identique ? Et quelle partie du code semble être la plus lente ?

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI

Ajouter une macro a un fichier Excel en vba

26 réponses

Votre réponse

Discussions similaires