Sujet Précédent Sujet Suivant

Lancer une macro sur tous les onglets du fichier

Résolu
alexpepe - Modifié par BunoCS le 5/04/2016 à 12:02
alexpepe Messages postés 20 Date d'inscription mercredi 30 mars 2016 Statut Membre Dernière intervention 14 avril 2016 - 11 avril 2016 à 09:49
Bonjour à tous,

J'ai besoin de votre aide car je suis novice en VBA.

J'ai un tableau excel dans l'onglet j'ai un onglet par mois.
Dans cette onglet se trouve des dates en colonnes B et des adresses mails en colonnes E.
J'ai une macro qui analyse les dates du premier onglet et si la date se situe entre 1 et 7 jours après aujourd'hui et si il n'y a pas de "X" en colonne G alors cela envoi un mail à l'adresse correspondante (le dernier point avec le "X" fait l'objet d'une autre question sur le forum: mettre X si condition respectée).

Je souhaite rendre cette macro automatique (un autre sujet sur le forum: lancer une macro automatique 1 fois par semaine).
Et surtout je souhaite que cette macro se fasse sur tous les onglets du fichiers.
Pour l'instant je n'arrive à la faire fonctionner que sur un onglet.

Merci d'avance pour votre aide.

Ps: pour infos voici mon code à l'heure actuel 

Sub TesteDate()
'envoie un mail si la date est dans 7 jours ou moins
Dim sSujet, sBody, sAdresseMail As String 'chaines pour le sujet, corps, adresse d'envoi
Dim duree As Date 'nbre de jours entre aujourd'hui et la date à tester
Dim Lig_Deb, Lig_Fin As Integer 'ligne de début, de fin
Dim sDates_Col, sMails_Col As String 'colonnes qui contiennent les dates à tester et les adresses mail
Dim I As Integer

'initialisation des constantes de la macro :
Lig_Deb = 2 'dans ma feuille Excel, les dates à tester commencent en ligne 2
sDates_Col = "B" ' et elles sont en colonne B ( 2 ième colonne)et les adresses mail sont en colonne E à côté

'initialisation des données du mail envoyé :
sSujet = "Visite médical"
sBody = "Votre agent doit passer sa visite médicale dans sept jours ou moins."

'Ligne de fin =1ère cellule vide dans la colonne des dates
Lig_Fin = Val(Range(sDates_Col & CStr(Lig_Deb)).End(xlDown).Row)

' boucle de test dans la plage des dates (=> )

For I = Lig_Deb To Lig_Fin
Range(sDates_Col & CStr(I)).Select 'activer la cellule testée
duree = ActiveCell.Value - Now  ' la date est dans la cellule active
If duree <= 7 And duree > 0 Then 'la date est dans 7 jours ou moins par rapport à aujourd'hui
sAdresseMail = ActiveCell.Offset(0, 3).Value 'l'adresse mail est dans trois colonne après offset (0,3)

' envoyer le mail :
CDO_SendMail sSujet, sBody, sAdresseMail
Else

End If

Next I

End Sub

Sub CDO_SendMail(ByVal sSujet As String, ByVal sBody As String, ByVal sAdresseMail As String)
'MARCHE IMPEC, sans demande de confirmation ;-)))))
'on peut préciser : le sujet, le corps , l'adresse mail, l'adresse de retour
Dim OutApp As Object
    Dim OutMail As Object
  
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = sAdresseMail
        .CC = ""
        .BCC = ""
        .Subject = sSujet
        .HtmlBody = sBody
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing



End Sub
A voir également:

3 réponses

Réponse 1 / 3
BunoCS Messages postés 15368 Date d'inscription lundi 11 juillet 2005 Statut Modérateur Dernière intervention 12 mai 2023 102
5 avril 2016 à 12:00
Hello,

J'ai besoin de votre aide car je suis novice en VBA.
Tu sais qu'il existe un sous-forum VBA? Je déplace encore cette fois-ci mais attention à la prochaine fois ;)
0
Réponse 2 / 3
ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 219
5 avril 2016 à 12:31
Bonjour,
Je vais me contenter de te répondre de la manière suivante :
Toutes les feuilles (en te rappelant que les onglets ne sont pas des feuilles, mais des "pointeurs" vers des feuilles) d'un classeur appartiennent à la collection Worksheets du classeur concerné.
On parcourt les objets X d'une collection Y par une boucle de type
For each X in Y
Je n'irai pas plus loin tant que je n'aurai pas vu ce que tu tentes sur cette base.

0
alexpepe Messages postés 20 Date d'inscription mercredi 30 mars 2016 Statut Membre Dernière intervention 14 avril 2016
5 avril 2016 à 15:24
alors en prenant note de l'infos, j'ai modifié le code comme cela 

Private Sub Workbook_Open()
'envoie un mail si la date est dans 7 jours ou moins
Dim sSujet As String
Dim sBody As String
Dim sAdresseMail As String 'chaines pour le sujet, corps, adresse d'envoi
Dim duree As Date 'nbre de jours entre aujourd'hui et la date à tester
Dim Lig_Deb As Integer
Dim Lig_Fin As Long 'ligne de début, de fin
Dim sDates_Col As String
Dim sMails_Col As String 'colonnes qui contiennent les dates à tester et les adresses mail
Dim sRappel_Col As String
Dim I As Integer
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
'initialisation des constantes de la macro :
Lig_Deb = 2 'dans ma feuille Excel, les dates à tester commencent en ligne 2
sDates_Col = "B" ' et elles sont en colonne B ( 2 ième colonne)et les adresses mail sont en colonne E à côté
sRappel_Col = "G"
'initialisation des données du mail envoyé :
sSujet = "Visite médical"
sBody = "Votre agent doit passer sa visite médicale dans sept jours ou moins."

'Ligne de fin = dernière cellule remplie dans la colonne des dates
Lig_Fin = Range("B" & Rows.Count).End(xlUp).Row

' boucle de test dans la plage des dates (=> )

For I = Lig_Deb To Lig_Fin
Range(sDates_Col & CStr(I)).Select 'activer la cellule testée
duree = ActiveCell.Value - Now ' la date est dans la cellule active
If duree <= 7 And duree > 0 And Range(sRappel_Col & CStr(I)).Value = "" Then 'la date est dans 7 jours ou moins par rapport à aujourd'hui et la case rappel est vide
sAdresseMail = ActiveCell.Offset(0, 3).Value 'l'adresse mail est dans trois colonne après offset (0,3)

' envoyer le mail :
CDO_SendMail sSujet, sBody, sAdresseMail
'mettre la date d'envoi dans la case rappel
Range(sRappel_Col & CStr(I)) = Now

Else

End If

Next I
Next ws

End Sub



Donc il est écrit dans ThisWorkbook avec Workbook open.
Et quand je lance le test ça ne le fait que sur une seule feuille.
0
alexpepe Messages postés 20 Date d'inscription mercredi 30 mars 2016 Statut Membre Dernière intervention 14 avril 2016
5 avril 2016 à 15:53
bon alors je suis arrivé à lancer la macro sur plusieurs onglets, donc j'obtiens le résultat souhaité.

voici mon code 
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Select


dernier point de détail, admettons que le fichier s'ouvre sur l'onglet 2, la macro se lance et analyse feuille par feuille et comme j'en ai 12 reste ouverte à la fin sur la 12ème feuille, est-il possible de revenir à la feuille qui était active à l'ouverture du fichier c 'est à dire la 2ème?

merci
0
ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 219 > alexpepe Messages postés 20 Date d'inscription mercredi 30 mars 2016 Statut Membre Dernière intervention 14 avril 2016
5 avril 2016 à 16:23
Ouais ?
Et tout cela pourquoi, selon toi ?
Il me semble t'avoir déjà dit (dans une autre de tes discussions) aujourd'hui-même que l'on ne travaillait pas à coup de "select", mais directement sur les objets de Excel, non ?
Si tu le faisais, tu resterais sur la feuille de départ, non ?
worksheets("Feuilx").range("A3").Value = "coucou"
écrit "coucou" en cellule A3 de la feuille Feuilx sans s'y rendre ...
0
alexpepe Messages postés 20 Date d'inscription mercredi 30 mars 2016 Statut Membre Dernière intervention 14 avril 2016 > ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018
7 avril 2016 à 13:27
oui je viens de voir ta réponse sur l'autre discussion et on ne cesse de tourner en rond autour du même problème,
tu me donnes des conseils, j'en prend note, je suis partisans de les mettre en oeuvre car j'ai envie d'arriver au bout avec ce fichier, mais je ne sais pas comment appliquer ce que tu me dit tout simplement. Tu sembles être hyper compétent sur le sujet, ça se ressent mais je n'arrive pas à comprendre comment faire.

je pense que c'est la ou tu m'explique le select 
For Each ws In ActiveWorkbook.Worksheets
ws.Select

j'ai trouvé cette ligne sur le net et c'est la seule qui fonctionne à peu près, je ne sais pas comment faire sans le select. comment le rentre volatile et qu'il aille voir dans chaque onglet qui existe.

Je comprend qu'il faut utiliser value et non select mais comment l'écrire.
0
ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 219
7 avril 2016 à 13:36
Je comprend qu'il faut utiliser value et non select mais comment l'écrire.

Que l'on me tue si j'ai dit une telle ineptie !
J'ai dit que si toto était un objet worksheets, Toto.range("A3") (par exemple était la cellule A3 de l'objet toto !...
Te rends-tu comptes de ce que tu cherches à comprendre de Z vers A et non, comme il se doit, d'apprendre de A à Z ?
Ne confonds pas ce forum avec un site d'apprentissage des rudiments, s'il te plait ..
0
Réponse 3 / 3
alexpepe Messages postés 20 Date d'inscription mercredi 30 mars 2016 Statut Membre Dernière intervention 14 avril 2016
11 avril 2016 à 09:49
Bonjour à tous,

avec de l'aide et différents forums je suis arrivé au bout de mes problèmes.

Pour que ça puisse servir au plus grands nombre voici le résumé de ce que je voulais faire suivi du code.

Dans un fichier Excel avec une feuille par mois, dans chaque feuilles des nom d'agents, des dates de visites, des mails du responsables et une date de rappel.

Le but de la macro est qu'à l'ouverture du fichier, elle analyse sur toutes les feuilles s'il y a des dates comprises entre aujourd'hui et dans 7 jours afin d'envoyer un mail de rappel au responsable si ce n'est pas déjà fait.

Voici le code: 
Sub Workbook_Open()
'envoie un mail au DPX si la date de VM est dans 7 jours ou moins
Dim sSujet As String 'sujet du mail
Dim sBody As String ' corps du mail
Dim sAdresseMail As String 'adresse mail du dpx
Dim duree As Date 'nbre de jours entre aujourd'hui et la date à tester
Dim Lig_Deb As Integer 'première ligne du tableau
Dim Lig_Fin As Long 'dernière ligne remplie du tableau
Dim sDates_Col As String ' colonne avec la date de VM
Dim sMails_Col As String 'colonne avec l'adresse mail du DPX
Dim sRappel_Col As String ' colonne avec la date de rappel
Dim I As Integer
Dim ws As Worksheet
Dim NomOnglet As String
Dim NomAgent_Col As String

'enregistrer le nom de l'onglet actif à l'ouverture du fichier
NomOnglet = ActiveWorkbook.ActiveSheet.Name

'pour tous les onglets dans le fichier
For Each ws In ActiveWorkbook.Worksheets
ws.Activate

'initialisation des constantes de la macro :
Lig_Deb = 2 'dans ma feuille Excel, les dates à tester commencent en ligne 2
sDates_Col = "B" ' et elles sont en colonne B ( 2 ième colonne)et les adresses mail sont en colonne E à côté
sRappel_Col = "G" 'colonne ou apparait la date d'envoi d'un rappel par mail
NomAgent_Col = "D" 'colonne ou apparait le nom de l'agent


'Ligne de fin = dernière cellule remplie dans la colonne des dates
Lig_Fin = Range("B" & Rows.Count).End(xlUp).Row

' boucle de test dans la plage des dates (=> )
'pour toutes les lignes de ligne du début à la derniere ligne remplie
For I = Lig_Deb To Lig_Fin
Range(sDates_Col & CStr(I)).Activate 'activer la cellule testée
duree = ActiveCell.Value - Now ' la date est dans la cellule active
If duree <= 7 And duree > 0 And Range(sRappel_Col & CStr(I)).Value = "" Then 'la date est dans 7 jours ou moins par rapport à aujourd'hui et la case rappel est vide
sAdresseMail = ActiveCell.Offset(0, 3).Value 'l'adresse mail est dans trois colonne après offset (0,3)

'initialisation des données du mail envoyé :
sSujet = "Visite médical"
sBody = "Votre agent " & Range(NomAgent_Col & CStr(I)) & " doit passer sa visite médicale le " & Range(sDates_Col & CStr(I)) & "."

' envoyer le mail :
CDO_SendMail sSujet, sBody, sAdresseMail

'mettre la date d'envoi dans la case rappel
Range(sRappel_Col & CStr(I)) = Now

Else

End If

Next I
Next ws

'retourner sur l'onglet actif à l'ouverture du fichier
Worksheets(NomOnglet).Activate


End Sub

Sub CDO_SendMail(ByVal sSujet As String, ByVal sBody As String, ByVal sAdresseMail As String)
'MARCHE IMPEC, sans demande de confirmation ;-)))))
'on peut préciser : le sujet, le corps , l'adresse mail, l'adresse de retour
Dim OutApp As Object
    Dim OutMail As Object
  
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = sAdresseMail
        .CC = ""
        .BCC = ""
        .Subject = sSujet
        .HtmlBody = sBody
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub



Je sais qu'il est possible d'apporter des améliorations quand à la manière dont j'ai utilisé certains arguments mais je n'ai pas réussi et ce code fonctionne parfaitement à l'heure actuelle.

En espérant qu'il puisse aider.
0
Rejoignez-nous