Macro trop grande

**Gwelawen** · 29/03/2021, 12h07

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 :

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
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 :

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

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 !

**Patrice740** · 29/03/2021, 12h18

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 !!!

**Gwelawen** · 29/03/2021, 12h40

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 :

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

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

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

**ChristianBosch** · 29/03/2021, 15h47

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 :

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

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

**Gwelawen** · 29/03/2021, 16h53

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!

**ChristianBosch** · 29/03/2021, 17h02

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 :

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

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 :

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

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

**Gwelawen** · 29/03/2021, 17h10

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

**ChristianBosch** · 29/03/2021, 17h54

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 :

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

 range("a" & i)

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

Code :

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

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 :

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

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

**Gwelawen** · 29/03/2021, 18h16

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.

**ChristianBosch** · 29/03/2021, 18h59

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.

**Patrice740** · 30/03/2021, 02h33

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)

**Gwelawen** · 30/03/2021, 11h13

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...

**ChristianBosch** · 30/03/2021, 11h39

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...

**Gwelawen** · 30/03/2021, 12h00

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.

**ChristianBosch** · 30/03/2021, 12h29

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.

**Gwelawen** · 30/03/2021, 13h52

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

Macro trop grande

Macros et VBA Excel

Discussions similaires

Partager

Partager