Embed documents in word: run a macro from Excel

[naveen_acheanz]

Dear reader,

I am Naveen. I am learning to code macros.

I need to run a macro from Excel. The routine cycles though the rows of data in sheet2 and uses word properties to create each document and save it disk. This part of routine is working fine.

I need to embed multiple work documents to the active document. So I am calling the function embed_doc. I am getting error in this function. “Object required”. I am not sure hw to add a book mark.
Need help to resolve the issue with embedding documents.
I need to run a macro from Excel. The routine cycles though the rows of data in sheet2 and uses word properties to create each document and save it disk. This part of routine is working fine.

I need to embed multiple work documents to the active document. So I am calling the function embed_doc. I am getting error in this function. “Object required”. I am not sure hw to add a book mark.
Need help to resolve the issue with embedding documents.


Macro code:Function embed_doc(testcase_id As String, saveasname As String)

Dim varDest As String
Dim varFIle As String
Dim appWD As Object


varDest = Sheets("dest_path").Range("B1").Value
vardest1 = varDest & testcase_id
varFIle = Dir(vardest1 & "\*.txt")


Set appWD = CreateObject("word.application")
appWD.Visible = False

Do

appWD.Documents.Open saveasname
With appWD
'.Visible = True
app.Selection.InlineShapes.AddOLEObject ClassType:="Package", Filename:= _
vardest1 & varFIle, LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _
"C:\Windows\system32\packager.dll", IconIndex:=0, IconLabel:=varFIle
End With


Selection.TypeParagraph


varFIle = Dir()

Loop Until varFIle = ""

Application.ScreenUpdating = True


End Function




Sub TResult_doc()
' Creates memos in word using Automation (late binding)
Dim WordApp As Object
Dim Data As Range, message As String
Dim Records As Integer, i As Integer
Dim Region As String, SalesAmt As String, SalesNum As String
Dim saveasname As String
Dim varPath As String
Dim testcase_id As String
Dim BMRange As Object


varPath = Sheets("dest_path").Range("A1").Value

'On Error GoTo ErrorCode

' Start Word and create an object
Set WordApp = CreateObject("Word.Application")

' Information from worksheet
Set Data = Sheets("Sheet2").Range("A1")
message = Sheets("Sheet2").Range("Message1")

' Cycle through all records in Sheet1
Records = Application.CountA(Sheets("Sheet2").Range("A:A"))
For i = 2 To Records
' Update status bar progress message
Application.StatusBar = "Processing Record " & i

' Assign current data to variables
testcase_id = Data.Cells(i, 1).Value
Structure = Data.Cells(i, 2).Value
Test_case_desc = Data.Cells(i, 3).Value
expected_result = Data.Cells(i, 4).Value
Client_no = Data.Cells(i, 5).Value
Account_no = Data.Cells(i, 6).Value
Account_type = Data.Cells(i, 7).Value
Bank_id = Data.Cells(i, 8).Value
Branch_no = Data.Cells(i, 9).Value

' Determine the file name
saveasname = varPath & "\" & testcase_id & ".docx"

' Send commands to Word
With WordApp
.Documents.Add
With .Selection
.Font.Size = 14
.Font.Bold = True
.ParagraphFormat.Alignment = 1
.TypeText Text:="TestCase_Id: " & testcase_id
.TypeParagraph
.TypeParagraph
.Font.Size = 12
.ParagraphFormat.Alignment = 0
.Font.Bold = False
.TypeText Text:="Date:" & vbTab & _
Format(Date, "mmmm d, yyyy")
.TypeParagraph
.TypeParagraph
.TypeText Text:="expected_result :" & vbTab & expected_result
.TypeText Text:="Clinet# :" & vbTab & Client_no
.TypeParagraph
.TypeText Text:="Account # :" & vbTab & Account_no
.TypeParagraph
.TypeText Text:="Account type :" & vbTab & Account_type
.TypeParagraph
.TypeText Text:="Bank Id :" & vbTab & Bank_id
.TypeParagraph
.TypeText Text:="Branch Number :" & vbTab & Branch_no
.TypeParagraph

End With
.ActiveDocument.SaveAs Filename:=saveasname

Call embed_doc(testcase_id, saveasname)
End With
Next i

ErrorCode:
If Err.Number = 0 Then
MsgBox Records - 1 & " memos were created in Word " & WordApp.Version & " and saved in " & varPath
Else
MsgBox "An error occurred."
End If

' Kill the object
WordApp.Quit false
Set WordApp = Nothing

' Reset status bar
Application.StatusBar = ""

' Show the folder
Shell "explorer.exe " & varPath, vbNormalFocus
end sub



Regards,
Naveen N

 

[Andrew Poulsom]

Welcome to MrExcel.

What is app on this line?

app.Selection.InlineShapes.AddOLEObject ClassType:="Package", Filename:= _
    vardest1 & varFIle, LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _
    "C:\Windows\system32\packager.dll", IconIndex:=0, IconLabel:=varFIle

 

[naveen_acheanz]

Welcome to MrExcel.

What is app on this line?

app.Selection.InlineShapes.AddOLEObject ClassType:="Package", Filename:= _
    vardest1 & varFIle, LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _
    "C:\Windows\system32\packager.dll", IconIndex:=0, IconLabel:=varFIle

Hi Andrew,
Thanks, I corrected the selection as appWd. Now with this code I am able to embed document.

appWD.Selection.InlineShapes.AddOLEObject ClassType:="Package", Filename:= _
vardest1 & "\" & varFIle, LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _
"C:\Windows\system32\packager.dll", IconIndex:=0, IconLabel:=varFIle
End With

The emded documents are inserted at the top of the document.
What to know how can I emded document in a particular location.
What to do this since I am inserting some text from excel. Want to embed documents after text copied from excel.

Regards,
Naveen N

 

[Andrew Poulsom]

Why don't you do the embedding within your TResult_doc procedure (rather than calling another procedure)? That way Selection may be in the right place.

 

[naveen_acheanz]

Thanks a lot Andrew . The solution suggested worked fine.