Using portions of a filename for a caption.

mathemagician44 · #1 02-03-2022, 09:18 PM

I found a great code posted by Macropod a couple years ago (https://www.msofficeforums.com/word-...-pictures.html). I do thank you again for posting that code. I am essentially trying to take the following lines to grab only a portion of the file name:

Code:

'Get the Image name for the Caption
        StrTxt = Split(.SelectedItems(j), "\")(UBound(Split(.SelectedItems(j), "\")))
        StrTxt = " - " & Split(StrTxt, ".")(0)
'Insert the Caption on the row below the picture
        With oTbl.Cell(r + 1, c).Range
          .InsertBefore vbCr
          .Characters.First.InsertCaption _
          Label:="Photograph", Title:=StrTxt, _
          Position:=wdCaptionPositionBelow, ExcludeLabel:=False
          .Characters.First = vbNullString
          .Characters.Last.Previous = vbNullString

This is grabbing the entire filename until the first period in the file name. Since my filenames may be multiple sentences and the statement needs to end in a period, how can I change that above code to include everything but the file extension ".jpg"

After I get that figured out, I then need to make the caption not include the first entry of the file name. I.e., I number each photo so they are inserted in the correct order, but I don't want that number included in the caption.

My filenames for each jpg might be something like:
1 Dog is brown. Dog is big.
1.2 Cat is black. Cat is little.
2 Pencil is a yellow. Pencils are great.

I just want the macro to grab everything after the initial number in the file name, minus the ending file extension. Can this be done? I thank you for reading. Have a good day!

gmayor · #2 02-03-2022, 10:05 PM

The following should work

Code:

Sub Test()
    MsgBox GetCaption(ActiveDocument.Name)
End Sub

Function GetCaption(sName As String)
Dim sCaption As String
Dim iPos As Integer
    If IsNumeric(Left(sName, 1)) = True Then
        iPos = InStr(1, sName, " ")
        sCaption = Mid(sName, iPos + 1)
    Else
        sCaption = sName
    End If
    sCaption = Left(sCaption, InStrRev(sCaption, "."))
    GetCaption = sCaption
End Function

mathemagician44 · #3 03-07-2022, 06:54 PM

I have tried this and 58 different variations trying to figure this out. Thus far, I have just been going back and deleting the beginning number of the caption after the macro has ran. But this is tedious for numerous photos multiple times per week.

Trying to incorporate what you said to my program, I now have below, but the "isnumeric" is not working

Code:

'Get the Image name for the Caption
        StrTxt = Split(.SelectedItems(j), "")(UBound(Split(.SelectedItems(j), "")))
        StrTxt = "." & Split(StrTxt, ".JPG")(0)
        Dim iPos As Integer
        Dim sCaption As String
        If IsNumeric(Left(StrTxt, 1)) = True Then
        iPos = InStr(1, StrTxt, " ")
        sCaption = Mid(StrTxt, iPos + 1)
        Else
        sCaption = StrTxt
        End If
        'Insert the Caption on the row below the picture
        With oTbl.Cell(r + 1, c).Range
        .InsertBefore vbCr
        .Characters.First.InsertCaption _
        Label:="Photograph", Title:=StrTxt, _
        Position:=wdCaptionPositionBelow, ExcludeLabel:=False
        .Characters.First = vbNullString
        .Characters.Last.Previous = vbNullString
        End With

macropod · #4 03-07-2022, 07:18 PM

After:

Code:

StrTxt = Split(.SelectedItems(j), "\")(UBound(Split(.SelectedItems(j), "\")))

insert:

Code:

        StrTxt = Left(StrTxt, InStrRev(StrTxt, ".") - 1)
        StrTxt = " - " & Right(StrTxt, Len(StrTxt) - Len(Split(StrTxt, " ")(0)) - 1

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

mathemagician44 · #5 03-07-2022, 07:31 PM

Freaking excellent!! Thank you so much!

While I have your attention, can you tell me how to apply a certain font name, type, color to

Code:

'Get the Image name for the Caption
        StrTxt = Split(.SelectedItems(j), "\")(UBound(Split(.SelectedItems(j), "\")))
        
        StrTxt = Left(StrTxt, InStrRev(StrTxt, ".") - 1)
        StrTxt = Right(StrTxt, Len(StrTxt) - Len(Split(StrTxt, " ")(0)) - 1)
        
        'Insert the Caption on the row below the picture
        With oTbl.Cell(r + 1, c).Range
        .InsertBefore vbCr
        .Characters.First.InsertCaption _
        Label:="Photograph", Title:=StrTxt, _
        Position:=wdCaptionPositionBelow, ExcludeLabel:=False
        .Characters.First = vbNullString
        .Characters.Last.Previous = vbNullString
        End With

macropod · #6 03-07-2022, 07:36 PM

See, for example, posts #4 & #5 in: https://www.msofficeforums.com/word-...cture-1-a.html

gmayor · #7 03-07-2022, 09:52 PM

Quote:

Originally Posted by mathemagician44

I have tried this and 58 different variations trying to figure this out.

There was nothing to figure out. The function I posted provided the caption gets the text in the filename (sname) without the extension or the number at the beginning, from the examples that you provided.

You can call the function where you want to use the caption in your larger macro e.g.

Code:

Sub TestCaption()
Const fName1 As String = "1 Dog is brown. Dog is big.jpg"
Const fName2 As String = "1.2 Cat is black. Cat is little.jpg"
Const fName3 As String = "2 Pencil is a yellow. Pencils are great.jpg"
Dim sCaption As String
    sCaption = GetCaption(fName1)
    MsgBox sCaption
    sCaption = GetCaption(fName2)
    MsgBox sCaption
    sCaption = GetCaption(fName3)
    MsgBox sCaption
End Sub

Function GetCaption(sName As String)
Dim sCaption As String
Dim iPos As Integer
    If IsNumeric(Left(sName, 1)) = True Then
        iPos = InStr(1, sName, " ")
        sCaption = Mid(sName, iPos + 1)
    Else
        sCaption = sName
    End If
    sCaption = Left(sCaption, InStrRev(sCaption, "."))
    GetCaption = sCaption
End Function

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to uninstall portions of Office Pro 2016	Moondoggy	Office	1	07-10-2020 04:10 AM
filename field not displaying correct filename when that name starts with #	plrsmith	Word	1	07-06-2018 03:10 AM
Filename	UnlimitedPower	Word VBA	1	08-19-2016 12:22 AM
Showing or hiding portions of contract based on selected services.	BlackrazorNZ	Word	1	05-30-2014 05:43 AM
Error! Filename not Specified	mark.lacey	Outlook	0	10-12-2011 03:02 AM