[VBA] Boucle en VBA

nat54

nat54

XLDnaute Barbatruc
Bonjour,

J'ai finalisé mon code VBA qui marche pour un pôle en question (un des 31 fichiers tableau de bord)

Ci-dessous le code :

Code: 
Sub Construire_fichiers()
 
 
    ''' Test sur pôle 3945 (à voir plus tard pour une boucle)
    Workbooks.Open Filename:= _
    "R:\ECHANGE\Tableau_de_bord_RH\3945 - PEDIATRIE\TdB_RH_3945_année_2011-2012.xls", _
    WriteResPassword:="wxcvbn", _
    IgnoreReadOnlyRecommended:=True
 
       
    ''' Déprotéger classeur
    Application.Run "'TdB_RH_3945_année_2011-2012.xls'!DeProtegeClasseur"
   
   
    ''' Rendre visible les onglets d'export
    Windows("TdB_RH_3945_année_2011-2012.xls").Activate
    Sheets("export_HUS_abs").Visible = True
    Sheets("export_HUS_gestor").Visible = True
    Sheets("export_abs_N-1").Visible = True
    Sheets("export_gestor_N-1").Visible = True
   
    ''' Pour moyenne absentéisme HUS
    Sheets("export_HUS_abs").Select
    Range("a2:u" & Range("u65536").End(xlUp).Offset(1, 0).Row).Select
    Selection.ClearContents
    Windows("Export_BO_ABS_HUS.xls").Activate
    Range("a1:u" & Range("u65536").End(xlUp).Offset(1, 0).Row).Select
    Selection.Copy
    Windows("TdB_RH_3945_année_2011-2012.xls").Activate
    Sheets("export_HUS_abs").Select
    Range("A65536").End(xlUp).Select
    ActiveSheet.Paste
   
   
    ''' Pour moyenne gestor HUS
    Windows("Export_BO_GESTOR_HUS.xls").Activate
    Range("a1:k" & Range("k65536").End(xlUp).Offset(0, 0).Row).Select
    Selection.Copy
    Windows("TdB_RH_3945_année_2011-2012.xls").Activate
    Sheets("export_HUS_gestor").Select
    Range("A65536").End(xlUp).Select
    ActiveSheet.Paste
   
  
    ''' Pour absentéisme du pôle test 3945 (à voir pour une boucle plus tard)
    Windows("TdB_RH_3945_année_2011-2012.xls").Activate
    Sheets("export_abs").Select
    Range("a2:ad" & Range("ad65536").End(xlUp).Offset(1, 0).Row).Select
    Selection.ClearContents
    Windows("Export_BO_ABS_nominatif.xls").Activate
    Selection.AutoFilter Field:=4, Criteria1:="3945"  'field 4 = colonne D, pôle 3945
    Range("a2:ad10000").Select
    Selection.Copy
    Windows("TdB_RH_3945_année_2011-2012.xls").Activate
    Sheets("export_abs").Select
    Range("a2:ad" & Range("ad65536").End(xlUp).Offset(1, 0).Row).Select
    ActiveSheet.Paste
 
   
   
     ''' Pour gestor du pôle test 3945 (à voir pour une boucle plus tard)
    Windows("Export_BO_GESTOR_nominatif.xls").Activate
    Selection.AutoFilter Field:=5, Criteria1:="3945"  'field 5 = colonne E, pôle 3945
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("TdB_RH_3945_année_2011-2012.xls").Activate
    Sheets("export_gestor").Select
    x = Selection.Rows.Count + 2 '+1 si pas de ligne de titre, +2 si ligne de titre
    Range("A" & x).Select
    ActiveSheet.Paste
       
 
    ''' Reprotéger classeur
    Application.Run "'TdB_RH_3945_année_2011-2012.xls'!ProtegeClasseur"
   
   
    ''' Masquer les onglets export HUS (gestor et abs), export N-1 (gestor et abs)
    Sheets("export_HUS_abs").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("export_HUS_gestor").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("export_abs_N-1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("export_gestor_N-1").Select
    ActiveWindow.SelectedSheets.Visible = False
   
     
    ''' Sauvegarder le TdB RH
    ActiveWorkbook.Close True 'true = sauvegarde les changements
 
 
    ''' Fermer les classeurs d'export
    Windows("Export_BO_ABS_HUS.xls").Activate
    ActiveWorkbook.Close False
    Windows("Export_BO_GESTOR_HUS.xls").Activate
    ActiveWorkbook.Close False
    Windows("Export_BO_ABS_nominatif.xls").Activate
    ActiveWorkbook.Close False
    Windows("Export_BO_GESTOR_nominatif.xls").Activate
    ActiveWorkbook.Close False
 
End Sub






Le code fonctionne parfaitement.

J'aimerai donc pouvoir maintenant "boucler" pour faire tous les fichiers en une fois.
Chaque fichier est nommé de la même manière : TdB_RH_3945_année_2011-2012.xls
où 3945 correspond au numéro du pôle.

Toutes les actions doivent se faire sur chacun des pôles.
Le prochain à traiter est le 3580.

Y a t'il moyen de variabiliser cela ?

Contrainte supplémentaire : le chemin d'accès au fichier ... :(
- le pôle 3945 se situe dans le répertoire R:\ECHANGE\Tableau_de_bord_RH\3945 - PEDIATRIE
- le pôle 3580 se situe dans le répertoire R:\ECHANGE\Tableau_de_bord_RH\3580 - ODONTOLOGIE
Y a t il moyen de trouver le chemin en disant quelque chose du genre "contient 3580" ?


Merci d'avance !


edit : je voulais mettre en couleur les endroits à variabiliser mais ça fait planter les balises code du forum...

edit 2 : le synopsis si ça peut aider.. quoique.. :d
http://www.heberger-image.frhttp://www.heberger-image.fr
 
Dernière édition:
Trier par date Trier par votes
V

Vorens

XLDnaute Occasionnel
Re : [VBA] Boucle en VBA

Hello,

Le plus simple est de faire un mapping dans une feuilles de ton classeur. Ensuite, tu boucle sur chaque élément de ton mapping avec dans chaque colonne une infos utile comme par exemple le nom du classeur, le chemin d'acces etc. De cette façon si tu veux ajouter / supprimer / modifier ton automatisme il te suffis seulement de modifier ton mapping.

Donc pour Variabliser comme tu dis, c'est pas compliquer, par exemple pour ouvrir ton fichier cela donne:

Code: 
MaVariablePath= "R:\ECHANGE\Tableau_de_bord_RH\3945 - PEDIATRIE\TdB_RH_3945_année_2011-2012.xls"

Workbooks.Open Filename:=MaVariablePath

Autre exemple:

Code: 
Sheets("export_HUS_abs").Select

Devient

Code: 
MaVariableNomFeuille = "Feuil1"
Sheets(MaVariableNomFeuille).Select

Etc etc,


Attaquons nous à la boucle.

Plusieurs choix possible, on peux prendre par exemple une boucle for

Code: 
FinTableauMapping = sheets("Feuille_Mapping").range("A" & "65535").end (xlup).row

For i = 2 to FinTableauMapping

FichierTraite = sheets("Feuille_Mapping").range("A" & i).value
PathFichier = Sheets("Feuille_Mapping").range("B" & i) & FichierTraite 


'Ton code


next


Explication de ce qu'est un mapping:

Dnas une feuille de paramètre tu créer un tableau par exemple en colonne A le nom de tes fichiers

Toto
Titi
Tata
etc..

En colonne B, le path (chemin d'acces) de tes fichier ton tableau debient

Toto.xls / C:\\temp\
titi.xls / C:\\User\

etc,,,


Voila, si tu as des questions hésite pas :) mais sans fichier exemple on aura de mal à t'aider plus que part des propositions telle que celle que je présente.

Meilleures salutations
 
Vote positif 0 Vote négatif
nat54

nat54

XLDnaute Barbatruc
Re : [VBA] Boucle en VBA

Merci pour ces éléments, je teste et reviens vers vous en cas de souci.

Le problème, pour vous fournir, des fichiers est de les construire anonymisés et vous n'aurez pas la structure des répertoires...
 
Vote positif 0 Vote négatif
V

Vorens

XLDnaute Occasionnel
Re : [VBA] Boucle en VBA

Re,

Oui pour ce genre de problème c'est toujours compliquer d'envoyer les fichiers. Par conséquent, on ne peut donner que des indications sur la façon de résoudre le problème donc cela prend plus de temps et demande plus de débrouille :)
 
Vote positif 0 Vote négatif
nat54

nat54

XLDnaute Barbatruc
Re : [VBA] Boucle en VBA

Surtout que je débute en VBA...

Justement j'ai déjà une question

1. J'ai fait le mapping ci-dessous
http://www.heberger-image.fr

2. Je ne comprends pas où utiliser votre code
Code: 
MaVariablePath= "R:\ECHANGE\Tableau_de_bord_RH\3945 - PEDIATRIE\TdB_RH_3945_année_2011-2012.xls"

Workbooks.Open Filename:=MaVariablePath
comme il ne fait pas référence au mapping :huh:

et si je prends un bout de mon code, par exemple
Code: 
    Windows("TdB_RH_3945_année_2011-2012.xls").Activate
    Sheets("export_HUS_abs").Visible = True
    Sheets("export_HUS_gestor").Visible = True
    Sheets("export_abs_N-1").Visible = True
    Sheets("export_gestor_N-1").Visible = True


comment préciser que je dois prendre le 1er path de la boucle au lieu du fichier écrit en toutes lettres ?
car c'est en fait le nom du fichier qui revient tout le temps...
 
Vote positif 0 Vote négatif
V

Vorens

XLDnaute Occasionnel
Re : [VBA] Boucle en VBA

Re,


C'était des exemples vu que tu demandais comment mettre des variables.

Je vais être plus précis:

Prend mon exemple suivant:

Code: 
FinTableauMapping = sheets("Feuille_Mapping").range("A" & "65535").end (xlup).row

For i = 2 to FinTableauMapping

FichierTraite = sheets("Feuille_Mapping").range("A" & i).value
PathFichier = Sheets("Feuille_Mapping").range("B" & i) & FichierTraite 


'Ton code


next

La première ligne définit la grandeur du tableau de ton mapping (nombre de lignes)

Ensuite, la 2 eme ligne prend l'information de la cellule A et le numéro de la ligne correspondant a l'itération de la boucle. Exemple lors du premier passage dans la boucle se sera A2 (car y'a une en-tête dans le tableau) A la 2 eme se sera A3 car la valeur de i sera de 3 etc etc

Pour le path c'est la 3 eme ligne qui effectue une concaténation entre la colonne A et la colonne B donc du Math et du fichier. La concaténation se fait via le symbole "&"

Donc par rapport au tableau que tu présente: en cellule A2 on a "TdB_RH_3945_année_2011-2012.xls"
En B2 on a "R:\ECHANGE\Tableau_de_bord_RH\3945 - PEDIATRIE\"

Puis avec la concaténation on obtient

R:\ECHANGE\Tableau_de_bord_RH\3945 - PEDIATRIE\TdB_RH_3945_année_2011-2012.xls

Cette information est stockée dans la variable PathFichier.

Donc à partir de là, on peut ouvrir ton fichier avec le code suivant

Code: 
Dim F_CurrentCata As Workbook
Application.DisplayAlerts = False
Set F_CurrentCata = Workbooks.Open(PathFichier)


A chaque itération de la boucle (qui s’exécutera un nombre n fois en raport au nombre de ligne de ton tableau de mapping) , ta variable PathFicher changera toute seule.

J’espère avoir été claire et précis :)


Meilleures salutaitons
 
Vote positif 0 Vote négatif
V

Vorens

XLDnaute Occasionnel
Re : [VBA] Boucle en VBA

Re,

Pour ta deuxième question, prend l'habitude de pas utiliser les .select ou .activate, sa ralenti énormément le code. Ecrit le plus comme sa.

Code: 
    With Workbooks(FichierTraite)
        
        .Sheets("export_HUS_abs").Visible = True
        .Sheets("export_HUS_gestor").Visible = True
        .Sheets("export_abs_N-1").Visible = True
        .Sheets("export_gestor_N-1").Visible = True
        
    End With
 
Vote positif 0 Vote négatif
nat54

nat54

XLDnaute Barbatruc
Re : [VBA] Boucle en VBA

Re,

Merci

Ca marche plutôt pas mal sauf que je ne connais pas la syntaxe pour le ApplicationRun
Application.Run "FichierTraite!'DeProtegeClasseur'"
ne marche pas car, du coup, il cherche FichierTraite.xls
il faut que je prenne la value, je teste
 
Dernière édition:
Vote positif 0 Vote négatif
nat54

nat54

XLDnaute Barbatruc
Re : [VBA] Boucle en VBA

Je me suis mal exprimée..
En fait la macro DeprotegeClasseur est directement dans chaque fichier Tableau de Bord

Je n'arrive pas à appeler la macro depuis qu'on a variabilisé le nom du fichier
Code: 
    Application.Run "'TdB_RH_3945_année_2011-2012.xls'!DeProtegeClasseur"
marchait

Mais comment intégrer la variable FichierTraite ?

Merci
 
Vote positif 0 Vote négatif
V

Vorens

XLDnaute Occasionnel
Re : [VBA] Boucle en VBA

Re,


Oui excuse moi j'avais pas fait attention que c’était pour lancer une macro distante :/

En fait lorsque tu utilise une variable comme nom de fichier c'est le même principe que lorsque tu utilise une variable pour un range.

Il faut reconstituer le string par la concaténation des différents éléments- Cela donne quelque chose comme sa.

Code: 
Application.Run FichierTraite & "!" & "'DeProtegeClasseur'"

J'ai pas testé si il faut laisser les ' ' entre le nom de la macro, test avec si sa marche pas test sans si sa marche bah redis moi et on regarde :)
 
Vote positif 0 Vote négatif
nat54

nat54

XLDnaute Barbatruc
Re : [VBA] Boucle en VBA

La 1ère boucle fonctionne nickel, mais ne marche pas pour le 2nd fichier

Debogage sur la ligne
Code: 
FichierTraite = Sheets("Mapping").Range("A" & i).Value

Erreur d'exécution 9
L'indice n'appartient pas à la sélection
 
Vote positif 0 Vote négatif

Discussions similaires

B
  • Question
XL 2016 Interférence entre une boucle et un copie de feuille
Réponses
4
Affichages
364
Oneida
Oneida
M
  • Question
Microsoft 365 Macro VBA supprimer une colonne
Réponses
5
Affichages
132
marie3107
M
T
  • Question
XL 2016 Optimisation d'une boucle
2 3
Réponses
30
Affichages
1 K
thivame
T
mouchtre
  • Résolu(e)
Microsoft 365 Macro fonctionnant avec excel 365 sur windows 11 mais ne fonctionnent pas sur un autre windows 11 avec aussi un excel 365
Réponses
11
Affichages
567
mouchtre
mouchtre
M
  • Résolu(e)
XL 2016 Amélioration de code
Réponses
3
Affichages
380
M@xu3L
M
C
  • Question
Microsoft 365 Arret macro si condition est fausse
Réponses
4
Affichages
402
Chounoé
C
M
  • Question
Microsoft 365 Copier / coller avec mise en forme VBA
Réponses
0
Affichages
190
marie3107
M
X
  • Question
XL 2016 Copier à partir de la dernière ligne renseignée.
Réponses
8
Affichages
452
Xender
X
L
  • Question
VBA : erreur sur boucle
Réponses
2
Affichages
102
Latias
L
A
  • Résolu(e)
XL 2016 Autofill Destination sur colonne adjacente
Réponses
3
Affichages
369
Aldonanou
A

Membres actuellement en ligne

15 membres et 100 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 391
Messages
2 087 941
Membres
103 679
dernier inscrit
yprivey3