To MSWord Gurus: calling the last inserted image

[kkjensen]

Hi everyone,

I've used a macro for a while now that dumps a snapshot of an excel range into word. An extra few lines adds the original location (spreadsheet file, sheet and range) to the dumped image (In the web alternative text field) so the info can be traced back.

Works great once but since I don't know how the numbering on objects works my code only adds this extra bit to the LAST (numerically) image. For example...fill a blank word document with pictures and everything is great. If I go back, delete number 2 (for example) and reinsert a new one in it's place this extra info does not get added to the newest picture.

Here's the code:

' Copy the range in Excel
        Selection.CopyPicture Appearance:=xlScreen, _
            Format:=xlPicture
'Past in Word       
        WDApp.Selection.Paste

'******ADD SOURCE INFORMATION ******************************
        With WDDoc.InlineShapes(WDDoc.InlineShapes.Count)
            .AlternativeText = Application.Substitute( _
                Selection.Address(External:=True), _
                "!" & Selection.Address, "")
        End With
'**********************************************************

I realize this is code running from Excel but the code is all specific to Word.

Any help on adding the soruce info to the last image pasted would be greatly appreciated.

Kindest regards and a million thanks in advance,

kkjensen

P.S. This is only a snippet of the real code. I can include more if needed. For this to work the Word library needs to be added in the excel VBA editor.

 

[Macropod]

Hi kkjensen,

To find out which is the last-inserted image, you'd need to loop through the shapes collection and find which one has the highest shape ID. For example:

Sub GetLatestShape()
Dim Shp As Shape, ShpID As String
With ActiveDocument
  If .Shapes.Count > 0 Then
    For Each Shp In .Shapes
      If InStr(Shp.Name, "Picture") > 0 Then _
      If Shp.Name > ShpID Then ShpID = Shp.Name
    Next Shp
    .Shapes(ShpID).Select
  End If
End With
End Sub

Note that I've assumed your images are being inserted as pictures, rather than as Excel objects. On the latter point, have you considered inserting your snapshots as linked objects? If you did that, Word could update its representation of whatever range you're linking to whenever the contents of that range changes - without the need for a macro. If you name the range, then, with a little bit of work, the linked object could accommodate changes in the named range's definition. Plus the links necessarily store the path to the source data within them.

 

[Macropod]

Hi kkjensen,

Note also that my code assumes you're working with shapes (which is how pictures are normally inserted), but i see that your code assumes inlineshapes. If you're working with the latter, simply change 'Shape' and 'Shapes' to 'InlineShape' and 'InlineShapes', respectively.

 

[kkjensen]

Looks like that might be it! I knew that the objects incremented and that my code only modified the one that matched the total count but this idea quickly falls apart.

I'll give it a shot! Thanks.

 

[Macropod]

Hi kkjensen,

On reflection, I don't believe the approach I outlined in my last post will work with InlineShapes. For starters, they don't have a 'name' property.

However, since you're using a macro to insert the images, you can use that to assign an 'AlternativeText' property to the InlineShapes at the insertion time, based on the date & time (eg as a serial number). Then it'd be a simple matter to test the 'AlternativeText' values and select the highest one, using code like:

Sub GetLatestInlineShape()
Dim iShp As InlineShape, iShpID As String
With ActiveDocument
  If .InlineShapes.Count > 0 Then
    For Each iShp In .InlineShapes
      If iShp.AlternativeText > iShpID Then
        iShp.Select
        iShpID = iShp.AlternativeText
      End If
    Next iShp
  End If
End With
End Sub

Since Shape objects also have an 'AlternativeText' property, a similar approach could be used with them also.

 

[kkjensen]

I am using the code to insert other things into the 'alternative text' field. I suppose I could put both.

If "InlineShapes.Count" can be used to modify the alternative text for the last one, doesn't this imply that the objects are still numbered?

 

[kkjensen]

I wasn't able to figure out the auto-incrementing of the inlineshape object numbers but I finally settled for the following...probably not the most graceful or efficient because it's going to do a LOT of looping as the number of inserted objects in the document increases. The most important thing for me is to have a dependable bit of info dropped into the 'alternative text' field showing me where it came from. Many excel sheets will be feeding this document and I'm writing an add-in for excel that will simply assist in dumping info from excel to the word report.

At the beginning of the macro:

        'Fill in blank references (from manually inserted images)
        'to prevent reference text from spreading where it isn't
        'supposed to go
        With WDApp.ActiveDocument
            If .InlineShapes.Count > 0 Then
                For Each iShp In .InlineShapes
                    If iShp.AlternativeText = blank Then
                        iShp.AlternativeText = "manually inserted picture"
                    End If
                Next iShp
            End If
        End With

The picture is then taken and pasted as in the first post...I've removed a lot of code for clarity but it was all error-checks and stuff.

At this point all pictures, except the newly inserted one, should have some text attached to them...either a reference (from previous runs of the macro) or a filler "manually inserted picture".

This then adds the reference to the only one with a blank .alternativetext field

        With WDApp.ActiveDocument
            For Each iShp In .InlineShapes
                    If iShp.AlternativeText = blank Then
                        iShp.AlternativeText = Selection.Address(External:=True) & "-" & Rangename
                    End If
            Next iShp
        End With

Thanks for the help Macropod!

 

[Macropod]

If "InlineShapes.Count" can be used to modify the alternative text for the last one, doesn't this imply that the objects are still numbered?

Hi kkjensen,

No, it's rather like having a bag of marbles and counting them. There's no reason to suppose that, just because you can count the marbles, each has a number painted on it. And, unless the marbles can be individually identified (eg by marking them in some way), there's no way to tell which one was the last one added to the bag.

FWIW, you could get an insertion order for each picture if, immediately after inserting & tagging them you converted them from an InlineShape to a Shape:
WDApp.Selection.InlineShapes(1).ConvertToShape
The .Name property that gets added by the conversion gives you the insertion order. The conversion to a Shape object also gives you more flexibility as to where the picture ends up being positioned on the page.

 

[kkjensen]

Interesting points about the inlineshape to shape conversion.

The way I've got it setup seems to get the job done but all the looping now is horribly inefficient. I need to learn how to read info to a table in memory (i.e. bookmarkname and alternate text for each of the images in Word) and then use that in my excel macro. Right now there is looping to check for a bookmark's existence, to add filler alternate text if there is none and then to finally find the new inlineshape and add the info in the alternate text field.

1 picture means 3 loops checking 1 picture...3 operations...no big deal. My report has several hundred pictures right now and it just got really slow...at least the references work!

 

[kkjensen]

The macro has been working as expected but the looping is getting out of hand. I'm up to over a hundred pages...mostly full of pictures inserted and tagged by my macro. I had a bit of a brain wave though:

I was thinking, instead of starting at the first image and looping through all of them, what if I went to the bookmark (used to place and replace the image when the macro is run) then hopped back a couple pages (just to be sure) and then use the 'next image' to cycle through just a couple images until I get to the one with no alternative text.

Does this make any sense? I recorded myself doing it but it just takes me there...doesn't select the image:

Application.Browser.Target = wdBrowseGraphic
Application.Browser.Next