Complément xlam

Le 29/06/2021 à 11:09

Bonjour,

Je tente de créer un complément Excel pour utiliser une macro sur une grande quantité de fichier.

J'ai créée la macro dans ThisWorkbook pour utiliser les procédures Workbook_BeforeClose et Workbook_Open.

Je rencontre 2 problèmes:

-Disons que dans les procédures, j'utilise "With Sheets("test")" et que l'onglet "test" existe bien dans tous mes fichiers. Et bien la console m'affiche qu'il y a un problème à ce niveau : "l'indice n'appartient pas à la sélection"

-Si j'active ce complément, je rencontrerai toujours un problème avec les fichiers qui ne disposent pas de l'onglet "test". Comment pallier à ce problème?

Merci d'avance pour vos retours.

Le 29/06/2021 à 11:19

Bonjour,

D'après l'aide de microsoft sur ThisWorkbook :

Utilisez cette propriété pour faire référence au classeur qui contient votre code de macro. Il est impossible de faire référence à un classeur de macro complémentaire à partir de la macro complémentaire même sans utiliser ThisWorkbook. La propriété ActiveWorkbook renvoie le classeur appelant la macro complémentaire et non pas le classeur de macro complémentaire.

Pour votre second problème, testez si la feuille Test existe avant de lancer une quelconque action sur cet objet :

Sub TEST()
Dim TROUVE As Boolean, WS As Worksheet
TROUVE = False
For Each WS In ActiveWorkbook.Worksheets
    If WS.Name = "Test" Then TROUVE = True
Next WS
If TROUVE = False Then MsgBox "Onglet TEST non trouvé", vbCritical: Exit Sub
End Sub

Cdlt,

Le 29/06/2021 à 11:47

Merci pour la réponse,

Alors pour le premier problème, j'ai remplacé par ActiveWorkbook.Worksheets("test") et je rencontre le même problème

Pour le second: merci pour cette solution. Mais où dois-je mettre cette procédure car sans Workbook_open est-ce que la procédure se lancera automatiquement?

Le 29/06/2021 à 12:34

Bonjour,

Dans votre classeur xlam :
- Supprimez tout (ou archivez tout)
- Créez un module de classe nommé EVENTS et insérez y le code suivant :

Option Explicit
Private WithEvents App As Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Dim TROUVE As Boolean, WS As Worksheet
TROUVE = False
For Each WS In Wb.Worksheets
    If WS.Name = "Test" Then TROUVE = True
Next WS
If TROUVE = False Then MsgBox "Onglet TEST non trouvé", vbCritical: Exit Sub
Wb.Worksheets("Test").Activate 'A adapter
End Sub

- Dans le code du classeur xlam ThisWorbook insérez y le code suivant :

Option Explicit
Private XLApp As EVENTS

Private Sub Workbook_Open()
    Set XLApp = New EVENTS
End Sub

- Enregistrez

Si vous ouvrez désormais un classeur (en ayant activé au préalable votre complément xlam dans l'application EXCEL) alors un message d'erreur apparaît si la feuille test n'existe pas. Il ne vous reste qu'à adapter la macro avec le TROUVE à l'activation de la fenêtre en fonction de ce que vous voulez faire, soit remplacer la ligne avec le commentaire.

Vous ne pouvez pas exécuter une macro à l'ouverture d'un classeur en testant ses objets sachant qu'il n'est pas encore ouvert. On attend donc l'activation de la fenêtre (App_WindowActivate) afin d’exécuter le code.

Le module de classe sert à initier l'application EXCEL et donc le code du module ThisWorkbook xlam.

Je ne maîtrise pas trop les modules de classe donc je ne m'aventure pas plus loin, mais les tests sont fonctionnels chez moi. Pour plus de lecture je me suis inspiré de cette source.

Cdlt,

Le 29/06/2021 à 12:38

Bonjour,

@Ergotamine, tu en es aux modules de classe...

Dis donc, la progression a été fulgurante ! Félicitations

Le 29/06/2021 à 13:27

Bonjour JoyeuxNoel,

J'ai le temps en ce moment d'apprendre ... Mais non je suis trèèèèès loin de modules de classes (surtout que j'explore Python en parallèle). Mais vu que je voyais d'où venait la problématique, une recherche sur Google et je suis tombé sur ça. J'ai testé, adapté après quelques tatonement mais sans avoir d'explication claire sur l'ensemble des modifications apportées. Je sais ce que j'ai fait et pourquoi je l'ai fait, mais de là à le retransposer en langage clair ... C'est autre chose. Loin du niveau des membres à couleur de ce forum.

C'est pour ça que je ne m'aventurerais pas plus loin pour le moment.

En tout cas merci pour tes félicitations, ça fait plaisir et te retourne le compliment !

Cdlt,

Le 29/06/2021 à 14:04

@Ergotamine t'as solution me permet bien d'utiliser une macro à l'ouverture mais il y'a deux problèmes:

-les procédures se mettent en marchent à chaque activation de la feuille excel et non seulement à l'ouverture. Si j'ai une 2ème feuille et que je reviens sur la première, la procédure va de nouveau se lancer.

-le code que tu m'as proposé fonctionne pour une exécution à l'ouverture mais pas pour le code correspondant au Workbook_BeforeClose.

Pour plus de compréhension je mets mon code initial qui se trouvais dans le .xlam dans Thisworkbook. Le code permet d'écrire l'heure d'ouverture (au moment ou le fichier s'ouvre) et pareil pour l'heure de fermeture.

Option Explicit

Private Sub Workbook_Open()
Dim LR As Long
Dim i As Integer

With Sheets("file_log")

    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Now()

End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim LR As Long

With Sheets("file_log")

LR = .Range("A" & Rows.Count).End(xlUp).Row
If .Range("A" & LR).Value <> "" Then
    .Range("B" & LR).Value = Now()
End If

End With
End Sub
Le 29/06/2021 à 15:02

Bonjour,

Dans ce cas, conservez le code suivant dans ThisWorkbook de votre xlam :

Private XLApp As EVENTS

Private Sub Workbook_Open()
    Set XLApp = New EVENTS
End Sub

Et dans le module de classe EVENTS :

Option Explicit

Private WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Dim TROUVE As Boolean, WS As Worksheet, LR As Long
For Each WS In ActiveWorkbook.Worksheets
    If WS.Name = "file_log" Then TROUVE = True
Next WS
If TROUVE = False Then MsgBox "Onglet file_log non trouvé", vbCritical: Exit Sub
With ActiveWorkbook.Worksheets("file_log")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("B" & LR) = Now()
End With
ActiveWorkbook.Save
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim TROUVE As Boolean, WS As Worksheet, LR As Long
If Application.Workbooks.Count > 0 Then
    TROUVE = False
    For Each WS In Wb.Worksheets
        If WS.Name = "file_log" Then TROUVE = True
    Next WS
    If TROUVE = False Then MsgBox "Onglet file_log non trouvé", vbCritical: Exit Sub
    With Wb.Worksheets("file_log")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A" & LR + 1) = Now()
    End With
End If
End Sub

Private Sub Class_Initialize()
    Set App = Application
End Sub

Comme dit je ne maîtrise pas les modules de classe, donc je test. Dans ce cas-ci ça log bien les ouvertures et fermeture du fichier dans le cas où file_log existe, ce qui est, si j'ai bien compris, le résultat attendu.

Cdlt,

Edit : Je vous laisse modifier ou non la présence du message si la feuille est absente.

Le 30/06/2021 à 09:49

C'est parfait! c'est exactement ça. Merci pour le temps consacré.

Juste, sais-tu pourquoi la message box s'affiche plusieurs fois à la fermeture du fichier?

Le 30/06/2021 à 10:12

Bonjour,

J'ai mal placé mes codes. Dernière modif et vous êtes bon :

Module de classe du xlam :

Option Explicit

Private WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim TROUVE As Boolean, WS As Worksheet, LR As Long
If Application.Workbooks.Count > 0 Then
    TROUVE = False
    For Each WS In Wb.Worksheets
        If WS.Name = "file_log" Then TROUVE = True
    Next WS
    If TROUVE = False Then MsgBox "Onglet file_log non trouvé", vbCritical: Exit Sub
    With Wb.Worksheets("file_log")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A" & LR + 1) = Now()
    End With
End If
End Sub

Private Sub Class_Initialize()
    Set App = Application
End Sub

Module ThisWorkbook du xlam :

Option Explicit

Private XLApp As EVENTS

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim TROUVE As Boolean, WS As Worksheet, LR As Long
For Each WS In ActiveWorkbook.Worksheets
    If WS.Name = "file_log" Then TROUVE = True
Next WS
If TROUVE = False Then MsgBox "Onglet file_log non trouvé", vbCritical: Exit Sub
With ActiveWorkbook.Worksheets("file_log")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("B" & LR) = Now()
End With
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()
    Set XLApp = New EVENTS
End Sub

Cdlt,

Rechercher des sujets similaires à "complement xlam"