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

 

[Jon Peltier]

I've always had better luck using a ChartObject (chart-containing shape embedded in worksheets) than a Chart sheet (a chart on a sheet without the underlying grid), and resizing the ChartObject before copying. it.

 

[Macropod]

Try:

Dim IDACIFile As Excel.Workbook
Dim wrdRng As Word.Range
Dim wrdIShp As Word.InlineShape
Const wrdBkMk As String = "IDACI1"

'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
With wrdApp
  If .Bookmarks.Exists(wrdBkMk) Then
    Set wrdRng = .Bookmarks(wrdBkMk).Range
    'Delete any inlineshapes that might already be there
    With wrdRng
      While .InlineShapes.Count > 0
        .InlineShapes(1).Delete
      Wend
      'Insert and size our inlineshape
      Set wrddIShp = .PasteSpecial(DataType:=wdPasteBitmap, Placement:=wdInLine) 'wdPasteEnhancedMetafile might be better
      With wrdIShp
        .Height = 295
        .Width = 480
      End With
    End With
    'Format the paragraph
    .ParagraphFormat.Alignment = wdAlignParagraphCenter
    'Reapply the bookmark
    .Bookmarks.Add Name:=wrdBkMk, Range:=wrdRng
  End If
End With

I'm assuming from the code you posted that wrdApp is a document reference that you've declared and referenced somewhere else, as it isn't in the code you posted. That said, wrdApp would normally be an application reference, not a document reference.

Note: As this isn't really an Excel question, you'd have done better posting in 'General Excel Discussion & Other Questions'.

 

[DIMWIT]

resize chart once pasted into word

Hi all,

After several days on this and a lot of googling I cannot still find a remedy for this.
Basically I have a word document that is opened up and an excel file that is opened. One of the charts within this excel file is copied and pasted into the word document via a bookmark.

The code I have copies and pastes the chart in fine but I cannot get it to re size to 65%.

My code for this section is below. I would be so grateful for any help

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
If ActiveDocument.InlineShapes.Count = 1 Then
    ActiveDocument.InlineShapes(1).ConvertToShape
With ActiveDocument.Shapes(1)
  .ScaleHeight 0.65, msoFalse, msoScaleFromTopLeft

End With
 End If
End Sub

What am I doing wrong?

 

[Domenic]

Re: resize chart once pasted into word

Does it help if you replace...

If ActiveDocument.InlineShapes.Count = 1 Then
    ActiveDocument.InlineShapes(1).ConvertToShape
    With ActiveDocument.Shapes(1)
       .ScaleHeight 0.65, msoFalse, msoScaleFromTopLeft
    End With
 End If

with

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

Note that here the ScaleHeight property of the InlineShape object is being used, instead of the ScaleHeight method of the Shape object.

 

[DIMWIT]

Re: resize chart once pasted into word

Thank you Domenic ,

I have tried your suggestion however the chart doesn't resize at all

 

[Domenic]

Re: resize chart once pasted into word

Did you use 65 instead of 0.65 to scale your height?

 

[DIMWIT]

Re: resize chart once pasted into word

I did- yes

 

[Domenic]

Re: resize chart once pasted into word

Actually, I noticed that you haven't assigned wrdApp a Word application object. Did you initially post the complete code? In any case, can you post the complete code that you've now tried, and confirm from within which application the code is being run?

 

[Macropod]

Re: resize chart once pasted into word

Unfortunately, you can't retrieve the .ScaleHeight & .ScaleWidth dimensions of a Chart Object to adjust them on a proportionate basis - you need to set the height & width explicitly, as per your other thread.

PS: It seems I wasted my time telling you how to address the issues you were in post #3, as you're still using your problem code... and, as Domenic notes, there is still the issue of your apparently undeclared wrdApp object.