Erreur 9 sur copie feuille d'un classeur à un autre

Le 03/12/2018 à 23:48

Bonjour,

Je cherche à copier dans un classeur une feuille provenant d'autre classeur. Après différentes recherches j'ai écrit le code suivant :

Sub Ajout()
Dim NomFichier As String
NomFichier = ThisWorkbook.Name
' Ouverture boite information
MsgBox "Choisir le modèle de commande"
'Ouverture fenêtre de choix
Dim NomModele As Variant
NomModele = Application.GetOpenFilename
' Si aucun choix arrêt
If NomModele = False Then Exit Sub
Workbooks.Open NomModele
'Récupération nom de la feuille
Dim NomFeuille As String
NomFeuille = ActiveSheet.Name
Workbooks(NomFichier).Activate
'Copie de la feuille
[Surligner]Workbooks(NomModele).Sheets(NomFeuille).Copy After:=Workbooks(NomFichier).Sheets("Feuil1")[/Surligner]
End Sub

Mon soucis est qu'à l'exécution sur la dernière ligne d'instruction j'ai une erreur :Erreur d'exécution 9 l'indice n'appartient pas à la sélection.

Or après vérification par le debogeur :

  • Toutes les variables de l'instruction ont les bonnes valeurs
    Les deux classeurs Excel concernés sont bien ouverts

Je sèche, si quelqu’un à la solution je suis preneur.

Merci d'avance

Bruno

Le 04/12/2018 à 00:45

Bonjour,

à tester,

Sub Ajout()
Dim NomFichier As Workbook
Dim wk1 As Workbook
Dim NomFeuille As Worksheet
Dim NomModele As Variant

Set NomFichier = ThisWorkbook
' Ouverture boite information
MsgBox "Choisir le modèle de commande"
'Ouverture fenêtre de choix

NomModele = Application.GetOpenFilename("Excel Files (*.xls), *.xlsx")

' Si aucun choix arrêt
If NomModele = False Then Exit Sub
Workbooks.Open NomModele
Set wk1 = ActiveWorkbook
'Récupération nom de la feuille

Set NomFeuille = ActiveSheet
'Copie de la feuille

NomFeuille.Copy After:=NomFichier.Sheets("Feuil1")

'fermeture du fichier sans l'enregistrer
wk1.Close SaveChanges:=False

NomFichier.Activate
End Sub
Le 04/12/2018 à 08:33

Merci SabV

Je viens de tester ce code, mais j'ai 2 soucis avec :

  • Sur l'instruction NomModele = Application.GetOpenFilename("Excel Files (*.xls), *.xlsx") seuls les fichiers xls sont disponibles, dans les types fichiers il n'y a que les xls (pas les xlsx)
    Sur l'instruction de copie de la feuille j'ai l'erreur 1004 Excel ne parvient pas à insérer les feuilles dans le classeur de destination car il contient moins de lignes et de colonnes que le classeur source. Pour déplacer les ou copier les données .... vous pouvez les sélectionner puis utiliser les commandes copier et Coller. Or je veux conserver le formatage des lignes et des colonnes de la feuille source ce qui risque de ne pas être le cas avec un copier/Coller.

Merci de ton aide

Le 04/12/2018 à 15:28

re,

Sur l'instruction NomModele = Application.GetOpenFilename("Excel Files (*.xls), *.xlsx") seuls les fichiers xls sont disponibles, dans les types fichiers il n'y a que les xls (pas les xlsx)

modifier la ligne

NomModele = Application.GetOpenFilename("Excel Files (*.xls), *.xlsx")

par

NomModele = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")

Excel ne parvient pas à insérer les feuilles dans le classeur de destination car il contient moins de lignes et de colonnes

il faudrait enregistrer le fichier, qui vient d'être ouvert, au format xlsx ou xlsm

'sans macro (xlsx)

ActiveWorkbook.SaveAs Filename:=wk1.FullName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

'avec macro (xlsm)

ActiveWorkbook.SaveAs Filename:=wk1.FullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Le 04/12/2018 à 19:24

Merci de ton aide.

Pour la mise à disposition de tous les fichiers de types Excel, la solution proposée fonctionne.

Pour la copie de la feuille le problème reste entier et identique. D'ailleurs j'avais du mal à imaginer venait d'un besoin d'enregistrement dans la meure où le fichier source est déjà enregistré sur le DD et ne subit aucune modification.

Le 04/12/2018 à 21:29

re,

Pour la copie de la feuille le problème reste entier et identique. D'ailleurs j'avais du mal à imaginer venait d'un besoin d'enregistrement dans la meure où le fichier source est déjà enregistré sur le DD et ne subit aucune modification.

si l'erreur provient du fait :

Excel ne parvient pas à insérer les feuilles dans le classeur de destination car il contient moins de lignes et de colonnes que le classeur source.

c'est que le fichier que l'on ouvre avec NomModele = Application.GetOpenFilename

est une version antérieur à 2007, donc moins de lignes et de colonnes.

en enregistrant ce fichier au format xlsx, on égalise le nombre de lignes et de colonnes, et là, la copie de l'onglet devient possible.

Le 05/12/2018 à 14:28

Merci encore.

On est jamais trop précis dans ses demandes, ni trop explicatif dans les réponses. Pour le problème de copie de feuille, c'est le fichier destination qui était en xls et le fichier source en xlsx !

J'ai converti le fichier destination en xslx et effectivement ça fonctionne. J'ai malgré tout gardé la possibilité de conversion si nécessaire du fichier source, avec une modification. En effet dans le code proposé tu reprenais le nom du xls pour le mettre en xlsx. Or la variable contenait l'extension xls ce qui générait une erreur.

Ci-joint le code définitif qui fonctionne,

Sub AjoutFeuille()

Dim W1 As Workbook
Dim Feuille As Worksheet
Dim NomModele As Variant
Dim NomModeleXlsx As Variant
Set W1 = ActiveWorkbook

' Ouverture boite information
MsgBox "Choisir la feuille de commande"
'Ouverture fenêtre de choix

NomModele = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
' Si aucun choix arrêt
If NomModele = False Then Exit Sub
If Right(NomModele, 3) = "xls" Then
Workbooks.Open NomModele
NomModeleXlsx = Replace(NomModele, "xlx", "xlsx")
ActiveWorkbook.SaveAs Filename:=NomModeleXlsx, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Else
Workbooks.Open NomModele
End If

'Récupération nom de la feuille

Set Feuille = ActiveSheet

Feuille.Copy After:=W1.Sheets(Sheets.Count)
W1.Activate

End Sub

Par contre j'ai supprimé le paramètre FullName dans l'instruction SaveAs qui me générais une erreur 424 "Objet Requis". Par curiosité je suis preneur de l'explication.

Merci encore

Le 05/12/2018 à 15:29

re,

j'ai supprimé le paramètre FullName dans l'instruction SaveAs qui me générais une erreur 424 "Objet Requis". Par curiosité je suis preneur de l'explication.

avais tu attribué la référence à la variable wk1 ?

Set wk1 = ActiveWorkbook
ActiveWorkbook.SaveAs Filename:=wk1.FullName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Le 05/12/2018 à 15:53

Dans mon code la variable est NomModeleXlsx.

Le problème vient je pense de la définition du type de variable.

Je l'ai définie en Variant et non en Workbook, car dans ce dernier cas c'est l'instruction

NomModeleXlsx = Replace(NomModele, "xlx", "xlsx")

qui coince. Cette instruction est nécessaire (peut-être y a t'il d'autres moyens) pour que l'instruction SaveAs enregistre en xlsx un fichier qui est au départ un xls

Rechercher des sujets similaires à "erreur copie feuille classeur"