Macro to Insert Picture and Filename as a Caption

[nowangmanage]

Dear all,

I am a VBA novice trying to write code to insert a picture and the filename as a caption. By surfing the net to find example macros and piecing together the relevant bits, here's what I've come up with so far:


Sub InsertPicture()
Dim Doc As Dialog
Dim BClicked As String
Dim picPath As String
Dim picName As String
Dim oRg As Range
Set Doc = Dialogs(wdDialogFileOpen)
With Doc
.Name = "*.jpg"
BClicked = .Display
picName = .Name 'picName holds the picture name
End With
picPath = CurDir 'picPath holds the picture path
Selection.InlineShapes.AddPicture FileName:=picPath & "\" & picName
Selection.InsertCaption Label:="Figure", TitleAutoText:="", Title:=": " & picName, _
Position:=wdCaptionPositionBelow
End Sub


I have two problems:

1. If I try to insert a picture with a filename that contains spaces then I get a run-time error '5152'.

2. The code 'Position:=wdCaptionPositionBelow' positions the caption at the bottom right of the picture but I would prefer it to be underneath it.


Does anyone have any ideas or suggestions? Any help would be much appreciated.

Thanks,

Andrew

[lucas]

Hi Andrew,
I have taken the liberty of enclosing your code in vba tags.....just select your code when posting and hit the "Wrap VBA tags button"

I am no word expert by any means but I think you will have to rename your pictures so that they don't have any spaces....maybe someone will come along and correct me...I have the same problem with your code.

As far as getting the caption below the picture, try adding a carriage return between your insert picture and insert caption code..

Option Explicit
Sub InsertPicture()
Dim Doc As Dialog
Dim BClicked As String
Dim picPath As String
Dim picName As String
Dim oRg As Range
Set Doc = Dialogs(wdDialogFileOpen)
With Doc
.Name = "*.jpg"
BClicked = .Display
picName = .Name 'picName holds the picture name
End With
picPath = CurDir 'picPath holds the picture path
Selection.InlineShapes.AddPicture FileName:=picPath & "\" & picName
Selection.TypeParagraph
Selection.InsertCaption Label:="Figure", TitleAutoText:="", Title:=": " & picName, _
Position:=wdCaptionPositionBelow
End Sub

[nowangmanage]

Steve,

Thanks for the help - the Selection.TypeParagraph line worked a treat.

Unfortunately, I have over two thousand pictures to insert into ten different MS Word documents so retagging the entire collection is not really feasible.

I need to somehow find a method of implementing this macro for filenames that contain one or more spaces.

If you have any other ideas, please let me know.

Thanks again.

Andrew

[Killian]

Hi Andrew, and welcome to VBAX :hi:

Regarding the error:
this is caused by illegal characters in the file name - but spaces are perfectly valid.
To find out whats going on, you look at the value of your string variable in each case.

In the VBE, select View>Locals - this will show the Locals window that displays a list of all the local variables while the routine is running.
Now put your cursor in the End With line at press F9 - this will set a breakpoint... the code will pause at that point and you can look at the variable values.
When you want to continue the code excecution, press F5 (or F8 to step line, by line)

With a filename with no spaces, you get "filename.jpg"

With a filename with spaces, you get ""filename.jpg""

Notice the extra quotes, which are illegal characters in filenames.
this is caused, quite frankly, by buggy Office dialogs that insert quotes around strings with spaces in when using Display mode. (Display doesn't excecute the dialog action, like Show does)
I wondered why you didn't use the InsertPicture dialog - it somehow seems appropriate - either way, you need to do a bit of string manipulation to sort things out.

With the FileOpen dialog using Display, after the End With line, add'replace the extra quotes with spaces
Do Until InStrRev(picName, """") = 0
Mid(picName, InStrRev(picName, """"), 1) = " "
Loop
'trim off the spaces
picName = Trim(picName)Here's an alternative using wdDialogInsertPicture - it returns the whole path so you need to get the filename for the caption from thatSub InsertPicture2()

Dim Doc As Dialog
Dim BClicked As String
Dim picPath As String
Dim picName As String

Set Doc = Dialogs(wdDialogInsertPicture)
With Doc
.Name = "*.jpg"
BClicked = .Show 'using show executes the dialog
picPath = .Name
End With
'strip off the path to get the file name
picName = Right(picPath, Len(picPath) - InStrRev(picPath, "\"))

'only do this if OK was clicked
If BClicked = -1 Then
Selection.InsertCaption Label:="Figure", _
TitleAutoText:="", Title:=": " & picName, _
Position:=wdCaptionPositionBelow
End If
End SubAlso, in this example, I've checked the return value from the dialog to make sure it wasn't cancelled to avoid errors

[lucas]

Two great working solutions, thanks K. I also really liked your Excel search toolbar. (http://vbaexpress.com/forum/showthread.php?t=6038) You should put them in the KB

[nowangmanage]

Steve & Killian,

Thanks very much for the help. Killian - your solution worked a treat.

Is there any way to control the size of the picture I am inserting through VBA? I have had a looked on the net and have also tried using the record macro function but to no avail. Considering there are over 1000 files to insert, it would be very time consuming to have to manually format the size of each picture.

Any help would be much appreciated.

Thanks,

Andrew

[Killian]

You can size the picture using it's Height and Width properties, or ScaleHeight and ScaleWidth. It would be useful to set a object variable for the picture, which isn't quite as straight-forward when you use a dialog compared with when you use the Shapes or InlineShapes Add method.

So, considering there are over 1000 files to insert, won't it be very time consuming to have to select each file with the dialog?
You might want to consider batch processing all the files in a folder. Here's an example that also resizes pictures that are over a defined widthSub BulkInsertPictures()

'!!! ADD A REFERENCE TO THE
'!!! Mircosoft Scripting Runtime
'!!! from Tools>References

Dim fso As FileSystemObject
Dim fldr As Folder
Dim f As File
Dim myNewPic As InlineShape
Dim scale_factor As Long

'set constants for max width of pictures
'and the path of the target folder
Const max_width As Long = 100
Const pic_path As String = "C:\TEMP"

Set fso = New FileSystemObject
'set up the target folder
Set fldr = fso.GetFolder(pic_path)

'loop through all the files in the target folder
For Each f In fldr.Files
'check file extension
If LCase(Right(f.Name, 3)) = "jpg" Then
'add picture
Set myNewPic = Selection.InlineShapes.AddPicture( _
f.Path, False, True)
'check size and scale if too big
If myNewPic.Width > max_width Then
scale_factor = (max_width / myNewPic.Width) * 100
myNewPic.ScaleWidth = scale_factor
myNewPic.ScaleHeight = scale_factor
End If
Selection.TypeParagraph
Selection.InsertCaption Label:="Figure", TitleAutoText:="", _
Title:=": " & f.Name, Position:=wdCaptionPositionBelow
Selection.TypeParagraph
End If
Next

End Sub

[fumei]

This has come up in another thread. Word is NOT a graphics application.

The only alternative is to use ActiveX controls, and adjust those for the dimensions you want. Interestingly the better control to use is a label control, NOT an image control.

Image controls resize the control to the image. Label controls resize the image to the control. So make the control the size you want, then use LoadPicture to insert the image.

Unfortunately, the ActiveX controls do not have an attached "caption". They DO have a Caption property, but like other controls this is the text ON the control...not beside it.

[fumei]

In fact, using FileSystemObject to get the file names, it is easy to run through folders inserting images into ActiveX controls.

I do this for my photographs (15,000+). However, as I do not use InlineShapes (I use the ActiveX Label control), I bring in each file (using FSO) into the control with LoadPicture, then using the same FSO file grab the filename and use it for the following text caption.

The advantage of ActiveX, rather than InlineShapes is that they clickable (with events), and much more, IMHO, easier to resize.

[mdmackillop]

While this may not be of use for an existing library of photos, I've used the following approach,
When downloading off a card, I have a userform routine which sets appropriate file names and causes the file path/name and a caption (default is file name) to be saved in a text file. This text file can be opened by code and listed photos inserted into Word, followed by the caption.
The Word document can be recreated at any time, so I don't have to store huge documents (I know about links, but they are not always suitable)
Regards
MD