Ouverture fichier trop longue

Question

J'ai un fichier Excel qui me sert de base de données, celle-ci est rempli à l'aide d'autres feuilles et elle sert aussi à sortir des états de suivis et de reporting. Le problème étant que cette base de données est quasi vide d'où le poids du fichier de 7.5 Mo seulement. Comme vous vous en doutez, ce fichier est rempli de macro et de recherchev principalement. J'ai déjà mis les calculs manuels pour essayé de réduire le temps d'ouverture mais celui-ci reste encore beaucoup trop long pour 7.5 Mo. En effet, le fichier met entre 7 et 8 minutes à s'ouvrir. Auriez-vous des astuces, des idées pour réduire le chargement des macros ou quoique ce soit ? Je prend tout ce qui peut aider. Je vous remercie d'avance ...

thev · Accepted Answer

Après analyse, effectivement la macro d'ouverture n'est pas en cause. Le problème se situe au niveau de ta feuille "liste". La solution est : 1- de créer une feuille vierge 2 -de copier les cellules A1 à AO147 de la feuille liste actuelle 3- de sauvegarder les noms relatifs à la feuille liste via écriture d'une macro (ou les reconstituer 1 par 1 sur la nouvelle feuille) 4- supprimer la feuille "liste" 5- renommer la feuille vierge en "liste" Une copie de la feuille "liste" actuelle ne résoudra rien...

thev · Answer

Cette ouverture longue est liée à l'exécution de la macro d'ouverture du fichier.

D'après mon expérience, il serait fort possible qu'une boucle de cette macro balaye la plage complète d'une feuille (environ 1 million de lignes et 15000 colonnes) au lieu de se limiter à la plage utilisée.

galopin01 · Answer

Je vais pas te faire plaisir, mais... ton fichier est considérablement pollué par des résidus de construction, et alourdi de maladresses nombreuses. Quand au code il n'y a pratiquement aucun effort d'optimisation. Ce ne sont que des cascades d'enregistrements de macros... Dans ces conditions difficile de te dire par quoi commencer d'autant que j'ai du mal à évaluer exactement ce que je vois : Toute tes BDD sont formatées au moins pour un millier de lignes. Est-ce parce que tu en as supprimé les données pour nous le communiquer ou est-ce un formatage "de précaution" ? A priori il n'y a aucune raison de préformater un BD avec une ligne de plus que son contenu... A plus forte raison de poser des formules matricielles sur des plages vides... Ce fichier pratiquement vide ne devrait pas dépasser 2 Mo (C'est son poids un fois légèrement compressé) Il n'y a aucune raison qu'il pèse 8 Mo. Chez moi il se charge en 12 secondes environ. Ce qui est acceptable. Après il est difficile d'apprécier l'impact des liens externes vu que de chez moi le pb ne se pose pas. Je considère que, -en l'état actuel- ce fichier ne tiendra pas longtemps la route. Je préconise une refonte complète des BDD et des macros avant de provoquer un plantage irrémédiable... Mais pas une révision Olé, Olé hein ! Une construction professionnelle... Bon c'est pas vraiment mon domaine de prédilection mais je commencerai en premier par externaliser tous les graphiques qui sont les plus gourmands en Ko, ressources et mémoire... A+...

FINDRH · Answer

Ci joint une proposition pour dimensionner tes TCD au contenu ' TAILLE = zone de référence pour TCD Vdlb = Range("A1").End(xlDown).Row = dernière ligne à partir de la première cellule du tableau d'une colonne pleine Vdcb = Cells(1, Columns.Count).End(xlToLeft).Column = dernière colonne 'selection des cellules pour adresse type A1 etc mc = Worksheets("nomtafeuille").Cells(1, 1).Address() md = Worksheets("nontafeuille").Cells(Vdlb, Vdcb).Address Bzone = "Manques!" & mc & ":" & md ' crée la reference de type a1:h28 par ex, Bzone représente cette zone Sheets.Add ActiveSheet.Name = "nomfeuilTCD" Range("a3").Select ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ Bzone).CreatePivotTable TableDestination:= _ "nomfeuilTCD!R3C1", TableName:="Tableau croisé dynamique1" si tu enregistres la création du TCD tu remplaces la partie haute de ton enregistrement par ces lignes Si cela peut t'aider Cordialement FINDRH...

thev · Answer

Oui. IL faut absolument repartir sur une feuille vierge.

ci-dessous une macro 1 pour récupérer les noms relatifs à la feuille liste, sur la feuille active dans les colonnes A et B

 Sub stocker_noms_liste()

    Dim nom As Name
    Dim i As Integer
    Dim nom_feuille As String
    Dim cellule_début As Range

    Set cellule_début = ActiveSheet.[A1]
    On Error Resume Next
    i = 0
    For Each nom In ThisWorkbook.Names
         nom_feuille = nom.RefersToRange.Worksheet.Name
         If Err.Number = 0 _
         And nom_feuille = "Liste" Then
             cellule_début.Offset(i).Value = nom.Name
             cellule_début.Offset(i, 1).NumberFormat = "@"
             cellule_début.Offset(i, 1).Value = nom.RefersTo
            i = i + 1
        End If
        Err.Clear
    Next

End Sub

ci-dessous une macro 2 pour recréer les noms relatifs à la feuille liste, à partir de la feuille active dans les colonnes A et B

Sub recréer_noms_liste()

    Dim plage, ligne As Range

    Set plage = ActiveSheet.[A1:B51]
    For Each ligne In plage.Rows
        ThisWorkbook.Names.Add Name:=ligne.Columns(1), RefersTo:="" & ligne.Columns(2) & ""
    Next

End Sub

thev · Answer

Si tu as bien stocké les noms relatifs à liste sur "Feuil2" via ma macro1

1- supprime tous les noms relatifs à liste

2- supprime la feuille "Liste'

3- renomme "Feuil1" en "Liste"

4- recrée les noms relatifs à liste via ma macro 2

Pourquoi cette manip ?

J'ai constaté qu'en supprimant la feuille "Liste" et en recréant une nouvelle, le temps d'ouverture était correct.

thev · Answer

Content d'avoir pu te dépanner. L'explication : Certainement des données inattendues et invisibles se trouvant à la fin des lignes ou des colonnes de ta feuille...