View Full Version : Excel & Word Integration

10-18-2007, 10:08 AM
Hi everyone.. I've got two questions for you guys that i've scoured the ends of the internet for and have come up empty.

I've been writing an excel wb that generates a report onto a word template via several bookmarks. The macro currently looks for a word template named "Request Generator.doc" in the same path that the wb is in.

My first question (if possible/practical) is if instead of having the macro find a word template in the same folder, could I embed the word template into the wb and use it that way? I know I can kick the template into excel, but I'm having trouble running my macro that way. The reason I ask is because several people will be using the wb and I'd like to make it into one nice little excel package for them.

My second question is if anyone knows a word macro that goes something like "For eack link in document... lock all links".. Any clues?

Here's my code as is, calling the word template:

Option Explicit
Dim ws As Object
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim Path As String

Sub BCMerge()
' Creates Word Document Loan Request and merges all bookmarks.
Application.ScreenUpdating = False

Set wb = ActiveWorkbook
Path = wb.Path & "\Request Generator.dot"

'Create a new Word Session
Set pappWord = CreateObject("Word.Application")

'Open document in word
Set docWord = pappWord.Documents.Add(Path)

'Fills all Bookmarks and selects Sheet 1

pappWord.Selection.GoTo What:=wdGoToBookmark, Name:="Bk1"
pappWord.Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False

'Activate word and display document
With pappWord
.Visible = True
.ActiveWindow.WindowState = 0
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

10-18-2007, 10:33 AM
Ok... I think I've found the answer to my first question... I was goofing around and added this

Set wb = ActiveWorkbook
Path = wb.Path

'Create a new Word Session
Set pappWord = CreateObject("Word.Application")

'Select the word object from sheet1
Sheet1.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary

'Open document in word
Set docWord = GetObject(, "Word.Application").ActiveDocument
'Fills all Bookmarks and selects Sheet 1

The only thing that is an issue is that it automatically saves within the excel doc when you exit the word doc.... so if you exit the word doc and re run the macro, all the info is double pasted. And, the user has to know to choose "Save as Copy" as the object doesn't take on the proprerties of a word template in that it creates a new doc.

Does anyone know if there's a way to not save changes on exit?