PDA

View Full Version : Reference a word doc embedded into excel



IRish3538
06-15-2012, 07:49 AM
I have an excel workbook that populates info into a word document thru bookmarks. How I currently have it setup is that the word document needs to be in the same folder as the excel wb so it can open it, populate, and save.

My question is how to reference the word doc if it's embedded inside the wb as an object. Below is how I currently reference the word doc. Any help would be much appreciated. Thanks!!


'Master Sub to Generate the Results Summary Document
Sub Final_Report_Doc()

Dim iTemp As Integer

'Ignore errors to allow for error evaluation
On Error Resume Next

iTemp = GetAttr(ActiveWorkbook.Path & "\Final Report Template.docx")

'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
Direxists = True
Case Else
Direxists = False
End Select

' Creates Word Document and merges all bookmarks.
If Direxists = False Then
msgresults = MsgBox("Please place the file 'Final Report Template.docx' in the same " & _
"folder as the workbook.", vbOKOnly, "Draft Document not Found")
Exit Sub

End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set Wb = ActiveWorkbook

Path = Wb.Path & "\Final Report Template.docx"

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

'Open document in word
Set docWord = pappword.documents.Add(Path)

'Sub that fills in all the bookmarks of the template
Call FinalReportFill

'Activate word and display document
With pappword
.Visible = True
.ActiveWindow.WindowState = 1
.Activate
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True

'Saves the document
ReportYear = Format(Sheet20.Range("c14"), "yyyy")
Path = ActiveWorkbook.Path & "\" & ReportYear & " " & Sheet20.Range("c4") & _
" - " & Sheet20.Range("c5") & " Final Report.docx"
docWord.SaveAs Path

Sheet1.Select

Exit Sub

Tinbendr
06-15-2012, 08:57 AM
Sub test()
Dim WDApp As Object
Dim WDDoc As Object
Dim WDObj As OLEObject

'Application.ScreenUpdating = False

Set WDObj = Sheets("Sheet1").OLEObjects(1) 'Specify object

WDObj.Activate
WDObj.Object.Application.Visible = False

Set WDApp = GetObject(, "Word.Application")

Set WDDoc = WDApp.ActiveDocument

WDDoc.Range(0, 0) = "This is some text, test #1"

Set WDDoc = Nothing
Set WDApp = Nothing
Set WDObj = Nothing

End Sub

With the WDDoc object, then you can manipulate the doc as required.

IRish3538
06-15-2012, 09:19 AM
Thanks David,

I don't have it embedded as an OLE though. I was hoping to embed the actual document inside, not just the link. I marco'd this:

ActiveSheet.Shapes("Object_Draft").Select

Selection.Verb Verb:=xlOpen

But I can't figure out how to set the object so excel can reference it once it opens.

IRish3538
06-15-2012, 09:43 AM
Disregard my thing about the linking.. The OLE method worked, however I do a "save as", but i can't keep the template document preserved.. the changes made get saved there as well. I'm thinking that there's no way to keep an uneditted version of the document in there unless....

At the beginning of the code, could I make a copy of the OLEobject, then reference the copy?

Tinbendr
06-15-2012, 10:02 AM
I found this. (http://stackoverflow.com/questions/9766334/how-to-use-embedded-dotx-in-word-documents-add-from-excel-vba)

Kenneth Hobs
06-15-2012, 10:28 AM
Sub OpenADoc()
Dim mpWord As Object
Dim mpDoc As Object

ActiveSheet.OLEObjects("aDoc").Verb xlPrimary
Do
Set mpWord = GetObject(, "Word.Application")
mpWord.Visible = True
Set mpDoc = mpWord.ActiveDocument
Loop Until Not mpDoc Is Nothing

With mpDoc
.Range(.Content.Start, .Content.End).Copy
End With

mpDoc.Close
mpWord.Quit
Set mpDoc = Nothing
Set mpWord = Nothing

Worksheets("Sheet3").Paste
End Sub


http://vbaexpress.com/forum/showthread.php?t=21619

IRish3538
06-15-2012, 11:09 AM
Ok I think I'm spinning off into a different direction a little bit, but I found this code to copy an OLE without openning it, but it pastes it as a scrap document and I can't figure out how name it and paste it as a word doc. When I try to open the Scrap.shs file thru VB i get an error that the file is corrupt, but have no issues openning the file (as a word doc) manually.

I tried changing the path to "activeworkbook.path & "\test.doc"
didn't work
and i tried to change the "Shell.Application" to "Word.Application"
no dice

any thoughts? i'm trying to avoid openning the OLE because i can't seem to close it out completely. The word application hangs, and I don't want to quit in the event a user has some other unrelated word doc open, it'll close it out.

ActiveSheet.OLEObjects("Object_Draft").Copy
'paste to activeworkbook's path
CreateObject("Shell.Application") _
.Namespace(ActiveWorkbook.Path) _
.Self.InvokeVerb "Paste"