Fichier excel trop lourd et trop lent

[richert90]

Bonjour à tous,

J'ai un fichier Excel avec macros qui, à l'ouverture, va importer 21 fichiers CSV les uns à la suite des autres sur un onglet puis va réaliser automatiquement des graphiques, stats etc ... sur ces fichiers importés.
L'application fonctionne très bien car les 21 fichiers avaient chacun une taille de 65ko environ.

Par contre, j'ai reçu 21 nouveaux fichiers et chacun de ces 21 fichiers a une taille de 800ko environ ce qui est très différent. En lançant le fichier Excel, j'ai eu des erreur d’exécution '6' à cause des Integer (dépassement de capacité). En effet quand je parcours les lignes des fichiers que j'ai importé, on dépasse vite les 35000 lignes..
Donc j'ai remplacé le type des variables concernées en Long au lieu de Integer. Le problème c'est que l'application bug au démarrage: ça met une éternité de temps à ouvrir (et encore je suis même pas sur que ça va s'ouvrir, j'ai attendu 10minutes et j'ai coupé....). je sais vraiment pas quoi faire pour arranger cela..
Quelqu’un a t-il une idée?

Merci d'avance de m'aider pour ce problème

 

[Misange]

Re : Fichier excel trop lourd et trop lent

Bonjour
Sans voir le fichier et le code, difficile de dire grand chose.
Fais des tests en limitant à 2 puis 3 puis 10 fichiers pour voir quand et où ça coince. Il faut probablement vider la mémoire, prévoir un enregistrement intermédiaire en cours de procédure.
Faire également très attention à mettre le mode de calcul sur manuel en début d'importation, pour ne le remettre en auto qu'à la fin.

 

[richert90]

Re : Fichier excel trop lourd et trop lent

Bonjour Misange et merci d'avoir répondu

En effet je ne peux pas joindre le ficheir excel avec macros car il fonctionne avec un fichier de configuration et des ficheirs csv à importer.
Comment fait-on pour vider la mémoire ?
Concernant le mode de calcul manuel, cela est fait pour quoi?

 

[Misange]

Re : Fichier excel trop lourd et trop lent

Tu peux au moins montrer ton code d'importation et donner un exemple de tes classeurs importés (sans données confidentielles et seulement quelques lignes).

Si à chaque ligne que tu importes, tu as des formules qui se mettent en branle, ton classeur va effectivement mettre des plombes à tout faire. Donc il faut mettre le mode de calcul sur manuel ,en début de macro et le rétablir sur auto en fin de macro.

Je t'invite +++ à lire l'excellente page de Laurent Longre sur l'accélération du code en VBA
Ce lien n'existe plus
tout ça y est détaillé

 

[emiliedusud]

Re : Fichier excel trop lourd et trop lent

hello ! bonjour Misange,
ce que j'ai fait dans ce cas c'est d'enregistrer le fichier au format 97 ou 2003 et le remettre au format 2007; (je suis sur 2007)
sa a fonctionné chez moi essais

 

[richert90]

Re : Fichier excel trop lourd et trop lent

Re,

Je vais en effet essayer de joindre un fichier exemple.
Merci pour le lien qui en effet n'est pas mal du tout..

 

[richert90]

Re : Fichier excel trop lourd et trop lent

Bonjour emilie
A quel moment tu le remet au format 2007?

 

[richert90]

Re : Fichier excel trop lourd et trop lent

Re bonjour à tous;

Voici un fichier joint avec le code pour l'importation. Il marche bien. J'ai réussi à l'optimiser en utilisant des for.. each au lieu des for.
Néanmoins si vous pensez que ce code peut encore être amélioré n'hésitez pas

De plus, après l’import des 21 fichiers, je trace des graphiques. Pour cela, je dois récupérer des données à partir des fichiers importés et je fais souvent des boucles comme do.. while etc.... Comment je peux améliorer ceci afin d'optimiser le code et améliorer le temps d'éxécution ?

Merci d'avance,

 

[Misange]

Re : Fichier excel trop lourd et trop lent

RE,

Les boucles sont toujours une source de lenteur importante avec excel. La solution de choix c'est de s'en passer en injectant les données dans un array (tableau interne), en travaillant dans ce tableau si nécessaire puis en réinjectant le résultat en une seule opération dans la feuille.
Je te laisse découvrir tout ça. Ca demande un certain apprentissage et tu devras t'y coller mais c'est le jour et la nuit en termes de rapidité
Ce lien n'existe plus
Pour ce qui est de tes graphiques, je ne vois pas trop l'intérêt de passer par une macro mais n'ayant pas de données sous les yeux c'est difficile d'en dire plus. Tu fais ton graphique, tu remplaces les références aux plages de données par des références à des plages nommées et dans ta macro d'importation tu nommes tes plages. Ou sinon tu travailles avec des tableaux excel (onglet accueil/mettre sous forme de tableau, voir ici l'avantage : Ce lien n'existe plus). De cette façon plus de boucle.

 

[richert90]

Re : Fichier excel trop lourd et trop lent

Re,

En effet, je serais bien parti pour la méthode des tableaux, assez simple, mais le problème c'est que pour mes graphiques, j'ai des plages de données variables en fonction d'une valeur dans une colonne. Par exemple en colonne A, j'ai soit 1,2,3 et je vais faire 3 graphiques:
le 1er pour voir l'évolution d'un paramètre dans la colonne B en fonction du temps avec la valeur en colonne A=1
le 2eme pour voir l'évolution du paramètre dans la colonne B en fonction du temps avec la valeur en colonne A=2
le 3eme pour voir l'évolution du paramètre dans la colonne B en fonction du temps avec la valeur en colonne A=3

Donc pour cela les tableaux ne seront pas vraiment appropriés ?!

Merci d'avance

 

[richert90]

Re : Fichier excel trop lourd et trop lent

Re,

Pour les graphiques, afin de sélectionner des plages de données très grandes et afin d'éviter de parcourir des milliers de lignes, est-ce possible d'utiliser du SQL? (SELECT maColonne etc...)?

 

[Misange]

Re : Fichier excel trop lourd et trop lent

Désolée mais on parle dans le vide. Sans un petit classeur exemple de ce que sont tes données, et tes graphiques j'arrête, je ne suis pas devin.
Mets en ligne un classeur avec un exemple anonymisé de ce que tu récupères après importation, et les types de graphiques que tu en sors, que ce soit par macro ou sans macro.

 

[richert90]

Re : Fichier excel trop lourd et trop lent

Re,

J'avais posté un exemple de fichier de l'import
Pour les graphiques, j'ai un exemple pour lequel j'ai ouvert un nouveau post (il y a un classeur joint ici ):
https://www.excel-downloads.com/threads/simplification-dun-code.220775/