Angelique_C
New Member
Hi,
I have some excel vba code where I'm trying to insert a picture into a Word Document. The picture needs to be selected from a folder, with the jpg named according to an ID number selected from the current row in an excel worksheet ("query"). Once the correct jpeg has been selected, it needs to be pasted into a word table cell. I think there's a few problems with the code I'm using, the first one being that I'm getting a "user defined type not defined" message for the Word.Application. Can anyone help?
I have some excel vba code where I'm trying to insert a picture into a Word Document. The picture needs to be selected from a folder, with the jpg named according to an ID number selected from the current row in an excel worksheet ("query"). Once the correct jpeg has been selected, it needs to be pasted into a word table cell. I think there's a few problems with the code I'm using, the first one being that I'm getting a "user defined type not defined" message for the Word.Application. Can anyone help?
Code:
Sub OpenWordCopyPic ()
Dim i As Integer
Dim word_app As Word.Application
Dim word_doc As Word.Document
Dim word_tab As Word.Table
Dim word_rng As Word.Range
Dim ws As Worksheet, iExcelRow As Integer, iWordRow As Integer, currName As String, wordName As String, photoFile As String
Dim foundIt As Boolean
Set word_app = CreateObject("word.Application")
Set word_doc = word_app.Documents.Open("S:\Projects\2015_Template_LPO.docx")
word_app.Visible = True
Set ws = Worksheets("Query")
For iExcelRow = 2 To ws.UsedRange.Rows.Count ' go through the list of names
currName = ws.Cells(iExcelRow, 2) ' save the name so can add to the Word document
photoFile = "S:\2015\" & ws.Cells(iExcelRow, 1) & ".jpg" ' determine the file name of the new photo
If Dir(photoFile) = "" Then 'if no matching file in first location, check another folder for a match
photoFile = "S:\2014\" & ws.Cells(iExcelRow, 1) & ".jpg"
End If
If Dir(photoFile) = "" Then
MsgBox "A new photo for " & currName & " cannot be found."
Else
foundIt = False
Set word_tab = word_doc.Tables(1)
For iWordRow = 1 To word_tab.Rows.Count
If Dir(photoFile) <> "" Then word_rng.InlineShapes.AddPicture photoFile, False, True ' add the new photo
word_rng.InsertBefore wordName + vbCrLf ' put the name under the photo with a carriage return and line feed
foundIt = True
Exit For
End If
Next
If Not foundIt Then
MsgBox currName & " could not be found"
End If
End If
Next
MsgBox "Ok all finished", vbInformation
End Sub
Last edited by a moderator:
