Discussion :

[koubiac]

Bonjour à tous, cela fait un bye, je continue à me balader sur le forum en mode underground mais je suis un peu bloqué aujourd'hui donc je voudrai avoir des avis.

je suis sur une fonction VBA qui copie une feuille EXCEL "origine" afin d'en manipuler les données par la suite, l'imprimer et la supprimer. Cela fonctionne bien et mais j'ai une boucle qui peut être amenée à effectuer une centaine de fois cette manipulation et la copie est très longue (beaucoup de shapes dans la feuille en question), et lors de cette boucle, EXCEL freeze (page blanche + Microsoft EXCEL - Ne répond pas) puis tout redevient normal après traitement.

J'ai optimisé le code avec:

Code :

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

1
2
3
Application.Calculation = xlCalculationManual    
Application.ScreenUpdating = False
Application.DisplayAlerts = False

La fonction de copie native à EXCEL:

Code :

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

1
2
ThisWorkbook.Sheets("RECAP").Copy After:=Worksheets("RECAP")
ActiveSheet.Name = "TRAIT_RECAP"

Mais la lenteur ne provient pas du code VBA car je constate une lenteur identique en manuel.

Avez-vous une idée? je pensais placer la feuille "origine" à copier dans un autre classeur EXCEL et faire une copie de ce fichier (surement moins long??) puis injecter les infos en appelant le classeur EXCEL ainsi copié??

Vous avez peut-être une solution plus simple à mettre en place, mon gros problème étant le freeze "Page Blanche" du style tout à planter... je voudrai constater l'avancer du traitement avec une progressbar...

Merci pour votre aide.


P.S. Précision importante: je suis sur Excel 2010 et Windows 7

[vinc_bilb]

Bonjour,

Quelques points avec plus de questions que de réponses:

1.

mais j'ai une boucle qui peut être amenée à effectuer une centaine de fois cette manipulation et la copie est très longue (beaucoup de shapes dans la feuille en question)

=> est-ce-que vous avez besoin des shapes? Sinon, il vaut mieux créer une feuille et ne copier que le range des données ....;
=> pourquoi en boucle et une centaine de fois?

2. Votre solution classeur externe me parait assez compliquée et je ne pense pas qu'elle vous fasse gagner du temps ....

3. Progressbar: c'est gérable assez facilement via le statusbar. Voir exemple sous http://www.developpez.net/forums/d15...e-progression/

[koubiac]

=> est-ce-que vous avez besoin des shapes? Sinon, il vaut mieux créer une feuille et ne copier que le range des données

Oui, pour des raisons de mise en page.

=> pourquoi en boucle et une centaine de fois?

il s'agit d'une dématérialisation de rapports, les feuilles générées sont ensuite enregistrées en PDF, il peut y avoir une centaine de rapports...

2. Votre solution classeur externe me parait assez compliquée et je ne pense pas qu'elle vous fasse gagner du temps ....

Mon gros problème n'est pas vraiment par rapport au temps que cela prend... Mais plutôt sur le fait qu'Excel freeze sur des traitements assez long, page blanche avec un Microsoft EXCEL - ne répond pas". Apparemment, c'est un problème depuis EXCEL 2010 (cf. forum Microsoft)

je viens d'essayer avec un DoEvents dans la boucle mais rien de mieux.. je regardes pour ton exemple de StatusBar mais j'ai un doute du fait qu'Excel affiche une page blanche le temps du traitement...

est-ce que je suis plus clair? Merci en tout cas d'avoir répondu.

Bon, je suis un peu coincé, il semble que ce soit un problème à partir d'EXCEL 2010 et pas de solution selon Microsoft...

https://support.microsoft.com/fr-fr/kb/2790615

Le code fonctionne mais cela fait juste bizarre de voir une page blanche lors de ce traitement avec de temps en temps une fenêtre Publication qui s'affiche lors de l'impression...

Tester avec une statusbar, idem... je ne vois pas trop comment faire là...

[patricktoulon]

bonjour
peut etre si les shapes ne sont pas necessaire ne copier que la plage

1 copier le usedrange
2 ajouter un sheet
3paste

voir
1 variable plage devient usedrange
2 ajout de sheet
3 nouveausheet.cells(1,1).resize(page.rows.count,plage.columns.count)=plage

[koubiac]

merci pour ton post Patrick, je vais tenter ça et essayer aussi mon idée du FileCopy avec un classeur externe pour voir si cela limite la casse...

je vous donne des news

[koubiac]

je reviens clôturer ce thread comme convenu.

mes tests se sont avérés infructueux hier soir, il s'agit bien d'une lenteur EXCEL et on ne peut pas outrepasser comme expliqué sur le lien vers le site Microsoft + haut.

Le freeze était en grande partie dû à la copie des feuilles en question et, la nuit portant conseils, j'ai finalement trouvé un moyen de réaliser mon traitement sans copier la feuille en question.

Lenteur aussi de la fonction "ExportAsFixedFormat" me permettant de générer une impression PDF, code nettement optimisé en passant le paramètre "Quality:=xlQualityMinimum" à la place du Standard et en passant ce paramètre "Application.Calculation = xlManual" à la place de xlAutomatic (temps d'attente ainsi divisé par 10).

je vous place ci-dessous une procédure publique qui désactive les évènements trouvée sur un site anglophone, cela pourra peut être servir à quelqu'un:

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
Public Sub BlockEvScreenCalc(Optional ByVal bWlacz As Boolean = True, _
                             Optional Status As String = "")
    On Error Resume Next
    With Application
        If bWlacz Then
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
            .Cursor = xlDefault
        Else
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .Cursor = xlWait
        End If
        .StatusBar = Status
    End With
End Sub

à coller dans un module.

Pour appeler la procédure:

Code :

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

1
2
3
4
 
Call BlockEvScreenCalc(False, "Texte affiché dans la statusbar")
'votre traitement
Call BlockEvScreenCalc(True)

Voili voilou, merci encore de m'avoir apporter des éléments, j'ai gagné du temps.

Cdt, koubiac.