Macro trop grande

Version imprimable

Macro trop grande

Bonjour à tous,

Je suis plus que débutante sur VBA, partez du principe que je n'y connais rien…
Cela fait presque une journée que je cherche une solution à mon problème sur tous types de forums alors que je suis presque sûre que la solution est toute bête (quand on connait).

L'idée de base : j'ai un document Word modèle rempli de signets et je veux une macro dans Excel qui me génère directement ce modèle Word rempli avec les bonnes infos.
Tout marchait très bien jusqu'à ce qu'Excel me dise que la macro était trop grande et là je n'arrive pas à la couper… j'ai essayé plein de choses trouvées sur des forum mais comme j'y connais rien je l'ai probablement mal exécuté…

Voici la macro :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Sub Generer_PI_ESP() Dim NDF As String, NDF2 As String Dim WordApp As Word.Application Dim WordDoc As Word.Document NDF = ActiveWorkbook.Path & "\plan d'inspection récipient.docx" NDF2 = Sheets("Générer un PI ESP").Range("M3").Text & "\" & "D5370PIE" & Sheets("Générer un PI ESP").Range("D2").Text & ".doc" On Error Resume Next Set WordApp = CreateObject("Word.Application") Set WordDoc = WordApp.Documents.Open(NDF, ReadOnly:=False) With WordApp .Visible = True .Activate WordApp.ActiveDocument.Bookmarks("REC").Range.Text = UCase(Sheets("Générer un PI ESP").Range("D2").Value) WordApp.ActiveDocument.Bookmarks("Fonc").Range.Text = UCase(Sheets("Générer un PI ESP").Range("D6").Value) WordApp.ActiveDocument.Bookmarks("RF_PI").Range.Text = UCase(Sheets("Générer un PI ESP").Range("D3").Value) WordApp.ActiveDocument.Bookmarks("Ind_PI").Range.Text = UCase(Sheets("Générer un PI ESP").Range("D4").Value) WordApp.ActiveDocument.Bookmarks("Red").Range.Text = Sheets("Générer un PI ESP").Range("F3").Value
[à peu près 478 lignes de ce type…]
Code:

1 2 3 4 5 6 7 8 9 10 End With WordDoc.Application.ActiveDocument.SaveAs NDF2 Set WordDoc = Nothing Set WordApp = Nothing ActiveWorkbook.Close SaveChanges:=Nothing End Sub
Si quelqu'un peut m'aider, tout mon service en sera reconnaissant ! :)

29/03/2021, 11h18
Patrice740

Bonjour,

Dès qu'une procédure (ou fonction) dépasse une centaine de lignes, il faut se poser la question de l'architecture du code :
- essayer de le découper en petites procédures qui réalisent des fonctions élémentaires.
- essayer de réaliser une boucle plutôt qu'une longue série d'instructions similaires.

Dans ton cas, il serait plus simple de passer par un tableau de correspondance entre les bookmarks et les cellules qui permettrait de simplifier la macro en effectuant une boucle.

EDIT : Supprimes le On Error Resume Next Ça cache la poussière sous le tapis !!!
29/03/2021, 11h40
Gwelawen
Merci pour la rapidité de réponse!!!

J'ai essayé de la couper en deux avec une fonction Call mais je n'ai pas réussi… Je m'y suis probablement mal prise je sais pas…
Pour la boucle, je n'ai pa réussi vu qu'il y a 2 variables dans ma ligne, le signet et la case excel quand j'essaye :
Code:

1 2 3 4 5 For i = 1 to 30 For J = 102 to 131 WordApp.ActiveDocument.Bookmarks("DES_NOM" & i).Range.Text = Sheets("Générer un PI ESP").Range("C" & j).Value Next i Next j
Ca ne marche pas…

L'idéal serait surement de pouvoir la découper comme ça par la suite je pourrai faire des fichiers encore plus grands (ce qui sera très utile dans mon service) mais si je n'y arrive pas déjà là je vais avoir du mal à m'aventurer plus loin :lol:

EDIT : pour le on error resume next pour ma défense, il vient du copier coller que j'ai fait d'une macro déjà existante et j'avoue qu'il m'arrangeait bien :roll:

Salut, rentres tous tes noms de signets dans une colonne Excel et après tu n'auras plus qu'a faire une boucle pour les parcourir. Car au moins sur excel tu as 1 millions de lignes alors que ton code VBA pourra pas en avoir autant.

Du genre :
Code:

1 2 3 4 5 6 7 dim signet as string dim i as integer for i=1 to feuil1.Cells(Rows.Count, 1).End(xlUp).Row signet = Feuil1.range("a" & i).value WordApp.ActiveDocument.Bookmarks(signet).Range.Text = Sheets("Générer un PI ESP").Range("C" & i).Value next
Tu peux même rajouter autant de signets que tu veux ça fonctionnera.

Après effectivement le "on error resume next" peut cacher des ratés mais c'est sur que si tu as une plage variable de signets il vaut mieux le mettre pour éviter de planter sur 1/10 eme des lignes. Mais il faut avant s'assurer que ça fonctionne bien.

A+

CB

29/03/2021, 15h53
Gwelawen

Etant donné que les valeurs à récupérer Range("C" & i).Value sont dans différentes colonnes ou lignes, cela marche quand même ? Ou bien je dois renouveler pour chaque colonne ?

En tout cas merci beaucoup pour la réponse!

Citation:

Envoyé par Gwelawen

Etant donné que les valeurs à récupérer Range("C" & i).Value sont dans différentes colonnes ou lignes, cela marche quand même ? Ou bien je dois renouveler pour chaque colonne ?

En tout cas merci beaucoup pour la réponse!

Ca depend comment est organisé ton tableau en sortie mais rien ne t'empeche de faire une opération mathématique pour décaler tes valeurs. Tu peux faire une changement de variable ou bien directement le faire dans ton range

exemple :
Code:

1 2 3 4 5 6 7 dim signet as string dim i as integer for i=1 to feuil1.Cells(Rows.Count, 1).End(xlUp).Row signet = Feuil1.range("a" & i).value WordApp.ActiveDocument.Bookmarks(signet).Range.Text = Sheets("Générer un PI ESP").Range("C" & i+100).Value next
ou :
Code:

1 2 3 4 5 6 7 8 dim signet as string dim i as integer dim j as integer for i=1 to feuil1.Cells(Rows.Count, 1).End(xlUp).Row j=i+100 signet = Feuil1.range("a" & i).value WordApp.ActiveDocument.Bookmarks(signet).Range.Text = Sheets("Générer un PI ESP").Range("C" & j).Value next
Apres a toi d'adapter en fonction de tes besoins.

NB: fais attention le feuil1 est un CodeName c'est pas le nom de la feuille cf : https://silkyroad.developpez.com/VBA/FeuilleDeCalcul/

CB

29/03/2021, 16h10
Gwelawen

Pour les lignes oui c'est ce que j'ai fait du coup par contre pour les colonnes… Du coup j'ai fait des boucle par type de signet et par colonne de valeur correspondante, ça marche mais je me doute que c'est pas la manière la plus optimale. Car comme tu disais je peux rajouter 1000 lignes excel mais pas dans VBA...

Mais du coup d'après ce que tu expliques (enfin si je comprends correctement, ce qui n'est pas dit^^) je pense que le fichier excel de base n'est pas construit de manière à simplifier et optimiser au mieux la macro

Citation:

Mais du coup d'après ce que tu expliques (enfin si je comprends correctement, ce qui n'est pas dit^^) je pense que le fichier excel de base n'est pas construit de manière à simplifier et optimiser au mieux la macro

Il vaut toujours mieux adapter dans la mesure du possible tes fichiers pour avoir un code qui marche dans tous les cas, après comme je t'ai dit tu peux adapter le code que je t'ai fournit, d'ailleurs si tu veux faire des opérations mathématiques sur les colonnes c'est aussi possible car le :

Code:

range("a" & i)

Puisqu'ici on ne pointe qu'une seule cellule et pas une plage c'est l'adaptation de :

Code:

range(i,1)

Le "1" étant la première colonne donc la colonne A.

Donc tu peux aussi jouer avec les colonnes. Si c'est ça ta question.

tu peux donc boucler sur plusieurs lignes et plusieurs colonnes genre comme ca :
Code:

1 2 3 4 5 6 7 8 9 dim signet as string dim i as integer dim j as integer for j=1 to 10 'ici mets le nombre de colonnes qui vont accueillir les valeurs des signets for i=1 to feuil1.Cells(Rows.Count, 1).End(xlUp).Row signet = Feuil1.range(i,j).value WordApp.ActiveDocument.Bookmarks(signet).Range.Text = Sheets("Générer un PI ESP").range(j,i).Value next next
tu notera que tes noms de signets dans ton classeurs devront donc correspondre a l'arrangement que tu veux en sortie

Si tu veux que la valeur du signet "REC" soit sur la feuille "Générer un PI ESP" en D2 il faut que tu mette "REC" en D2 dans la feuil1.

J’espère avoir bien compris ce que tu voulais.

CB

29/03/2021, 17h16
Gwelawen

Citation:

Envoyé par ChristianBosch

tu notera que tes noms de signets dans ton classeurs devront donc correspondre a l'arrangement que tu veux en sortie

Si tu veux que la valeur du signet "REC" soit sur la feuille "Générer un PI ESP" en D2 il faut que tu mette "REC" en D2 dans la feuil1.

J’espère avoir bien compris ce que tu voulais.

CB

Je ne veux pas insérer de valeurs dans mon fichier excel, au contraire, je veux que la valeur de mes cases excel remplacent mes signets dans mon modèle word.
29/03/2021, 17h59
ChristianBosch

Oui j'ai confondu, vu que tu code sur Excel je pensais que c’était pour importer les valeurs dedans.

Mais du coup je comprends pas, les signets servent bien a identifier un paragraphe ? Si tu as toujours les mêmes noms, tu fais un tableau avec en colonne A les nom des signets et dans les autres colonnes les valeurs de ceux ci pour chacun de tes cas. Et tu fais ta boucle.

Si tu peux pas le faire alors il va falloir passer par un tableau de correspondance dans tous les cas avec par exemple en A le nom des signets et en B la valeurs des cellules correspondantes ou un truc du genre.
30/03/2021, 01h33
Patrice740

Re,

Tu peux si besoin utiliser 3 colonnes :
- le signet
- l'adresse de la donnée
- un code pour l’opération à faire sur cette donnée (ex. Ucase)
30/03/2021, 10h13
Gwelawen

Citation:

Envoyé par ChristianBosch

Mais du coup je comprends pas, les signets servent bien a identifier un paragraphe ? Si tu as toujours les mêmes noms, tu fais un tableau avec en colonne A les nom des signets et dans les autres colonnes les valeurs de ceux ci pour chacun de tes cas. Et tu fais ta boucle.

Si tu peux pas le faire alors il va falloir passer par un tableau de correspondance dans tous les cas avec par exemple en A le nom des signets et en B la valeurs des cellules correspondantes ou un truc du genre.

En fait, le but de tout ça c'est de pouvoir générer un grand nombre de documents qui seront tous uniformisés.
Mon fichier excel sert à récupérer des infos dans une base de données qui doivent ensuite figurer sur les documents word.
Du coup mes signets correspondent à des cases ou des trous dans mon modèle word, car la trame doit être la même pour tous les documents.
Parfois les infos sont répétitives donc oui j'ai souvent des signets X1, X1, X3... Y1, Y2, Y3... etc...
Mais plus j'avance, plus je me rend compte que quand on veut faire ce genre de chose qu'il faut vraiment réfléchir à la macro avant d'élaborer le fichier excel pour se simplifier le travail après.
Le soucis c'est que je pars d'un fichier excel qui existait déjà avant l'idée de faire une macro...
30/03/2021, 10h39
ChristianBosch

De manière générale c'est toujours mieux d'avoir une trame de données bien structurées, si il faut gérer du cas par cas c’est toujours très compliqué.

Tu peux pas créer un fichier intermédiaire xlsm dans lequel tu récupère les données, tu les organise et en suite tu lance la macro d'export vers word ?

Ce que Patrice propose est faisable ?

Si c'est le bazar dans tes data c'est peu être tout simplement impossible...
30/03/2021, 11h00
Gwelawen

Organiser en 3 colonnes toutes les données me parait compliqué, enfin surtout fastidieux, mais j'y penserais pour le prochain document que je créerai…

Pour les datas c'est pas moi qui les gère, la base de données est générée par une extraction dans excel depuis un autre logiciel de la boîte.
30/03/2021, 11h29
ChristianBosch

Citation:

Pour les datas c'est pas moi qui les gère, la base de données est générée par une extraction dans excel depuis un autre logiciel de la boîte.

Mais du coup ça veut dire que tu as une trame répétitive si c'est un logiciel qui te fait une extraction, si tu fait comme Pactrice t'as dit, un tableau de correspondance avec en colonne A les nom de tes signets et en colonne B l'adresse des informations correspondantes.

Si tu peux pas t'en sortir comme ça je vois pas alors.
30/03/2021, 12h52
Gwelawen

La base de données est une extraction avec des données répétitives oui. Mais le fichier excel sur lequel je travaille est un regroupement des informations de la base de données car tout est mélangé et permet également de récupérer des images stockés sur un serveur et il est organisé un peu comme le fichier word au final