Discussion :

[gerard13]

Bonjour,

Je développe un script afin de copier une feuille source dans des classeurs cibles.

Dans mon dossier "c:\Test", j'ai mon classeur VBA_Ajout_Feuil.xlsm contenant mon code.

J'y ai un classeur "Feuil_a_inserer.xlsx" contenant une feuille nommée "Feuil_en_plus".

J'y ai un classeur "Liste_classeurs.xlsm" renseigné comme ci-dessous.

	A	B	C
2	Classeur1.xlsx	10/10/2020	C:\Test\Classeurs
3	Classeur2.xlsx	10/10/2020	C:\Test\Classeurs
4	Classeur3.xlsx	10/10/2020	C:\Test\Classeurs
5	Classeur4.xlsx	10/10/2020	C:\Test\Classeurs
6	Classeur5.xlsx	10/10/2020	C:\Test\Classeurs

J'ai un dossier c:\Test\Classeurs contenant les 5 classeurs nommés Classeur1.xlsx à Classeur5.xlsx.

Chaque classeur a une feuille nommée "Feuil1".

J'ai un bug sur la ligne 37 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Workbooks(Fichier_source).Sheets(Feuil_source).Copy After:=Workbooks(Nom_du_classeur).Sheets("Feuil1")

Erreur d 'exécution '9':
L'indice n'appartient pas à la sélection

Et pourtant, ça a fonctionné au début et puis plus rien, je ne comprends pas, les valeur des variables sont ok en mode débug...

J'ai le sentiment que autre chose bloque... mais quoi

Merci d'avance de votre aide ou vos conseils,

Voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
42
43
44
45
46
47
48
Sub Ajout_feuil()
Dim WB As Workbook
Dim WS As Worksheet
Dim Liste_classeurs As String
Dim Ligne As Long
Dim Dossier_du_classeur As String
Dim Chemin_du_classeur As String
Dim Nom_du_classeur As String
Dim Fichier_source As String
Dim Feuil_source As String
 
Ligne = 2 ' Ici on pointe la ligne sur laquelle on démarre la lecture du classeur Liste_Classeurs
Liste_classeurs = "C:\Test\Liste_classeurs.xlsm" 'Chemin du fichier Liste_classeurs.xlsm
Fichier_source = "C:\Test\Feuil_a_inserer.xlsx"
Feuil_source = "Feuil_en_plus"
 
 
Workbooks.Open (Fichier_source)
Set WS = ActiveWorkbook.Worksheets(Feuil_source) 'Activation de la feuille
Set WS = Nothing
 
Workbooks.Open (Liste_classeurs) 'Ouverture du classeur liste des classeurs
Set WS = ActiveWorkbook.Worksheets("Feuil1") 'Activation de la feuil 1
Set WS = Nothing
 
    While Cells(Ligne, 1) <> "" ' On reboucle jusqu'à lire une ligne vide en colonne 1
 
    Nom_du_classeur = "" 'on efface toute valeur de la variable
    Chemin_du_classeur = "" 'on efface toute valeur de la variable
 
    Nom_du_classeur = Cells(Ligne, 1).Value ' Sélection du classeur cible dans la liste des classeurs
    Dossier_du_classeur = Cells(Ligne, 3).Value ' Sélection du chemin du classeur
    Chemin_du_classeur = (Dossier_du_classeur & "\" & Nom_du_classeur) ' Création du chemin du classeur avec son nom
 
    Workbooks.Open (Chemin_du_classeur) ' Ouverture du classeur cible
    Workbooks(Fichier_source).Sheets(Feuil_source).Copy After:=Workbooks(Nom_du_classeur).Sheets("Feuil1") 'On copie la feuille source à ajouter dans le classeur de destination
    Workbooks(Nom_du_classeur).Close SaveChanges:=True 'fermeture du classeur avec enregistrement
 
    Workbooks.Open (Liste_classeurs)
    Set WS = ActiveWorkbook.Worksheets("Feuil1")
    Set WS = Nothing
 
    Ligne = Ligne + 1 'Ici on incrémente d'une ligne la lecture
 
    Wend
 
MsgBox "Plus aucune ligne à traiter", vbOKOnly + vbExclamation, "Fin du traitement"
End Sub

De plus ces deux commandes ne fonctionnent pas non plus...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Workbooks(Liste_classeurs).Close SaveChanges:=False
Workbooks(Fichier_source).Close SaveChanges:=False

Petite précision, version Excel 2013 32 bits

[Philippe Tulliez]

Bonjour,

Erreur d 'exécution '9':
L'indice n'appartient pas à la sélection

Pourtant le message est clair

Dans cette ligne de code, plusieurs paramètres sont à contrôler car il y a quatre possibilités qui peuvent provoquer cette erreur le classeur source ou cible ou les deux ne sont pas ouverts, la feuille source ou la feuille cible n'existe pas voire les deux

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Workbooks(Fichier_source).Sheets(Feuil_source).Copy After:=Workbooks(Nom_du_classeur).Sheets("Feuil1")

Etant donné les deux instructions publiées en fin de votre avant-dernier fil et vos explications. n'avez vous pas créé une boucle pour copier les données et ne fermez vous pas la source dans la boucle ?

[gerard13]

Philippe Tulliez, Bonjour et merci de tes conseils ,

J'ai vérifié que les classeurs sont bien ouvert et que les feuilles existent bien aussi, et c'est le cas...

Voici quelques copies d'écran...

On y vois bien les fichiers ouvert, ainsi que les valeur des variables, tout parait ok...

Ensuite j'ai clarifié et nettoyer le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Sub Ajout_feuil()
Dim Liste_classeurs As String, Dossier_du_classeur As String, Chemin_du_classeur As String
Dim Nom_du_classeur As String, Fichier_source As String, Feuil_source As String
Dim Ligne As Long
 
Ligne = 2 ' Ici on pointe la ligne sur laquelle on démarre la lecture du classeur Liste_Classeurs
Liste_classeurs = "C:\Test\Liste_classeurs.xlsm" 'Chemin du fichier Liste_classeurs.xlsm
Fichier_source = "C:\Test\source.xlsx" 'Pointage du fichier source
Feuil_source = "Feuil1" ' Pointage de la feuille source
 
Workbooks.Open (Fichier_source) ' Ouverture du fichier source
Workbooks.Open (Liste_classeurs) 'Ouverture du classeur liste des classeurs
 
    While Cells(Ligne, 1) <> "" ' On reboucle jusqu'à lire une ligne vide en colonne 1
    Nom_du_classeur = "" 'on efface toute valeur de la variable
    Chemin_du_classeur = "" 'on efface toute valeur de la variable
 
    NomClasseur Nom_du_classeur, Dossier_du_classeur, Chemin_du_classeur, Liste_classeurs, Ligne ' Construction du nom et du chemin du classeur cible
 
    Copie Chemin_du_classeur, Fichier_source, Feuil_source, Nom_du_classeur ' Copie de la feuille source dans le classeur cible
 
    Ligne = Ligne + 1 'Ici on incrémente d'une ligne la lecture
    Wend
 
MsgBox "Plus aucune ligne à traiter", vbOKOnly + vbExclamation, "Fin du traitement"
End Sub
 
Sub NomClasseur(ByRef Nom_du_classeur As String, ByRef Dossier_du_classeur As String, ByRef Chemin_du_classeur As String, ByRef Liste_classeurs As String, ByRef Ligne As Long)
Workbooks.Open (Liste_classeurs) 'Ouverture du classeur liste des classeurs
Nom_du_classeur = Cells(Ligne, 1).Value ' Sélection du classeur cible dans la liste des classeurs
Dossier_du_classeur = Cells(Ligne, 3).Value ' Sélection du chemin du classeur
Chemin_du_classeur = (Dossier_du_classeur & "\" & Nom_du_classeur) ' Création du chemin du classeur avec son nom
End Sub
 
Sub Copie(ByRef Chemin_du_classeur As String, ByRef Fichier_source As String, ByRef Feuil_source As String, ByRef Nom_du_classeur As String)
Workbooks.Open (Chemin_du_classeur) ' Ouverture du classeur cible
Workbooks(Fichier_source).Sheets(Feuil_source).Copy After:=Workbooks(Nom_du_classeur).Sheets(Sheets.Count) 'On copie la feuille source à ajouter dans le classeur de destination
End Sub

J'ai le sentiment que c'est peut-être un problème avec Excel 2013...

Quand penses-tu toi?

[gerard13]

Ouf...
C'est ok, ça fonctionne, une coquille dans le code...

Merci Philippe de m'avoir mis sur la piste


Le code fonctionnel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub Ajout_feuil()
Dim Liste_classeurs As String, Dossier_du_classeur As String, Chemin_du_classeur As String
Dim Nom_du_classeur As String, Fichier_source As String, Feuil_source As String
Dim Ligne As Long
 
Ligne = 2 ' Ici on pointe la ligne sur laquelle on démarre la lecture du classeur Liste_Classeurs
Liste_classeurs = "C:\Test\Liste_classeurs.xlsm" 'Chemin du fichier Liste_classeurs.xlsm
Fichier_source = "C:\Test\source.xlsx" 'Pointage du fichier source
Feuil_source = "Feuil1" ' Pointage de la feuille source
 
Workbooks.Open (Fichier_source) ' Ouverture du fichier source
Workbooks.Open (Liste_classeurs) 'Ouverture du classeur liste des classeurs
 
    While Cells(Ligne, 1) <> "" ' On reboucle jusqu'à lire une ligne vide en colonne 1
    Nom_du_classeur = "" 'on efface toute valeur de la variable
    Chemin_du_classeur = "" 'on efface toute valeur de la variable
    NomClasseur Nom_du_classeur, Dossier_du_classeur, Chemin_du_classeur, Liste_classeurs, Ligne ' Construction du nom et du chemin du classeur cible
 
    Copie Chemin_du_classeur, Fichier_source, Feuil_source, Nom_du_classeur ' Copie de la feuille source dans le classeur cible
 
    Ligne = Ligne + 1 'Ici on incrémente d'une ligne la lecture
    Workbooks(Liste_classeurs).Activate 'Activation du classeur liste des classeurs
 
    Wend
 
MsgBox "Plus aucune ligne à traiter", vbOKOnly + vbExclamation, "Fin du traitement"
End Sub
 
Sub NomClasseur(ByRef Nom_du_classeur As String, ByRef Dossier_du_classeur As String, ByRef Chemin_du_classeur As String, ByRef Liste_classeurs As String, ByRef Ligne As Long)
Workbooks.Open (Liste_classeurs) 'Ouverture du classeur liste des classeurs
Nom_du_classeur = Cells(Ligne, 1).Value ' Sélection du classeur cible dans la liste des classeurs
Dossier_du_classeur = Cells(Ligne, 3).Value ' Sélection du chemin du classeur
Chemin_du_classeur = (Dossier_du_classeur & "\" & Nom_du_classeur) ' Création du chemin du classeur avec son nom
End Sub
 
Sub Copie(ByRef Chemin_du_classeur As String, ByRef Fichier_source As String, ByRef Feuil_source As String, ByRef Nom_du_classeur As String)
Workbooks.Open (Chemin_du_classeur) ' Ouverture du classeur cible
Workbooks("Source.xlsx").Sheets(Feuil_source).Copy After:=Workbooks(Nom_du_classeur).Sheets(Sheets.Count) 'On copie la feuille source à ajouter dans le classeur de destination
End Sub

[Philippe Tulliez]

Bonjour,
J'allais juste répondre que la chaîne "C:\Test\NomClasseur.xlsx" servait pour ouvrir le classeur mais pas pour le définir dans la collection des classeurs ouverts où là seul son nom doit apparaître soit NomClasseur.xlsx

Vous devriez travailler directement avec des variables objets ce qui permet d'éviter ce genre de mésaventure

Petit exemple ci-dessous.
Les classeurs "cible" sont dans un sous-répertorie (TestClasseur) du répertoire où se trouve le classeur source (ici ThisWorkbook) et la feuille "source" nommée MyTemplate est copiée après la dernière feuille des classeurs "source"
Le nom des sous-répertoires et des classeurs se trouve dans une table structurée nommée T_WorkbookTarget dont l'illustration se trouve plus bas
Attention, la procédure ne vérifie pas si la feuille a déjà été copiée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub T()
  ' Déclaration des variables
  Dim CopyArray()
  Dim wkbSource As Workbook
  Dim shtSource As Worksheet
  Dim wkbTarget As Workbook
  Dim PathName As String     ' Répertoire de l'application
  Dim FullName As String     ' Nom complet (répertoire + Nom du classeur
  Dim SubFolder As String    ' Sous-répertoire
  Dim Elem As Integer
  ' Affectation des variables
  Set wkbSource = ThisWorkbook                       ' Classeur source
  Set shtSource = wkbSource.Worksheets("MyTemplate") ' Feuille source
  PathName = wkbSource.Path & "\"
  ' Start
  ' Chargement de la table
  CopyArray = Range("T_WorkbookTarget").Value
  ' Copie de la feuille modèle vers les classeurs cibles
  For Elem = 1 To UBound(CopyArray)
    FullName = PathName & CopyArray(Elem, 1) & "\" & CopyArray(Elem, 2)
    ' Ouverture du classeur cible
    Set wkbTarget = Workbooks.Open(Filename:=FullName)
    ' Copie de la feuille source vers le classeur cible
    With wkbTarget
      shtSource.Copy After:=Worksheets(Worksheets.Count)
    End With
    wkbTarget.Close SaveChanges:=True
  Next
  ' Fin de la procédure
  Set wkbSource = Nothing: Set shtSource = Nothing: Set wkbTarget = Nothing
End Sub

[gerard13]

Merci à toi Philippe