Macro « copier-coller » qui fait « planter » Excel

Question

J’utilise une macro toute simple qui consiste à ouvrir des fichiers et copier les données d’une feuille bien précise pour les coller vers un autre classeur qui récapitule en fait toutes ces données sur une seule feuille, chaque plage de données l’une en dessous de l’autre. Je l’ai déjà adapté et utilisé dans plusieurs exemples sans problème mais dans ce cas précis si je dépasse un certain nombre de fichiers, Excel s’arrête de fonctionner …c’est-à-dire que si dans mon répertoire contenant les fichiers à « copier » j’en ai 20 ça fonctionne, si j’en ai 30 je suis brutalement éjecter en cours d’exécution avec « Excel à cesser de fonctionner » et parfois apparait une box annonçant « Automation erreur 440 » mais pas systématiquement, c’est aléatoire… J’ai cherché mais ne sais pas comment faire pour résoudre ce problème et surtout j’aimerais comprendre pourquoi dans d’autres cas de figure cela fonctionne et cette fois ci non… Ci-joint le code, peut être que cela va « parler » à quelqu’un qui a déjà eu ce cas-là. Merci d’avance pour votre temps et votre aide Virginie Sub integration_marche() Dim marche As String Dim recap As String Dim Nb_ligne As Integer 'Ne met pas à jour le l'affichage pour aller plus vite Application.ScreenUpdating = False 'Récupère le nom du classeur courant recap = ActiveWorkbook.Name ' effacer les données de marche avant d integrer les nouvelles données mise à jour Range("A2").Select Selection.CurrentRegion.Select Selection.ClearContents Range("A2").Select ' chemin d acces des fichier marché a traiter ChDrive "z" ChDir _ "Z:\fr-cha-infocentre\CANAUX\VK\VKP\FDV Canal VK\Top Down VK VKP\Stats INCO grpt régionaux département VKP\Marché Inco" '============ 'boucle '=========== marche = Dir("*.*") Do While marche &lt;&gt; "" Workbooks.Open Filename:=marche ' va sur la feuille liste client pour chercher les données et faire controle * pour avoir toutes les données Sheets("Liste des clients").Select Range("A2").Select Selection.CurrentRegion.Select ActiveWindow.SmallScroll Down:=0 'les copier Selection.Copy ' retourne dans classeur recap Workbooks(recap).Activate Range("A1").Select 'cherche la dernier ligne donne son nombre et rajoute 1 car doit se positionner en dessous pour copier le reste Nb_ligne = Selection.CurrentRegion.Rows.Count + 1 'se positionne sur cette cellule variable Cells(Nb_ligne, 1).Select 'copier les valeurs Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'vider presse papier des fichiers copier Application.CutCopyMode = False 'ferme le fichier MARCHE actif Workbooks(marche).Close 'passage au fichier suivant marche = Dir Loop 'remet à jour le l'affichage Application.ScreenUpdating = True End Sub...

eriiic · Accepted Answer

Ah ben ton plaisir fait plaisir....

cocher le post avec la solution

PS : note que je n'ai utilisé aucun .Select.

Ca aussi c'est source de lenteurs inutiles. Tous les enlever, sauf s'il est obligatoire (de très rares cas, excel grogne pour te le dire)

jmd · Answer

A la condition que la table des clients soit "mettre sous forme de Tableau" avec toujours le même nom de tableau dans chaque fichier, alors menu Données, obtenir, de fichier, de classeur naviguer vers le répertoire des fichiers (il peut y en avoir 100 ou 500 ou plus ! ) valider Excel te laisse choisir quels Tableaux prendre "Charger" pas une ligne de code ! pour actualiser cliquer "actualiser" Excel est magique ...

thev · Answer

A mon avis, à un certain moment , tu récupères dans ta boucle un fichier qui n'est pas un classeur Excel. Je te conseillerais donc de contrôler l'extension de "marche" via cette instruction ext_marche = Right(marche, Len(marche) - InStrRev(marche, "."))...

dhany · Answer

Bonjour virg , ça confirme bien que la cause du plantage, c'est un des fichiers (ou quelques uns d'entre eux) ; aussi, tu peux essayer par tâtonnements, en continuant ce que tu as si bien commencé : prend uniquement les 30 premiers puis les 30 derniers et élargit la fourchette jusqu'à trouver le ou les fichier(s) coupable(s). bonne chance ! si ça ne donne rien, alors ce lien t'aidera peut-être : https://support.microsoft.com/fr-fr/help/2758592/how-to-troubleshoot-crashing-and-not-responding-issues-with-excel dhany...

jmd · Answer

Re je viens de vérifier, pas besoin de Tableau il suffit que les données soient sur des onglets portant le même nom dans chaque fichier Excel est génial, souple d'emploi. Sans complication. coup dur pour VBA, Microsoft travaille d''arrache-pied pour simplifier Power Query avec des fenêtres bien faites et un GUI très complet. bonne journée à tous...

dhany · Answer

Bonjour jmd , je crois que tu as plutôt voulu dire : « Excel est génial, souple d'emploi. Sauf complication. » (en l'occurrence le plantage évoqué dans ce sujet) dhany...

eriiic · Answer

Comme tu sembles sûr que c'est lié au nombre de fichiers ça doit plus être un problème de saturation mémoire, mal libérée. Essaie en travaillant avec les objets plutôt : Sub integration_marche() Dim marche As String Dim shRecap As Worksheet, wbSource As Workbook, shSource As Worksheet, pl As Range 'Ne met pas à jour le l'affichage pour aller plus vite Application.ScreenUpdating = False 'classeur courant Set shRecap = ActiveWorkbook.Sheets("Feuil1") ' ThisWorkbook ???? ' effacer les données de marche avant d integrer les nouvelles données mise à jour shRecap.Range("A1").CurrentRegion.Offset(1).ClearContents '.Offset(1) car je suppose que tu voulais préserver une ligne de titres ' chemin d acces des fichier marché a traiter ChDrive "z" ChDir "Z:\fr-cha-infocentre\CANAUX\VK\VKP\FDV Canal VK\Top Down VK VKP\Stats INCO grpt régionaux département VKP\Marché Inco" marche = Dir("*.xl*") Do While marche &lt;&gt; "" Set wbSource = Workbooks.Open(marche) Set shSource = wbSource.Sheets("Liste des clients") If Not shSource Is Nothing Then ' si la feuille existe 'copier-coller les valeurs dans classeur recap Set pl = shSource.Range("A2").CurrentRegion ' ("A1").CurrentRegion.Offset(1) si là aussi tu voulais exclure une ligne de titre shRecap.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(pl.Rows.Count, pl.Columns.Count).Value = pl.Value End If 'ferme le fichier MARCHE actif wbSource.Close Set shSource = Nothing Set wbSource = Nothing 'passage au fichier suivant marche = Dir Loop End Sub J'ai aussi modifié la copie. Pour un copié-collé de valeur une ligne suffit. Si ta feuille Recap est une feuille du classeur de la macro, remplace ActiveWorkbook par ThisWorkbook Je n'ai pas pu tester, il y aura peut-être un peu de débogage à faire... eric PS : évite de trop sauter de lignes, ça ne facilite pas la lecture. Utilise-les juste pour séparer les principales fonctions de ta procédures...