La copie d'une feuille de calcul par programmation provoque l'erreur d'exécution 1004 dans Excel

Symptômes

Dans Microsoft Excel, vous exécutez une macro qui copie des feuilles de calcul, puis place les feuilles de calcul dans le classeur d'origine. Lors de cette opération, un message d'erreur semblable à l'un des suivants peut s'afficher :

Erreur d'exécution '1004' :
La méthode Copy de la classe Worksheet a échoué
Erreur d'exécution '1004' :
Erreur définie par l'application ou par l'objet.

Cause

Microsoft fournit des exemples de programmation à des fins d'illustration uniquement, sans garantie explicite ou implicite. Ceci inclut, de manière non limitative, les garanties implicites de qualité marchande ou d'adéquation à un usage particulier. Cet article suppose que vous connaissez le langage de programmation présenté et les outils utilisés pour créer et déboguer des procédures. Les techniciens du Support technique Microsoft peuvent vous expliquer les fonctionnalités d'une procédure particulière. Toutefois, ils ne modifieront pas ces exemples pour fournir des fonctionnalités ajoutées ou des procédures de construction pour satisfaire vos besoins spécifiques.

Ce problème peut se produire lorsque vous donnez un nom défini au classeur, puis que vous copiez la feuille de calcul plusieurs fois sans tout d'abord enregistrer et fermer le classeur, comme dans l'exemple de code suivant :


Sub CopySheetTest()
Dim iTemp As Integer
Dim oBook As Workbook
Dim iCounter As Integer

' Create a new blank workbook:
iTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iTemp

' Add a defined name to the workbook
' that RefersTo a range:
oBook.Names.Add Name:="tempRange", _
RefersTo:="=Sheet1!$A$1"

' Save the workbook:
oBook.SaveAs "c:\test2.xls"

' Copy the sheet in a loop. Eventually,
' you get error 1004: Copy Method of
' Worksheet class failed.
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
Next
End Sub

Résolution

Pour résoudre ce problème, enregistrez et fermez le classeur à intervalles réguliers pendant le processus de copie, comme dans l'exemple de code suivant :
Sub CopySheetTest()
Dim iTemp As Integer
Dim oBook As Workbook
Dim iCounter As Integer

' Create a new blank workbook:
iTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iTemp

' Add a defined name to the workbook
' that RefersTo a range:
oBook.Names.Add Name:="tempRange", _
RefersTo:="=Sheet1!$A$1"

' Save the workbook:
oBook.SaveAs "c:\test2.xls"

' Copy the sheet in a loop. Eventually,
' you get error 1004: Copy Method of
' Worksheet class failed.
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
'Uncomment this code for the workaround:
'Save, close, and reopen after every 100 iterations:
If iCounter Mod 100 = 0 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open("c:\test2.xls")
End If
Next
End Sub
Remarque Le nombre d'opérations de copie d'une feuille de calcul que vous pouvez effectuer avant de devoir enregistrer le classeur varie selon la taille de la feuille de calcul.

Contournement

Pour contourner ce problème, insérez une nouvelle feuille de calcul à partir d'un modèle au lieu de copier une feuille existante. Pour cela, procédez comme suit en fonction de la version d'Excel que vous exécutez.

Microsoft Office Excel 2007

  1. Créez un nouveau classeur, puis supprimez toutes les feuilles de calcul à l'exception d'une feuille.
  2. Mettez en forme le classeur, puis ajoutez les textes, données et graphiques appropriés dans le modèle par défaut.
  3. Cliquez sur le bouton Microsoft Office, puis sur Enregistrer sous.
  4. Dans la boîte de dialogue Nom de fichier, tapez le nom que vous souhaitez donner au modèle Excel.
  5. Dans la liste Type, cliquez sur Modèle (* .xltx), puis sur Enregistrer.
  6. Pour insérer le modèle par programmation, utilisez le code suivant :
    Sheets.Add Type:=chemin\nom_fichier


    Dans ce code, chemin\nom_fichier est une chaîne contenant le chemin d'accès complet et le nom de fichier de votre modèle de feuille.

Microsoft Office Excel 2003 et versions antérieures d'Excel

  1. Créez un nouveau classeur, puis supprimez toutes les feuilles de calcul à l'exception d'une feuille.
  2. Mettez en forme le classeur, puis ajoutez les textes, données et graphiques appropriés dans le modèle par défaut.
  3. Cliquez sur Fichier, puis sur Enregistrer sous.
  4. Dans la boîte de dialogue Nom de fichier, tapez le nom que vous souhaitez donner au modèle Excel.
  5. Dans la liste Type, cliquez sur Modèle (*.xlt), puis sur Enregistrer.
  6. Pour insérer le modèle par programmation, utilisez le code suivant :
    Sheets.Add Type:=chemin\nomfichier


    Dans ce code, chemin\nom_fichier est une chaîne contenant le chemin d'accès complet et le nom de fichier de votre modèle de feuille.

Statut

Microsoft a confirmé l'existence de ce problème dans les produits Microsoft figurant dans la liste des produits concernés par cet article.
Propriétés

ID d'article : 210684 - Dernière mise à jour : 13 juin 2007 - Révision : 1

Commentaires