'Replacing text with image in Word VBA template
I want to insert different images (.png) into their predefined spot in an MSWord report template. The report should be able to be used with different image file paths, so that the user is able to choose the folder in which the images are located.
The names of the images are the same for each use case since they come from a processing program in Matlab. Therefore, I use XXX.png, YYY.png etc. as placeholders in the Word document.
I was able to combine several parts of code I found here:
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
On Error Resume Next
BrowseForFolder = ShellApp.self.path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
BrowseForFolder = False
End Function
Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, j As Long, StrNm As String, StrErr As String, iShp As InlineShape, InputFolder As String, folderpath As String, location As String, filepath As String
location = BrowseForFolder("") & "\"
location = Replace(location, "\", "\\")
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*.png"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute
End With
Do While .Find.Found
StrNm = location & .Text
If Dir(StrNm) = "" Then
j = j + 1: StrErr = StrErr & vbCr & StrNm
Else
i = i + 1: .Text = vbNullString
Set iShp = .InlineShapes.AddPicture(FileName:=StrNm, LinkToFile:=False, SaveWithDocument:=True, Range:=.Duplicate)
End If
.Collapse wdCollapseEnd
.Find.Execute
Loop
End With
Application.ScreenUpdating = True
MsgBox i & " images added." & vbCr & j & " image files not found:" & StrErr
End Sub
The first part; choosing the designated folder where the images are located, works, but the second part is not working properly.
Since this report should be flexible, I don't want to just insert the whole folder path into .Text = "*.png" (e.g. .Text = "C:\\VBA Template\\*.png"), but rather want the user to choose the folder path beforehand. .Text = location & "*.png" to concatenate the strings doesn't work and inserting the whole path (C:\VBA Template\[imagename].png instead of just [imagename].png) as a placeholder in the template is not feasible.
So basically I want a report template where the macro asks the user for the folderpath to the pictures and then inserts the pictures at their designated spot in the document. And this to be flexible so multiple users can use it on different computers and drives.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
