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,

If you're inserting the inlineshape into a bookmark, you should be able to simply update that bookmark. Provided you're not replacing the bookmark with the inlineshape, there's then no need to attach an alternative text property to the inlineshape. You can ensure the newly-inserted inlineshape is bookmarked with code along the lines of:

Sub Test()
Dim BmkNm As String, NewRng As Range
MyBkMk = "Test"
If Selection.InlineShapes.Count > 0 Then
  Set NewRng = Selection.InlineShapes(1).Range
  ActiveDocument.Bookmarks.Add BmkNm, NewRng
End If
End Sub

 

[kkjensen]

Maybe I've set this up wrong from the start...

To facilitate entering large amounts of excel data into a word report (and engineering template where more text is needed than can be comfortably done in Excel alone).

Manually copying and updating things (especially from multiple spreadsheets) is very tedious and time consuming...bring on the scripting please!

At first it was a simple script that copied a picture from the selected range in excel to the current cursor position in Word.

Then the looping began: Looping through named ranges and dumping at identically named bookmarks in Word. I tried going to the bookmark and inserting directly but things seemed to fall apart when the image needed updating (goto bookmark, delete, reinsert) so I added a couple line breaks after the bookmark to ensure there was always a space for the image to drop in without starting to interact with any text or titles that might be in the area.

The alternate text isn't to identify or find the object in Word, it's to know which excel sheet and range it came from. When these reports are checked by a 3rd party we get hand-written comments on them. Finding the right file and range to correct each of the comments was a long task. With all of these scripted and alternative text marking the image, looking up the location of the item to be updated is easy, correcting happens and re-updating the Word report is all scripted.

Everything works as expected right now, it's just getting a bit slow as it's having to loop through all the inlineshapes in the document to find the right one and I'm trying to figure out how to reduce the amount of looping. My idea is trying to avoid lookping through the entire document from start to finish by going to the right area, backing up a bit to be sure and looping through some arbitrary number of inlineshapes to get the job done. Looping through 10 a couple hundred times is sure better than looping through a couple hundred, a couple hundred times.

 

[Macropod]

Hi kkjensen,

If the only reason for the updates is that the source data are changing value (but the source workbooks and ranges remain essentially the same), the best appraoch might be to copy the source ranges and insert them into Word using Edit|Paste Special and choosing the 'paste link' option.

That way, Word can update the data to reflect any changes in the source as they occur, without the need to replace anything. Plus, if you use named data ranges in Excel, you can edit the link fields in Word so that they use the named ranges. That way, if a named range in Excel changes, the Word document will update to reflect the new range.

No vba would be needed for any of this.

Even if each report uses a differrent set of workbooks, provided the reports are based on the same range sets, a simple Find/Replace operation of the link fields (perhaps vba-assisted) can deal with the change.

Perhaps you could clarify your needs a bit more?

 

[kkjensen]

I tried using linked objects but once the report hits a certain size things go south. I've always assumed this was a limitation of Word (kind of like Excel working great up to the 32MB file size where it blows up) so I've been working around it to make documents that can be updated quickly but not depend on the links always being intact.

 

[kkjensen]

Here's the solution you (I think) posted on tech-tips

Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Dim iShp As Word.InlineShape
Dim oRng As Word.Range

Set WDApp = GetObject(, "Word.Application")
Set WDDoc = WDApp.ActiveDocument

With WDDoc
    Set oRng = WDApp.Selection.Range
    oRng.End = .Range.End
    For i = 1 To 5
        If oRng.Previous Is Nothing Then Exit For
        oRng.MoveStart wdParagraph, -1
    Next
    For Each iShp In oRng.InlineShapes
        Application.StatusBar = "Alternative Text for " & Rangename
        If iShp.AlternativeText = "" Then
            iShp.AlternativeText = Selection.Address(External:=True) & "-AKA-" & Rangename
            If .InlineShapes(i).Height > 530 Then
                 .InlineShapes(i).Width = 530 * .InlineShapes(i).Width / .InlineShapes(i).Height
                 .InlineShapes(i).Height = 530
            End If
        End If
    Next
End With

Thanks for the help