Generate a word document with excel

Question

I've modificated the code following the last advices that I've recived. However I still can't generate the new word document using my VBA code from Excel.

I would like that Excel will able to modificate text content and generate a new document that it will open when I click on the button.

Here what I've done

And this is the code and it says I have a "91 error" for the line worddoc.Close SaveChanges:=False

there the code:

Sub export_Automatisation_MT() 'nom du maccro
    Dim wordapp As Word.Application
    Dim worddoc As Word.Document

    Set wordapp = CreateObject("word.application") 'crée une application word
    Set worddoc = wordapp.Documents.Open("O:\Projets\RAZAN BORKI\01 MEMOIRE TECHNIQUE.docx") 'document de base

    Call traitement_champs(worddoc) 'traite le texte contenu dans excel

    worddoc.Close SaveChanges:=False
    wordapp.Quit
End Sub


Private Sub traitement_champs(worddoc As Word.Document)
    Dim ws As Worksheet
    Dim derLigne As Integer
    Dim i As Integer
    Dim ctrl As Object 'control du contenu

    Set ws = Sheets("Mémoire technique")

    derLigne = ws.Range("C" & Rows.Count).End(xlUp).Row

    For i = 3 To derLigne
        On Error Resume Next 'si erreur, pas de contrôle de contnu de texte dans word
        
        Debug.Print "champ:" & ws.Cells(i, 3).Value & "valeur:" & ws.Cells(i, 4).Value
        
        For Each ctrl In worddoc.SelectContentControlsByTitle(ws.Cells(i, 3).Value)
            ctrl.Range.Value = ws.Cells(i, 4).Value
        Next ctrl
    Next i
End Sub

Why do you need to open the document, make changes and then save it unchanged? — Eugene Astafiev, Apr 28 '22 at 10:23
I just saw that you asked nearly the same question yesterday (https://stackoverflow.com/q/72035088/16578424). I would have expected, that you update your code according to the the answer you got - but you didn't ... :-( — Ike, Apr 28 '22 at 11:30
Mhh i think i've included all the advices of who answered me: i've modified this "Private Sub traitement_champs(worddoc As Word.Document)" and this "Call traitement_champs(worddoc)"; i've eliminates the 3 first lines and i've tried other think that didn't work and i always have the same error 91. This is why i asked a more specific question and i've said that "2 I've modificated the code following the last advices that I've recived" — Hoppigresi, Apr 28 '22 at 12:53
Hmm - there were already the advises to omit "on error resume next" and to omit `call` ... — Ike, Apr 28 '22 at 13:11
Yes, i've deleted it, i ran the code but i hade a new error on the line "For Each ctrl In worddoc.SelectContentControlsByTitle(ws.Cells(i, 3).Value) ctrl.Range.Value = ws.Cells(i, 4).Value " That's why i've wrote it again... — Hoppigresi, Apr 28 '22 at 13:17
Ignoring errors is not a good idea to get rid of fix errors! You got the error due to ctrl.range.value ... if you change that to .text you won't receive an error. — Ike, Apr 28 '22 at 16:32

score 2 · Answer 1 · answered Apr 28 '22 at 10:00

2

First of all: you should remove the on error resume next!

You don't use call and brackets to call a sub. Simply use traitement_champs worddoc

Make following changes to the sub: Dim ctrl as Word.ContentControl instead of as Object

ctrl.Range.Text = ws.cells(i,4)).value (Word ranges only have the text attribute - no value (like Excel))

worddoc.Close SaveChanges:=False looks ok - except it doesn't make sense to me, to open and edit the doc and then close it without saving.

answered Apr 28 '22 at 10:00

Ike

9,580
4
13
29

Thank you, but i don't understand this : You don't use call and brackets to call a sub. Simply use traitement_champs worddoc. You mean that instead of : "traitement_champs(worddoc As Word.Document)" i have to write traitement_champs(worddoc)? Also, for worddoc.Close SaveChanges:=False I would like that the word document will open to see the modification and then i will save it manually. But when i generate it, there is no document open – Hoppigresi Apr 28 '22 at 10:12
_You don't use call and brackets to call a sub_ why? It's perfectly valid [syntax](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/call-statement) – BrakNicku Apr 28 '22 at 10:17
@BrakNicku: From your link _If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded._ I suppose this is the source for the error ... – Ike Apr 28 '22 at 10:25
Your sub-definition is ok. But when calling it from your main routine you only use `traitement_champs worddoc`. - If you want to check the edited word doc, than remove `worddoc.Close SaveChanges:=False` and `wordapp.Quit` - otherwise the doc gets closed w/o saving and word app gets closed too. – Ike Apr 28 '22 at 10:28
There is no _function_ in the code, only subs, so how would it matter? – BrakNicku Apr 28 '22 at 10:28
@BrakNicku: you are right - in this case it doesn't make a difference. – Ike Apr 28 '22 at 10:32

Generate a word document with excel

1 Answers1