erreur 9 : l'indice n'appartient pas à la séléction : création et collage des données ds un fichier Excel

**popo2016** · 07/12/2016, 08h27

Bonjour,

J'ai un fichier excel contenant 2 feuilles :
feuille 1 : liste des noms de familles en colonne A avec d'autres critères en colonnes B, C, D,...
feuilel 2 : liste des noms dans la colonne A avec prénoms dans la colonne B

ce que je veux faire, boucler sur les prénoms de famille de la feuille 1, pour chaque prénom, créer un nouveau fichier excel renommé par le prenom.

dans la même itération, prendre le nom correspondant à ce prénom et aller filtrer avec ce nom dans la feuille 1.
copier le résultat du filtrage (toutes les colonnes) et le coller dans le classeur créé tout à l'heure (ayant le nom du prénom).

pour l'instant j'aimerai faire fonctionner mon code sur une seule valeur avant de procéder à la boucle, mon soucis actuel est au niveau des 2 dernières lignes, je n'arrive pas à ouvrir le fichier que j'ai créé pour y coller les lignes ( le nom du fichier doit être une variable).

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
Sub découper()
 
 
Dim XLApp As New Excel.Application
Dim XLBook As Workbook
Dim XLSheet As Worksheet
Dim MonClasseur As Workbook
Dim fichier As Workbook
 
Workbooks("decoupage").Sheets("Sheet2").Cells(2, 2).Select
 
Selection.Copy
motCopie = Selection.Value
 
Workbooks("decoupage").Sheets("Sheet1").Select
 
ActiveSheet.Range("$A$1:$B$11").AutoFilter Field:=1, Criteria1:= _
"=(DUPONT)", Operator:=xlAnd
 
Cells.Select
Selection.Copy
 
Set XLBook = XLApp.Workbooks.Add
XLBook.SaveAs motCopie
 
fichier = motCopie &".xlsx"
 
fichier = "C:\Users\admin\Documents\"& motCopie & ".xlsx"
fichier.Open
Workbooks("fichier").Sheets("Sheet1").Cells(1, 1).Paste
 
 (fichier)

l'erreur 9 sur la dernière ligne : l indice n appartient pas à la selection
Merci d'avance

**Marc-L** · 07/12/2016, 10h59

Bonjour,

effectivement le problème pourrait venir de l'inutile ouverture d'une seconde instance d'Excel ‼
Tu es déjà dans Excel, pourquoi diable en ouvrir un autre ?!

_________________________________________________________________________________________________________

Je suis Paris, Nice, Bruxelles, Charlie, …

**Jean-Philippe André** · 07/12/2016, 11h06

Salut,

fichier est une variable, donc pas de guillemets, et on n'ouvre pas une chaine de caracteres

:

Code :

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

1
2
Workbooks.Open fichier
Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

Sinon, sur le reste de ton code, on n'evite dans la mesure du possible de passer par des Select + Selection, surtout si tu jongles entre les classeurs.

Ton code pourrais etre considere comme plus robuste avec par exemple :

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
Sub découper()
 
 
Dim XLApp As New Excel.Application
Dim XLBook As Workbook
Dim XLSheet As Worksheet
Dim MonClasseur As Workbook
Dim fichier As Workbook
 
With Workbooks("decoupage").Sheets("Sheet2").Cells(2, 2)
   .Copy
   motCopie = .Value
End With
Workbooks("decoupage").Sheets("Sheet1").Range("$A$1:$B$11").AutoFilter Field:=1, Criteria1:= _
"=(DUPONT)", Operator:=xlAnd
 
Cells.Select
Selection.Copy
 
Set XLBook = XLApp.Workbooks.Add
XLBook.SaveAs motCopie
 
fichier = motCopie &".xlsx"
' ou bien
fichier = "C:\Users\admin\Documents\"& motCopie & ".xlsx"
Workbooks.Open fichier
Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

**popo2016** · 07/12/2016, 11h17

Envoyé par Marc-L

Bonjour,

effectivement le problème pourrait venir de l'inutile ouverture d'une seconde instance d'Excel ‼
Tu es déjà dans Excel, pourquoi diable en ouvrir un autre ?!

_________________________________________________________________________________________________________

Je suis Paris, Nice, Bruxelles, Charlie, …

effectivement je n'ai pas exprimé tout mon besoin, l'objectif de la macro est de découper le document initial en plusieurs fichiers Excels différents sur la base du critère : nom de famille.
dans mon code j'essaye de faire qlq chose de plus simple possible

**Marc-L** · 07/12/2016, 11h24

Et c'est réalisable directement dans l'Excel déjà ouvert évitant ainsi un gâchis de ressources …

**popo2016** · 07/12/2016, 11h29

Envoyé par Jean-Philippe André

Salut,

fichier est une variable, donc pas de guillemets, et on n'ouvre pas une chaine de caracteres

:

Code :

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

1
2
Workbooks.Open fichier
Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

Sinon, sur le reste de ton code, on n'evite dans la mesure du possible de passer par des Select + Selection, surtout si tu jongles entre les classeurs.

Ton code pourrais etre considere comme plus robuste avec par exemple :

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
Sub découper()
 
 
Dim XLApp As New Excel.Application
Dim XLBook As Workbook
Dim XLSheet As Worksheet
Dim MonClasseur As Workbook
Dim fichier As Workbook
 
With Workbooks("decoupage").Sheets("Sheet2").Cells(2, 2)
   .Copy
   motCopie = .Value
End With
Workbooks("decoupage").Sheets("Sheet1").Range("$A$1:$B$11").AutoFilter Field:=1, Criteria1:= _
"=(DUPONT)", Operator:=xlAnd
 
Cells.Select
Selection.Copy
 
Set XLBook = XLApp.Workbooks.Add
XLBook.SaveAs motCopie
 
fichier = motCopie &".xlsx"
' ou bien
fichier = "C:\Users\admin\Documents\"& motCopie & ".xlsx"
Workbooks.Open fichier
Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

Merci d'abord de votre réponse. j'ai testé le code en déclarant :
fichier As String
et en gardant :
fichier = "C:\Users\A455209\Documents\" & motCopie & ".xlsx"
Workbooks.Open fichier
Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

Ceci n'a malheureusement pas empéché l'erreur 9 sur "Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste"
j'ai essayé également avec ....Cells.Paste

**popo2016** · 07/12/2016, 11h32

Envoyé par Marc-L

Et c'est réalisable directement dans l'Excel déjà ouvert évitant ainsi un gâchis de ressources …

Est-ce qu'il y a moyen si non de coller les données tout en gardant le fichier créé fermé ? par ce que je n'ai pas intérêt à l'ouvrir à chaque fois vu qu'il y aura des dizaines de nouveaux fichiers

**Marc-L** · 07/12/2016, 11h39

Tout dépend du contexte mais pour une débutante c'est bien plus simple d'ouvrir le classeur.

Sinon en dehors des dizaines d'exemples dans les discussions de ce forum,
parmi les nombreux tutoriels à disposition voici Lire et écrire dans les classeurs Excel fermés …

**popo2016** · 07/12/2016, 11h48

Merci pour le tuto je lirai !
pour l'instant je vais uniquement ajouter un attribut Visible = False, pour qu'au moins il n'apparaisse pas à l'ouverture.
actuellement je crois que c'est un problème de syntaxe car Excel ne me propose pas le "Paste" quand j'écris la ligne :

Code :

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

Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

**Jean-Philippe André** · 07/12/2016, 11h55

Salut,

soit tu utilises fichier comme une chaine de caracteres, soit comme un classeur, mais tu ne peux pas l'utiliser avec les deux dans ce code =]

**popo2016** · 07/12/2016, 12h01

oui, justement je l'ai enlevé, j'ai gardé ceci :

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
Sub découper()
 
 
Dim XLApp As New Excel.Application
Dim XLBook As Workbook
Dim XLSheet As Worksheet
Dim MonClasseur As Workbook
Dim fichier As String
 
With Workbooks("decoupage").Sheets("Sheet2").Cells(13, 2)
   .Copy
   motCopie = .Value
End With
Workbooks("decoupage").Sheets("Sheet1").Range("$A$1:$B$11").AutoFilter Field:=1, Criteria1:= _
"=DUPONT", Operator:=xlAnd
 
Cells.Select
Selection.Copy
 
Set XLBook = XLApp.Workbooks.Add
XLBook.SaveAs motCopie
 
 
fichier = "C:\" & motCopie & ".xlsx"
Workbooks.Open fichier
 
 
Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste
 
End Sub

Mon fichier se crée, se renomme et s'ouvre mais l'erreur 9 à la dernière ligne

**Jean-Philippe André** · 07/12/2016, 12h20

Ok et es-tu certain que ton fichier a une feuille Sheet1 ?

**popo2016** · 07/12/2016, 13h24

Ouiii :/

**halaster08** · 07/12/2016, 13h38

Bonjour
test ceci:

Code :

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

1
2
Cells(1, 1).Copy
Cells(1, 2).Paste

et

Code :

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

1
2
Cells(1, 1).Copy
Cells(1, 2).PasteSpecial

Le premier renvoie une erreur 438, mais ce n'est pas l'erreur qu'il te donne il doit donc y avoir autre chose:

Code :

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

1
2
3
4
5
 
fichier = "C:\" & motCopie & ".xlsx"
Workbooks.Open fichier
 
Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

Pour ouvrir un fichier tu as besoin du chemin, mais une fois ouvert tu n'en a plus besoin, essaye donc

Code :

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

Workbooks(motCopie & ".xlsx").Sheets("Sheet1").Cells(1, 1).Pastespecial

**popo2016** · 07/12/2016, 14h09

ça ne change rien, l'erreur persiste tjrs c'est vrmnt bizarre, pensez vous que ça peut être du à des références à cocher ?
5 références sont cochées chez moi :
- VB for application
- M. Excel 12.0 object Library
- OLE automatisation
- M. office 12.0 Object Library
- Microsoft ActiveX data objects 2.0 library

**halaster08** · 07/12/2016, 14h15

Peux tu mettre une capture d'écran où l'on voit le nom du classeur et des feuilles?
Et tester le code en remplaçant dans la ligne en question toutes les variables par les valeurs qu'elles sont censé avoir.

edit: pour pour trouver précisement d'où vient l'erreur tu peux remplacer

Code :

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

Workbooks(fichier).Sheets("Sheet1").Cells(1, 1).Paste

par

Code :

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

1
2
3
4
Workbooks(fichier).select
Sheets("Sheet1").select
Cells(1, 1).select
selection.Paste

De cette manière tu verra exactement quel partie du code plante.

**Jean-Philippe André** · 07/12/2016, 14h16

Salut,

je recommande ceci :

Code :

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

1
2
3
4
Dim fichier As Workbook
Set fichier = Workbooks.Open ("C:\" & motCopie & ".xlsx")
 
 fichier.Sheets("Sheet1").Cells(1, 1).Paste

**popo2016** · 07/12/2016, 15h06

non plus .. j'ai essayé : fichier.Sheets("Sheet1").Cells(3, 3).Select
ça me sélectionne bien la cellule (3,3), le problème est par rapport au collage !
je me suis dite que ça peut être du à des droits d'écriture (lecture seule) du coup j'ai essayé avec un fichier ke j'ai créée manuellement et ça n'a pas marché non plus

**halaster08** · 07/12/2016, 15h16

J'avais édité mon message précédent, tu peux couper ta ligne en 4 pour voir exactement où est le problème:

Code :

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

1
2
3
4
fichier.select
Sheets("Sheet1").select
Cells(1, 1).select
selection.Pastespecial

Par contre j'insiste chez moi .paste genère un erreur 438, il faut utiliser pastespecial.

**Jean-Philippe André** · 07/12/2016, 15h31

Sinon tu fais tout d'un coup avec

Code :

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

Cells.Copy destination :=fichier.Worksheets("Sheet1").Cells(3,3)

erreur 9 : l'indice n'appartient pas à la séléction : création et collage des données ds un fichier Excel

Macros et VBA Excel

Discussions similaires

Partager

Partager