Help Opening Embedded Word File

[SilverSN95]

Hello, my question has two parts:
1. Does embedding a word file into excel actually embed the entire word file? I need the bookmarks to remain in the word file for my macro to work. The file is actually a .dot, will it still behave as a template?
2. How can I open the embedded file from my macro
Set dest = wdApp.Documents.Add(???????)

If I could embed and then open the .dot from my excel workbook via a macro it would cure a lot of headaches!
Thanks.

[mdmackillop]

Can you post a sample?

[SilverSN95]

Here is the macro now. It currently opens the file from a predetermined location. Hopefully by attaching the word files I could avoid having to change the doc locations. I cant post the word template file or the workbook for confidentiality reasons.
[vba]Public Sub FillEduTemplate()
Dim wdApp As Object
Application.ScreenUpdating = False
Dim sourceWB As Workbook
Dim dest As Object
Dim docLocation As String

On Error Resume Next
Set wdApp = GetObject(, "Word.Application") 'Look for instance of Word
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application") ' Otherwise create one
End If
On Error GoTo 0

Set sourceWB = ThisWorkbook
Set sourceWS = sourceWB.Sheets("Express Profile")
docLocation = sourceWB.Sheets("Express Profile").Range("N43").Value 'location of template adress

If Not FileThere(docLocation) Then 'test that location is valid or exit sub
MsgBox "Could Not Locate Specified Education Template File Location"
Exit Sub
End If


On Error Resume Next
Set dest = wdApp.Documents.Add(docLocation) ' Open template from word instance
If Err.Number <> 0 Then 'If file cannot be opened
MsgBox "Error opening Education Profile Binder"
Exit Sub
End If

wdApp.Visible = True 'Allow user to see word application
dest.Activate

With sourceWS ' Copy the appropriate fields from excel into word bookmarks
dest.Bookmarks("Insured_Name").Range.Text = .Range("C5")
dest.Bookmarks("Insured_Name2").Range.Text = .Range("C5")
dest.Bookmarks("Insured_Name3").Range.Text = .Range("C5")
dest.Bookmarks("Insured_Name4").Range.Text = .Range("C5")
dest.Bookmarks("New_Renewal").Range.Text = .Range("I44")
dest.Bookmarks("Broker_Contact").Range.Text = .Range("I11")
dest.Bookmarks("Street").Range.Text = .Range("C7")
dest.Bookmarks("City").Range.Text = .Range("C8")
dest.Bookmarks("State").Range.Text = .Range("C9")
dest.Bookmarks("Zip").Range.Text = .Range("C10")
dest.Bookmarks("Business_Type").Range.Text = .Range("C14")
dest.Bookmarks("Policy_Number").Range.Text = .Range("C53")
dest.Bookmarks("Effective").Range.Text = .Range("C12")
dest.Bookmarks("Expiration").Range.Text = .Range("C13")
dest.Bookmarks("Premium").Range.Text = Int(.Range("I40"))
dest.Bookmarks("Agency").Range.Text = .Range("I5")
dest.Bookmarks("Brok_State").Range.Text = .Range("I9")
dest.Bookmarks("Brok_City").Range.Text = .Range("I8")
dest.Bookmarks("Brok_Street").Range.Text = .Range("I7")
dest.Bookmarks("Brok_Zip").Range.Text = .Range("I10")
dest.Bookmarks("Producer_Number").Range.Text = .Range("I14")

End With

'If Include is Yes, this will call a function to autofill that template as well
If sourceWS.Range("F36").Value = "Yes" Then
Call FillPATemplate
End If

Application.ScreenUpdating = True
End Sub[/vba]

[mdmackillop]

Can you create a simple sample with a couple of bookmarks. I've not tried to access word in this way, so really need something to work with.

[SilverSN95]

Ok, I think this should help.

[mdmackillop]

[VBA]'Open document
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary[/VBA]

[SilverSN95]

Thanks, but in order to use my macro, I need a reference "dest" that points to this opened file and I don't think that method will support that.
If I could just specify the location of the object, I can pass it to the .add I am already using, but I can't seem to find a way to do that.

[mdmackillop]

A little crude, but try this.
[VBA]
Public Sub FillEduTemplate()
Dim wdApp As Object
Application.ScreenUpdating = False
Dim sourceWB As Workbook
Dim dest As Object
Dim docLocation As String
Dim wddoc
'Open document
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
On Error Resume Next
Set wdApp = GetObject(, "Word.Application") 'Look for instance of Word
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application") ' Otherwise create one
End If
On Error GoTo 0


Set sourceWB = ThisWorkbook
Set sourceWS = sourceWB.Sheets("Express Profile")
docLocation = sourceWB.Sheets("Express Profile").Range("N43").Value 'location of template adress
If Not FileThere(docLocation) Then 'test that location is valid or exit sub
MsgBox "Could Not Locate Specified Education Template File Location"
Exit Sub
End If

On Error Resume Next

Set dest = wdApp.Documents("Document in Book2.xls") ' Open template from word instance
dest.SaveAs "C:\AAA\Test.doc"
dest.Close
Set dest = wdApp.Documents.Open("C:\AAA\Test.doc")

If Err.Number <> 0 Then 'If file cannot be opened
MsgBox "Error opening Education Profile Binder"
Exit Sub
End If
wdApp.Visible = True 'Allow user to see word application
dest.Activate
With sourceWS ' Copy the appropriate fields from excel into word bookmarks

dest.Bookmarks("Street").Range.Text = .Range("C7")
dest.Bookmarks("City").Range.Text = .Range("C8")
dest.Bookmarks("Zip").Range.Text = .Range("C9")

End With
Application.ScreenUpdating = True
End Sub
[/VBA]

[SilverSN95]

Thanks mckillop, that might work but I want to try one more thing:
I found that this code will open the object:
ThisWorkbook.Sheets("Sheet1").OLEObjects("Object 1").Activate

Unfortunately I could not find a member that would just give me a path to the embedded file.
Is there any way I could set the above statement to the word object?

[mdmackillop]

I believe it is opening as a new document and thus has no path. As you can see, there is no doc extension to the file name.
You might also consider using Word Tasks to get a handle on the file.