Accessing Word by Excel VBA

[Paleo]

Dear friends,

I have a macro that creates a Word document, but I need to insert some VBA code in it too. Is it possible to insert this VBA in MS Word by an excel macro?
:dunno :dunno :dunno :dunno

[Jacob Hilderbrand]

Try this:


Option Explicit

Sub Macro1()
Dim AppWrd As New Word.Application
Dim Doc As Document
Dim Module As VBComponent
Set Doc = AppWrd.Documents.Add
With Doc.VBProject.VBComponents
Set Module = .Add(vbext_ct_StdModule)
End With
With Module
.CodeModule.AddFromString "Sub Macro1" & vbNewLine & _
"Dim Var1 As String" & vbNewLine & _
"Var1 = " & """" & "This is a test" & """" & vbNewLine & _
"End Sub"
End With
AppWrd.Visible = True
End Sub

Notice that the text for the module is in quotes, and if you want to put an actual quote in the text you need to put four quote marks.

Another option would be to base the word document off of a template that already has the code.

[Paleo]

Great Jacob,

thanks a lot. I will test it.

[Jacob Hilderbrand]

Also you will need to set a reference to the following from the VBE (Tools | References).
Microsoft Word 10.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3

Note that the actual numbers for theses references might be different for you based on the version of office that you have.

You will also need to allow programatical access to the Visual Basic project in Word.
Tools | Macro | Security | Trusted Sources

[Jacob Hilderbrand]

Great Jacob,

thanks a lot. I will test it.
You're Welcome

Take Care

[Paleo]

Worked just fine, many thanks!

[Jacob Hilderbrand]

Glad to hear it.

:fyi: When you want to mark a post solved don't edit your post. Select Thread Tools then Mark Solved. That way it will change the post title as well.