Résolu Consolidation qui plante

popeyem · 5 Juillet 2010

Bonjour à tous,
Je reviens avec ma macro consolidation et un nouveau pb.

Je consolide environ 50 fichiers avec le code ci-dessous. Le souci est que je dois avoir atteint une certaine limite, parce qu'il ne fonctionne plus correctement. Il me fait la première zone nickel ("estimé") mais les 3 autres ne sont pas faites correctement voire pas du tout (mauvais chiffres comparés avec une autre méthode). Je dois donc faire ces zones une par une (6 mn à chaque fois) pour avoir les bonnes données...

Et il y a pas 5mn, je viens de me rendre compte que si j'arrête la macro disons au bout de 1mn, mes chiffres sont consolidés et sont bons.. incroyable, non ?!

Quelqu'un aurait-il une idée de la source du pb ?? Jusqu'à combien de fichiers peut-on consolider d'un coup?

Merci d'avance pour vos éclairages.

[cpp]Option Explicit

Private Sub CommandButton1_Click()
Dim Chemin As String, pays_source As String, i As Integer, j As Integer
'Boucler sur les fichiers pays du répertoire
Chemin = ActiveWorkbook.Path
pays_source = Dir(Chemin & "\" & "* 3 YP by CBU.xls")

Application.ScreenUpdating = False
Do While pays_source <> ""
'Estimé
Range("C13:X50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C3:R103C24", _
Function:=xlSum

'Budget
Range("Z13:AU50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C26:R103C47", _
Function:=xlSum

'2011-2012
Range("AW13:BR50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C49:R103C70", _
Function:=xlSum

'2012-2013
Range("BT13:CO50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C72:R103C93", _
Function:=xlSum
Loop
'effacer les pourcentages apparus
For i = 0 To 4
For j = 0 To 4
Range(Cells(13, 2 * i + 23 * j + 15), Cells(37, 2 * i + 23 * j + 15)).ClearContents
Next
Next
Application.ScreenUpdating = True
End Sub
[/cpp]

zeb · 6 Juillet 2010

Ben là, tu m'en bouches un coin ! :ouch:

Salut PopeyeM, j'ai pas la moindre idée du début d'un commencement d'une réponse :/

popeyem · 7 Juillet 2010

BSalut Zeb, le forum,
c'est assez incroyable.

Bon comme je ne me laisse pas abattre, j'ai essayé autre chose! J'ai repris en partie le code que tu avais posté Zeb sur un ancien topic a propos de la macro consolidation. Elle marche bien, les chiffres sont consolidés correctement pour les 4 périodes. Mais du coup j'aimerais l'améliorer.

Ah et oui, les lignes 35à 38 ne s'éxecutent pas. Ma macro s'arrête sur le dernier fichier ouvert, du coup je reste avec mes 50 fichiers ouverts ! Por que?

[cpp]Option Explicit

Private Sub CommandButton5_Click()
Dim Chemin As String, pays_source As String, i As Integer, j As Integer
'Boucler sur les fichiers pays du répertoire
Chemin = ActiveWorkbook.Path
pays_source = Dir(Chemin & "\" & "* 3 YP by CBU.xls")

Application.ScreenUpdating = False
Do While pays_source <> ""
Workbooks.Open Chemin & "\" & pays_source
pays_source = Dir()
Loop

'Estimé
Range("C13:X50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]OMB'!R66C3:R103C24", _
Function:=xlSum

'Budget
Range("Z13:AU50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]OMB'!R66C26:R103C47", _
Function:=xlSum

'2011-2012
Range("AW13:BR50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]OMB'!R66C49:R103C70", _
Function:=xlSum

'2012-2013
Range("BT13:CO50").Consolidate Sources:= _
"'" & Chemin & "\" & "[* 3 YP by CBU.xls]OMB'!R66C72:R103C93", _
Function:=xlSum

Do While pays_source <> ""
Workbooks(pays_source).Close
pays_source = Dir()
Loop
Application.ScreenUpdating = False
For i = 0 To 4
For j = 0 To 4
Range(Cells(13, 2 * i + 23 * j + 15), Cells(37, 2 * i + 23 * j + 15)).ClearContents
Next
Next
Application.ScreenUpdating = True
End Sub
[/cpp]
Donc j'aimerais faire un appel de "procedure générale" a savoir 'louverture des fichiers. Comme ça, une fois ouverts, je peux faire marcher mes 4 macros (sur 4 feuilles différentes). Je place ça sur "Thisworkbook" ? ou sur un module ?

Xie xie (pour diversifier)

zeb · 7 Juillet 2010

Mais qu'est-ce que tu fiches ? Ta boucle commencée ligne 10 (Do..) devrait s'arrêter ligne 38 (Loop...). Or Ligne 13 tu l'arrêtes, et ligne 35 tu la recommences, dans le vide, forcément. Relis ton code.

Quand on gère plusieurs feuilles, plusieurs classeurs, on précise systématiquement le classeur et la feuille !

Fais des sous-procédures pour te simplifier la vie. Ça allonge le code, mais ça le rend plus compréhensible. N'ai-je pas déjà dit de simplifier chaque gros problème en petits problèmes, puis de les résoudre un par un.

Code:

Sub ConsolideClasseur(nomfichier As String, nomsources As String)
	Dim wb As Workbook
	Dim ws As Worksheet
	
	Set wb = Workbooks.Open(nomfichier)
	Set ws = wb.Worksheets(1)
	
	' // Estimé, Budget, 2011-2012, 2012-2013
	ws.Range("C13:X50"   ).Consolidate Sources:= "'" & nomsources & "'!R66C3:R103C24",  Function:=xlSum
    ws.Range("Z13:AU50"  ).Consolidate Sources:= "'" & nomsources & "'!R66C26:R103C47", Function:=xlSum
    ws.Range("AW13:BR50" ).Consolidate Sources:= "'" & nomsources & "'!R66C49:R103C70", Function:=xlSum
    ws.Range("BT13:CO50" ).Consolidate Sources:= "'" & nomsources & "'!R66C72:R103C93", Function:=xlSum
    
    wb.Close SaveChanges:=True
End Sub

Code:

Dim Chemin As String
Chemin = ActiveWorkbook.Path
pays_source = Dir(Chemin & "\" & "* 3 YP by CBU.xls" )
Do While pays_source <> ""
	ConsolideClasseur pays_source, Chemin & "\" & "[* 3 YP by CBU.xls]OMB"
	pays_source = Dir()
Loop

popeyem · 8 Juillet 2010

Bonsoir bonsoir
Alors voila ce que j'ai fait:
- j'ai crée un bouton "ouvrir les fichiers" dans chaque feuille. J'ai normalement à l'utiliser 1 seule fois ET ensuite je consolide.
- j'ai fait une procedure evenementielle: a la sortie de mon classeur consolidé, hop la je ferme tous les classeurs source.

Exemple pour 1 feuille (parmi lles 4)
[cpp]Option Explicit

Private Sub CommandButton2_Click()
Dim Chemin As String, pays_source As String
'Boucler sur les fichiers pays du répertoire
Chemin = ActiveWorkbook.Path
pays_source = Dir(Chemin & "\" & "* 3 YP by CBU.xls")

Application.ScreenUpdating = False
If MsgBox("Ceci va ouvrir tous les fichiers pays Phase 1. Continuer ?", vbYesNo) <> vbYes Then
Exit Sub
End If
Do While pays_source <> ""
Workbooks.Open Chemin & "\" & pays_source
pays_source = Dir()
Loop
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
Dim Chemin As String, pays_source As String, i As Integer, j As Integer
'Boucler sur les fichiers pays du répertoire
Chemin = ActiveWorkbook.Path
pays_source = Dir(Chemin & "\" & "* 3 YP by CBU.xls")

Application.ScreenUpdating = False
Do While pays_source <> ""

'Estimé
Range("C13:X50").Consolidate "'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C3:R103C24", xlSum

'Budget
Range("Z13:AU50").Consolidate "'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C26:R103C47", xlSum

'2011-2012
Range("AW13:BR50").Consolidate "'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C49:R103C70", xlSum

'2012-2013
Range("BT13:CO50").Consolidate "'" & Chemin & "\" & "[* 3 YP by CBU.xls]TAX'!R66C72:R103C93", xlSum

pays_source = Dir()
Loop

For i = 0 To 4
For j = 0 To 4
Range(Cells(13, 2 * i + 23 * j + 15), Cells(37, 2 * i + 23 * j + 15)).ClearContents
Next
Next
Application.ScreenUpdating = True
End Sub[/cpp]
Et fermeture
[cpp]Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Chemin As String, pays_source As String
'Boucler sur les fichiers pays du répertoire
Chemin = ActiveWorkbook.Path
pays_source = Dir(Chemin & "\" & "* 3 YP by CBU.xls")

'If workbooks(pays_source) = open Then
Do While pays_source <> ""
Workbooks(pays_source).Close
pays_source = Dir()
Loop
End If
End Sub[/cpp]Pour la fermeture, j'ai un souci ligne 9. Si je n'ai aucun fichier source ouvert (consultation simple de mon fichier), bé ça plante! normal. Comment eviter cela ? (du style a si un pays_source est ouvert, ferme le, sinon rien)

Sinon le reste marche, et la conso est quand même très rapide une fois que tous les classeurs sont ouverts.

Par contre je ne comprends toujours pas pourquoi, avec 10, 20, 30 classeurs source la fonction consolidate marchait très bien sans avoir à ouvrir les fichiers (et ça mettait 2-3mn à tout casser). Maintenant ça mets 10mn pour un travail imparfait :??:

Merci à tous!

zeb · 9 Juillet 2010

Ta fermeture ne peut pas fonctionner !
Il faut mettre le If dans la boucle Do While, m'enfin !

Pour vérifier si un classeur est ouvert :

Code:

Dim wb As Workbook
Dim ouvert As Boolean

ouvert = False
For Each wb In Workbooks
    If wb.Name = "nom du classeur" Then
        ouvert = True
        Exit For
    End If
Next

popeyem · 12 Juillet 2010

Bonjour le forum,

Merci Zeb pour cet indice. J'ai essayé et reéssayé de me servir de ton dernier post, mais pas moyen d'aboutir à qqch de bon.
Soit je n'avais qu'un seul fichier "pays" pris en compte (le premier de la boucle), soit rien du tout.
Egalement en utilisant "wb.Name", c'est d'abord "Me" qui entre dans la boucle et qu'il faut donc exclure. Mais après je n'arrivais pas à boucler sur les fichiers pays ouverts.

Donc j'ai fais ça et ça marche !! [cpp]Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Chemin As String, pays_source As String, wb As Workbook, ouvert As Boolean
'Boucler sur les fichiers pays du répertoire
Chemin = ActiveWorkbook.Path
pays_source = Dir(Chemin & "\" & "* 3 YP by CBU.xls")

Application.ScreenUpdating = False
Do While pays_source <> ""
On Error Resume Next
Workbooks(pays_source).Close
pays_source = Dir()
Loop
Application.ScreenUpdating = True
End Sub[/cpp]Est-e que ça à l'air bon, ou mélange des genres ?
Merci

zeb · 12 Juillet 2010

C'est presque très bien fait.
Il manque le On Error GoTo 0 pour remettre les choses en place à l'issue et surtout, il faut sortir l'instruction On Error Resume Next de la boucle, juste avant le Do.

Mais sinon, qu'est-ce que c'est moche. Beurk. :vomi:
Faire planter l'application exprès ! :pfff:

Code:

Option Explicit

Code:

Function EstOuvert(wb_name As String) As Boolean
    Dim wb As Workbook
    EstOuvert = False
    
    For Each m_wb In Workbooks
        If wb.Name = wb_name Then
            EstOuvert = True
            Exit For
        End If
    Next
End Function

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim pays_source As String, wb As Workbook, ouvert As Boolean
        
    pays_source = Dir(ActiveWorkbook.Path & "\" & "* 3 YP by CBU.xls")
    Do While pays_source <> ""
        If EstOuvert("pays_source") Then Workbooks(pays_source).Close
        pays_source = Dir()
    Loop
End Sub

Ce n'est pas comme si ce n'était pas un problème récurrent.

Vous devez être connecté pour voir les liens.

, la même chose mais pour les feuilles !

popeyem · 13 Juillet 2010

Salut salut
Bon ben écoute Zeb, comme d'hab (je peux maintenant le dire), c'est nickel, ça marche aussi très bien. *Du bon boulot !* :sol:

Quand tu dis que c'est moche (cf ton dernier post), qu'est ce qui ne va pas dans "l'esthetique" du code?

zeb · 13 Juillet 2010

Pour sortir de chez toi, tu pètes toutes les portes, y compris les portes de placard et tu regardes ce qu'il y a derrière ou tu réfléchis en te disant qu'avec la clef de la porte d'entrée, ça peut suffire ?

En fait, dans ton code, tu fais planter l'application exprès et ensuite tu gères l'erreur. Sauf que globalement, c'est une façon très moche de faire. Imagine qu'une erreur autre que celle que tu penses gérer survienne ? Par exemple, Workbooks(pays_source).Close pourrait planter non parce que le classeur n'existe pas, mais parce qu'une modif a été apportée, qu'il faut enregistrer et que le disque est plein, ou autre. Bref, tu ne gères en fait plus rien, tu passes outre ! C'est moche, quoi.

Une bonne façon de programmer, c'est d'accepter le

Vous devez être connecté pour voir les liens.

. C'est une façon de considérer que l'élégance du code consomme beaucoup de temps et donc d'argent, et qu'il faut mieux faire un truc crade mais qui marche dans 99% des cas, plutôt que d'attendre la perfection. Pour le 1% restant, un expert règlera le problème à prix d'or certes, mais si rarement qu'on rentrera dans les frais.

Ceci est donc à réserver à ceux qui programment très bien et depuis fort longtemps. Car il faut savoir éviter de nombreux pièges, souvent récurrents pour atteindre un niveau de % de risque honnête.

A suivre ce bon principe, le débutant négligeant économisera sa peine et produira rapidement et à moindre coût un programme qui marchera à ... 9%. Il faudra soit payer pour tout refaire, soit s'attacher les services d'un expert fort coûteux pour gérer les innombrables erreurs - c'est mon job actuel [

atch]

Donc si tu veux continuer à profiter de mes conseils, je te propose d'oublier un temps le worse is better, et d'apprendre à faire les choses proprement, ou du moins à le me laisser croire

popeyem · 13 Juillet 2010

En voilà de sages paroles. Je reconnais bien là l'attachement de l'expert pedadgogue à diffuser son message de "bien faire et proprement s'il vous plait" !
Je suis egalement partisan du travail bien fait, donc tant qu'à faire, une ptite question un peu naïve du débutant que je suis ne fait pas de mal pour recadrer le contexte et faire avancer les choses

Merci du conseil avisé, pour ma part, je vais m'attacher à appliquer le concept du faire bien tout court!

popeyem · 13 Juillet 2010

Meilleure réponse sélectionnée par popeyeM.

zeb · 13 Juillet 2010

Merci pour ton état d'esprit.

A te lire, bientôt.

BEBER1212 · 13 Juillet 2010

zeb :

Oui, mais si tous les débutants font directement les choses proprement, il n'y aura plus de boulot pour toi Zeb, ou alors tu deviendras beaucoup moins coûteux :lol:

zeb · 14 Juillet 2010

Résolu Consolidation qui plante

popeyem

zeb

popeyem

zeb

popeyem

zeb

popeyem

zeb

popeyem

zeb

popeyem

popeyem

zeb

BEBER1212

zeb