pasting a chart into word - runtime error 5941 member of collection does not exist

[DIMWIT]

Hey everyone,

I have recently taken over a piece of work which using VBA to open a chart in excel and pastes it into a word document and re sizes it. This code used to work but is now coming up with the run time error code 5941.

The bit where it mentions 'inline shapes' is where it falls over

The person who wrote the code is no longer here and no one else in the office - including myself - knows how to fix it. here is the code:

Public Sub AddIdaciCharts()
Dim IDACIFile As Excel.Workbook

'opens the file based on the school number workbook range
Set IDACIFile = Workbooks.Open(ThisWorkbook.Path & "\IDACI Files\335" & Range("SchoolDfE") & " idaci decile bands 2016.xlsx")

'copies the chart from the School vs LA tab and pastes into the bookmark in the word doc
IDACIFile.Sheets("School vs LA").ChartArea.Copy

wrdApp.Selection.Goto what:=wdGoToBookmark, Name:="IDACI1"
wrdApp.Selection.PasteSpecial DataType:=wdPasteBitmap, Placement:=wdInLine
wrdApp.Selection.MoveLeft unit:=wdCharacter, Count:=1, Extend:=wdExtend
With wrdApp.Selection.InlineShapes(1)
    .Height = 295
    .Width = 480
    .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
End With

We are truly stuck and need some assistance. I have checked and the bookmarks exist and the excel and charts are fine and in the right location.

Does anyone know what this might be?

Many thanks as always

 

[Macropod]

Re: resize chart once pasted into word

That line refers to the first inline shape within the document, not necessarily to the one just pasted.

No, it does not! It refers to the first shape in the designated range. Please read the code. In any event, even if it did refer to the first inlineshape in the document, no error would be produced unless the object referred to was not scalable (see my previous remarks about this).

DIMWIT: The code works fine for me (I tested before posting). Try replacing:

    If .Bookmarks.Exists(wrdBkMk) Then
      Set wrdRng = .Bookmarks(wrdBkMk).Range
      'Delete any inlineshapes in our bookmark's range
      With wrdRng
        While .InlineShapes.Count > 0
          .InlineShapes(1).Delete
        Wend
        
        'Insert and size our inlineshape
        .PasteSpecial Link:=False, DataType:=wdPasteBitmap, _
          Placement:=wdInLine, DisplayAsIcon:=False
        .Start = .Start - 1
        .InlineShapes(1).ScaleHeight = 65
      End With
      
      'Reapply the bookmark
      .Bookmarks.Add Name:=wrdBkMk, Range:=wrdRng
      
      'Format the paragraph
      .Paragraphs(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    End If

with:

    If .Bookmarks.Exists(wrdBkMk) Then
      Set wrdRng = .Bookmarks(wrdBkMk).Range
      'Delete any inlineshapes in our bookmark's range
      With wrdRng
        While .InlineShapes.Count > 0
          .InlineShapes(1).Delete
        Wend
        
        'Insert and size our inlineshape
        .PasteSpecial Link:=False, DataType:=wdPasteBitmap, _
          Placement:=wdInLine, DisplayAsIcon:=False
        .Paragraphs(1).Range.InlineShapes(1).ScaleHeight = 65
        
        'Format the paragraph
        .Paragraphs(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        
        'Reapply the bookmark
        .Bookmarks.Add Name:=wrdBkMk, Range:=.Paragraphs(1).Range.InlineShapes(1).Range
      End With
    End If

 

[Domenic]

Re: resize chart once pasted into word

No, it does not! It refers to the first shape in the designated range.

Actually, I thought the OP was referring to my code...

With wrdApp.ActiveDocument
    .InlineShapes(.InlineShapes.Count).ScaleHeight = 65
End With

But I see now that he was actually referring to your code. Sorry for any confusion.

 

[Macropod]

Re: resize chart once pasted into word

Conversely, the problem with your code is that it assumes the InlineShape to be resized is the last in the document. That may or may not be the inserted InlineShape, since we have no idea what else the document contains. The index # of an InlineShape isn't related to the order of insertion but to its relative location in the document.

 

[DIMWIT]

Re: resize chart once pasted into word

Thanks very much for all your help guys.

Macropod - you are a genius!. Thank you so much, the code is working fine and adjusting the size of the charts in the document.

You have saved me again

Really appreciate the help

 

[Domenic]

Re: resize chart once pasted into word

Conversely, the problem with your code is that it assumes the InlineShape to be resized is the last in the document. That may or may not be the inserted InlineShape, since we have no idea what else the document contains. The index # of an InlineShape isn't related to the order of insertion but to its relative location in the document.

Oh wow... I didn't realize that an InlineShape is indexed according to its relative location in the document. Now that I think of it, I guess it stands to reason since the shape is "in line" with text.

Thanks for pointing that out!

Cheers!