Insert data (image) into Word file using Access VBA

Question

I stole the format from an answer to a similar question : Insert Image into Word by using VBA

The problem I am getting is that it keeps not actually setting the variables.

Public Sub SignDoc(fileName As String, filetype As String)

FileCopy "\\SERVER01\InventoryObjects\" & filetype & ".docx", _ 
     "\\SERVER01\SignatureCaptures\" & fileName & " .docx"

Dim Word As Word.Application
Dim doc As Word.Document
Dim filePath As String: filePath = "\\SERVER01\SignatureCaptures\" & _ 
     fileName & ".docx"
Dim SHP As Word.Document
Dim strTmp As String: strTmp = "SignatureBM" 'bookmark in appropriate file
Dim strPath As String: strPath = "\\SERVER01\SignatureCaptures\" & fileName & ".gif"

Set Word = CreateObject("Word.Application")
Set doc = Word.Documents.Open(filePath)
Set SHP = doc.Bookmarks(strTmp).Range.InlineShapes.AddPicture(fileName:=strPath, _
    LinkToFile:=False, SaveWithDocument:=True)
With SHP
    'this will keep ratio
    '   .WrapFormat.type = 1  'wdWrapTight
    '   .WrapFormat.type = 7  'wdWrapInline
    .LockAspectRatio = -1    ' msoTrue
    'this will adjust width to 0.5 inch
    '.Width = wd.InchesToPoints(2.5)
    ' .Width = wd.CentimetersToPoints(2.66) * 2.5
    ' .Height = wd.CentimetersToPoints(3.27) * 2.5
    '   .ScaleHeight = 150
End With

End Sub

The code breaks at:

Set doc = Word.Documents.Open(filePath)

Though it highlights at Set SHP, it is never actually setting "doc" to be anything. Checking the variable in the immediate window shows that this line isn't actually successful, and returns the Runtime error 91 ...

Any suggestions would be helpful.

Answer 1

After doing much research and trial and error, I have come to the conclusion that this is a case where "you simply can not do" what I was trying to do.

The solution was to move the file to the local drive, perform the image insertion there, and then save the file to the Server location where it needed to be

as in the code below:

Public Sub SignDoc(fileName As String, filetype As String)

FileCopy "\\SERVER01\InventoryObjects\" & filetype & ".docx",
 "C:\users\" & GetUserName() & "\documents\" & fileName & ".docx"


Dim oWord As Word.Application
Dim doc As Word.Document
Dim filePath As String: filePath = "C:\users\" & GetUserName() & "\documents\" & fileName & ".docx"
Dim SHP As Object
Dim strTmp As String: strTmp = "SignatureBM" 'bookmark in appropriate file
Dim strPath As String: strPath = "C:\users\" & GetUserName() & "\documents\" & fileName & ".gif"
Dim oSel As Object

Set oWord = CreateObject("Word.Application")
Set doc = oWord.Documents.Open(filePath)
Set SHP = doc.Bookmarks(strTmp).Range.InlineShapes.AddPicture(fileName:=strPath, LinkToFile:=False, SaveWithDocument:=True)
doc.SaveAs ("\\SERVER01\SignatureCaptures\" & fileName & ".docx")
With SHP
    'this will keep ratio
    '   .WrapFormat.type = 1  'wdWrapTight
    '   .WrapFormat.type = 7  'wdWrapInline
    .LockAspectRatio = -1    ' msoTrue
    'this will adjust width to 0.5 inch
    '.Width = wd.InchesToPoints(2.5)
    ' .Width = wd.CentimetersToPoints(2.66) * 2.5
    ' .Height = wd.CentimetersToPoints(3.27) * 2.5
    .ScaleHeight = 50
    .ScaleWidth = 50
End With

doc.Close

End Sub

For those who are curious GetUserName is a function that pulls the name of whoever is logged into the computer: it's code is simple and looks like this:

Public Function GetUserName() As String

     GetUserName = Environ("USERNAME")

End Function

By moving the location of the operation, all errors disappeared. Not sure why it matters that the operation is on the local machine, when other operations such as the copy function work perfectly fine over the network.