VBA Erreur 1004 espace pile insuffisant

**L'Albatros** · 16/07/2013, 18h33

Bonjour,

Dans une macro toute bête, j'ai une ligne qui plante: "erreur d'exécution '1004' Espace pile insuffisant"

voici le code ci-dessous: (le code est très niais, mais le client veut un truc simple en plus des contraintes techniques imposées)

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
 
Private Sub test()
Dim Fichier As String, Chemin, navette As String
Dim Wb As Workbook
 
' Allocation pour la variable "Chemin" -> pointe sur le dossier cible "Fiches Validées" des gestionnaires
Chemin = "\\...\Dossier_Fiches_Eval_Validees_Cible_Macro\"
Fichier = Dir(Chemin & "*.xls")
 
' Désactivation de tous les messages automatiques d'Excel
Application.DisplayAlerts = False
 
' Boucle qui parcourt l'ensemble des fichiers Excel du dossier cible
Do While Fichier <> ""
 
    ' Ouverture d'un classeur Excel
    Set Wb = Workbooks.Open(Chemin & Fichier)
    Wb.Worksheets("Fiche").Select
    Range("C4").Select
    Selection.Copy
    Wb.Worksheets("Donnees_Taux_Capitalisation").Select
    Wb.Worksheets("Donnees_Taux_Capitalisation").Unprotect Password:="****"
    Range("J2").Select
    'On Error Resume Next
 
'*************************************** ligne où ça plante
    Range("J2").PasteSpecial
 
    'On Error Resume Next
    Wb.Worksheets("Donnees_Taux_Capitalisation").Protect Password:="****"
    Wb.Save
    Wb.Close
    ' Vidage mémoire
    Set Wb = Nothing
    ' Allocation variable de parcours "Fichier"
    Fichier = Dir
 
Loop
End Sub

Si quelqu'un pouvait me filer un coup de main, ce serait vraiment cool...

Merci d'avance

**EngueEngue** · 16/07/2013, 18h46

Oula quelle horreur! Vire moi tous ces selects et ça ne plantera pas.

Code :

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

1
2
Range(..).copy _
destination:= range(..)

**L'Albatros** · 16/07/2013, 18h55

Je veux bien, mais ça plante tout pareil...

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
 
Do While Fichier <> ""
 
    ' Ouverture d'un classeur Excel
    Set Wb = Workbooks.Open(Chemin & Fichier)
    Wb.Worksheets("Donnees_Taux_Capitalisation").Unprotect Password:="****"
    Wb.Worksheets("Fiche").Range("C4").Copy Destination:=Wb.Worksheets("Donnees_Taux_Capitalisation").Range("J2")
    Wb.Worksheets("Donnees_Taux_Capitalisation").Protect Password:="****"
    Wb.Save
    Wb.Close
    ' Vidage mémoire
    Set Wb = Nothing
    ' Allocation variable de parcours "Fichier"
    Fichier = Dir
 
Loop

**EngueEngue** · 16/07/2013, 18h59

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
Do While Fichier <> ""
 
    ' Ouverture d'un classeur Excel
    Set Wb = Workbooks.Open(Chemin & Fichier)
    Wb.Worksheets("Donnees_Taux_Capitalisation").Unprotect Password:="****"
Wb.Worksheets("Fiche").select
    Wb.Worksheets("Fiche").Range("C4").Copy Destination:=Wb.Worksheets("Donnees_Taux_Capitalisation").Range("J2")
    Wb.Worksheets("Donnees_Taux_Capitalisation").Protect Password:="****"
    Wb.Save
    Wb.Close
    ' Vidage mémoire
    Set Wb = Nothing
    ' Allocation variable de parcours "Fichier"
    Fichier = Dir
 
Loop

**Marc-L** · 16/07/2013, 19h04

C'est inutile …

Déjà faudrait-il savoir sur le nouveau code quel n° de ligne déclenche l'erreur ?!

Ensuite si ce n'est juste pour récupérer une valeur, pas besoin de passer par la méthode Copy,
suffit juste d'effectuer l'affectation directement !

Et puis dans le doute, commencer par redémarrer le PC et ne lancer qu'Excel …

__________________________________________________________________________________________

Merci de cliquer sur

pour chaque message ayant aidé puis sur

pour clore cette discussion …

**L'Albatros** · 17/07/2013, 11h24

Merci EnqueEnque, mais ça plante tout pareil. Et c'est toujours la même ligne qui pose problème, quelque soit la "syntaxe" :

Code :

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

Wb.Worksheets("Fiche").Range("C4").Copy Destination:=Wb.Worksheets("Donnees_Taux_Capitalisation").Range("J2")

Merci Marc-L, je vais réessayer de redémarrer le pc et de ne faire tourner qu'Excel. Je l'avais déjà fait, mais bon, sait-on jamais.

Quant à l'instruction "Copy", bien évidemment j'aurais préféré faire une affectation directe, mais j'ai des contraintes spécifiques. Je suis "obligé" de passer par un copy/paste...

**L'Albatros** · 17/07/2013, 11h44

Bon bin me re-voilà avec un pc re-démarré... et idem.

Les classeurs Excel ne sont pas très lourds, leurs noms respectifs ne dépassent par 30 caractères, l'intruction copy/paste d'une cellule c'est pas non plus super lourd...mais ça ne passe pas.

help please

**EngueEngue** · 17/07/2013, 11h46

peux tu envoyer ton classeur ?

**L'Albatros** · 17/07/2013, 11h56

Oui bien sûr, cf en PJ

**L'Albatros** · 17/07/2013, 12h04

Et je me permets d'ajouter que les fiches qui sont à corriger ont du code VBA dans leur "ThisWorkbook":

Exemple:

Code :

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Fonction qui initialise certaines cellules lorsque l'utilisateur effectue des "clics",
donc :

Le but du jeu est ici de changer le contenu de certaines cellules sans que cette fonction n'initialise des champs (sinon -> perte d'information)

Et le client veut un truc "simple", et robuste selon les différentes versions d'Excel (passage de Excel 2000 à 2007-2010 dans 4 mois)

**EngueEngue** · 17/07/2013, 12h05

Fonctionne parfaitement.

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
Private Sub Corrige_Fiches()
Dim Fichier As String, Chemin, navette As String
Dim Wb As Workbook
 
' Allocation pour la variable "Chemin" -> pointe sur le dossier cible "Fiches Validées" des gestionnaires
Chemin = "\\...\Dossier_Fiches_Eval_Validees_Cible_Macro\"
Fichier = Dir(Chemin & "*.xls")
 
' Désactivation de tous les messages automatiques d'Excel
Application.DisplayAlerts = False
 
' Boucle qui parcourt l'ensemble des fichiers Excel du dossier cible
Do While Fichier <> ""
 
    ' Ouverture d'un classeur Excel
    Set Wb = Workbooks.Open(Chemin & Fichier)
    Wb.Worksheets("Donnees_Taux_Capitalisation").Unprotect Password:="***"
    Wb.Worksheets("Fiche").Select
    Wb.Worksheets("Fiche").Range("C4").Copy Destination:=Wb.Worksheets("Donnees_Taux_Capitalisation").Range("J2")
    Wb.Worksheets("Donnees_Taux_Capitalisation").Protect Password:="***"
    Wb.Save
    Wb.Close
    ' Vidage mémoire
    Set Wb = Nothing
    ' Allocation variable de parcours "Fichier"
    Fichier = Dir
 
Loop
End Sub

Va copier C4 de fiche en J2 de tauxcapitalisation pour tout fichier excel contenant un onglet Donnees_Taux_Capitalisation et un onglet Fiche dans le répertoire chemin en déprotégeant la feuille et en la reprotégeant

**L'Albatros** · 17/07/2013, 12h34

Ben pas chez moi...

Puis-je t'envoyer un exemple de fiche à corriger en MP stp ?

**EngueEngue** · 17/07/2013, 12h36

sûr
A mon avis ton WB_Change va se lancer en cascade évenementielle à lors de la protection changement déprotection.. Donc change par un WB_Click...

**L'Albatros** · 17/07/2013, 12h50

En fait, pour des raisons de confidentialités, et pour que d'autres puissent participer, je mets ci-dessous l'ensemble du code VBA contenu dans le "ThisWorkbook" de chaque fiche Excel que je dois corriger.

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
'**********************************************************************************************************
'*********************************** Fonctions VBA par Jean-Baptiste*********************************
'******************************************** MAJ 4 juillet 2013 ********************************************
'**********************************************************************************************************
 
 
' Déclaration de la variable publique "drapeau": cette variable sert à savoir où on en est dans le déroulement de la saisie
Option Explicit
Dim drapeau As Integer
 
' Détection de l'ouverture de la fiche d'évaluation
Private Sub Workbook_Open()
    'Déclaration du mot de passe
    Dim Mot_de_Passe As String
    Mot_de_Passe = "IPAG"
 
    ' Désactivation du bouclier
    ActiveSheet.Unprotect (Mot_de_Passe)
 
    ' Ré-activation du bouclier
    ActiveSheet.Protect Password:=Mot_de_Passe, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
 
    ' Actualisation de la variable d'état: drapeau
    drapeau = 1
 
End Sub
 
 
 
' Détection de l'enregistrement de la fiche d'évaluation
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
If drapeau = 2 Then
    ' Messages avant enregistrement
        'Pour effectuer une action avant l'enregistrement: positionner le code ici
 
    ' Message relatif au remplissage du mini fichier Taux de Capitalisation ssi au moins un des postes est clos
    If Range("A2").Value = "Statut Dossier: Clos" Or Range("A2").Value = "Statut Dossier: En cours dont certains postes clos" Then
        MsgBox "Au moins un des postes est clos. Merci donc de bien vouloir saisir les nouveaux champs en bas de la fiche d'évaluation avant la sauvegarde"
        Rows("204:223").Select
    End If
 
    'Message relatif à la saisie effective des 3 cellules violettes A1, A2 et A3
    MsgBox "Avant de fermer le document, merci de veiller à ce que les informations colorées en violet soient à jour, en haut à gauche de la fiche d'évaluation: Pour un sinistre donné, une seule fiche d'évaluation doit avoir son statut validé." _
    & " Pour un sinistre donné, une seule fiche d'évaluation doit avoir son statut validé, et cette fiche doit être exhaustive: tous les postes concernés par le sinistre doivent être saisis"
    ' Condition sur actualisation des données de statut - si remplissage incomplet -> enregistrement annulé
    If (Range("A1").Value = "Statut Fiche: -----> A Remplir" Or Range("A2").Value = "Statut Dossier: ---> A Remplir" Or Range("A3").Value = "Etat Victime: -----> A Remplir") Then
        MsgBox "Attention: Au moins un des renseignements en haut à gauche de la fiche, en orange, cellules A1, A2 ou A3 n'a pas été actualisé"
        Cancel = True
    End If
End If
End Sub
 
' Détection de la fermeture de la fiche d'évaluation
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If drapeau = 2 Then
    ' Messages avant enregistrement
 
    ' Message relatif à la saisie effective des 3 cellules violettes A1, A2 et A3
        'Pour effectuer une action avant l'enregistrement: positionner le code ici
 
    ' Condition sur actualisation des données de statut - si remplissage incomplet -> Fermeture du classeur annulée
    If (Range("A1").Value = "Statut Fiche: -----> A Remplir" Or Range("A2").Value = "Statut Dossier: ---> A Remplir" Or Range("A3").Value = "Etat Victime: -----> A Remplir") Then
        MsgBox "Attention: Au moins un des renseignements en haut à gauche de la fiche, en orange, cellules A1, A2 ou A3 n'a pas été actualisé" & vbCrLf & vbCrLf & "La fermeture de ce document est donc annulée"
        Cancel = True
    Else
        MsgBox "Merci d'avoir effectué la saisie selon le nouveau process établi"
    End If
End If
End Sub
 
' Détection de la première modification de la fiche d'évaluation: détection utilisateur humain
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If drapeau = 1 Then
    ' Message d'accueil dès reconnaissance utilisateur
        ' Initialisation des statuts de la fiche (en orange)
    Sheets("Fiche").Range("A1").Value = "Statut Fiche: -----> A Remplir"
    Sheets("Fiche").Range("A2").Value = "Statut Dossier: ---> A Remplir"
    Sheets("Fiche").Range("A3").Value = "Etat Victime: -----> A Remplir"
 
    ' Initialisation des renseignements bénéficiaires
    Sheets("Fiche").Range("A79").Value = "Bénéficiaire OS: -----> A Remplir"
    Sheets("Fiche").Range("A95").Value = "Bénéficiaire OS: -----> A Remplir"
    Sheets("Fiche").Range("A128").Value = "Bénéficiaire: -----> A Remplir"
    Sheets("Fiche").Range("A138").Value = "Bénéficiaire: -----> A Remplir"
 
    ' Actualisation de la variable d'état: drapeau
    drapeau = 2
 
    'Message optionnel de reconnaissance de l'utilisateur
        'MsgBox "Bonjour " & Environ("UserName") & " Merci d'effectuer la saisie selon le process établi: cf document [lien hypertexte]"
    Exit Sub
    End If
End Sub

**EngueEngue** · 17/07/2013, 12h55

Code :

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

1
2
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

devient

Code :

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

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range)

Tu diras à Jean Baptiste que ses macros son grave inutiles et alourdissent à fond le classeur, en passant

**L'Albatros** · 17/07/2013, 13h02

Oui, sauf que je n'ai pas le droit de changer le code des fiches à corriger. Il faut que je créée un classeur indépendant avec un code VBA qui arrive à modifier des fiches (il y en a près de 500) contenant ce code.

Peut-être que le copy/paste n'est pas la bonne chose à faire, si quelqu'un à une autre idée ?

- modifier les fiches sans les ouvrir ? (et donc passer outre les protections)
- neutraliser le VBA des fiches à corriger ? (radical mais je ne sais pas faire)
- ...
- mettre des claques au PC, ou au client

**Marc-L** · 17/07/2013, 13h14

Envoyé par L'Albatros

Peut-être que le copy/paste n'est pas la bonne chose à faire, si quelqu'un à une autre idée ?

Déjà répondu ‼

RangeX = RangeY

Envoyé par L'Albatros

- modifier les fiches sans les ouvrir ? (et donc passer outre les protections)
- neutraliser le VBA des fiches à corriger ? (radical mais je ne sais pas faire)

Pas clair mais si ce n'est qu'un évènement provoquant l'erreur,
suffit juste de désactiver temporairement les évènements via la propriété Application.EnableEvents …

__________________________________________________________________________________________

Merci de cliquer sur

pour chaque message ayant aidé puis sur

pour clore cette discussion …

__________________________________________________________________________________________
A celle qui dit que les hommes sont tous pareils, lui répondre qu'il ne fallait pas tous les essayer !

**EngueEngue** · 17/07/2013, 13h36

Envoyé par Marc-L

si ce n'est qu'un évènement provoquant l'erreur,
suffit juste de désactiver temporairement les évènements via la propriété Application.EnableEvents …

+1

**L'Albatros** · 17/07/2013, 14h34

BINGO !!!

Merci Marc-L, je n'y aurais jamais pensé.

avec:

Code :

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

1
2
Wb.Activate
Application.EnableEvents=False

ça déroule nikel

Un très grand merci à tous

VBA Erreur 1004 espace pile insuffisant [XL-2000]

Macros et VBA Excel

Discussions similaires

Partager

Partager