CODEWITHSUNDEEP
excelms-wordvba
Chris
Chris

Reputation: 2809

Insert VBA into Word from Excel

  1. I have a spreadsheet that generates a word document, formats it, and then adds a bunch of text to the document, based on what was all selected and entered into the spreadsheet. All of this works great.
  2. What I am trying to do is somehow insert VBA code into the Word document at the time it was being generated within Excel.

Here is the specific code that I am trying to insert - ultimately this needs to go into Word VBA:

Private Sub Document_Close()
    ActiveDocument.Saved = True
End Sub

I can’t seem to get Excel to insert this code into the Word document that is being generated. I know it is possible, but can’t figure it out. I tried several things and it accepts my code, but I have no idea where it is being inserted, because when I look for the inserted lines I can’t find them.

Here is the code that I am using to generate the Word document. Can someone please take a look and let me know how to insert the above code into this Document? Thanks for any help you can give.

Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add

With wrdApp.Selection
‘A bunch of formatting code and text inputting is listed here for the word document.
End With

wrdApp.Visible = True
wrdApp.Activate

Upvotes: 1

Views: 763

Answers (2)

Mark Balhoff
Mark Balhoff

Reputation: 2356

If the code you want to insert into Word is constant (as it probably should be), you could manually create a Word file with the code already in it and then use that file as a template instead of starting from a completely empty Word document.

Upvotes: 0

brettdj
brettdj

Reputation: 55692

If you have set Word to trust programmatic access to the VBA project then:

Sub ExceltoWord()
Dim strIn As String
strIn = "Private Sub Document_Close()" & Chr(10) & " ActiveDocument.Saved = True" & Chr(10) & "End Sub"
Set wrdApp = CreateObject("Word.Application")
Set wrddoc = wrdApp.Documents.Add
wrddoc.VBProject.VBComponents("ThisDocument").CodeModule.AddFromString strIn
wrdApp.Visible = True
wrdApp.Activate
End Sub

enter image description here

Upvotes: 2

Related Questions