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

 

[DIMWIT]

Re: resize chart once pasted into word

I have tinkered with the code somewhat to try and get it working but as I don't have any experience of VBA (very limited at best) I don't understand what I need to change and am probably making it a whole lot worse to be honest.

Domenic thank you for your efforts with this. I do declare the wrd app right at the beginning of the code.
Here is the code and it runs from excel

Option Explicit
Dim wrdApp As Word.Application, wrdTemplate As Word.Document, oStory As Word.Range
Dim ReportName As String

Public Sub RunDSReport()
Application.ScreenUpdating = False
Application.DisplayAlerts = False


'Fire up Word, open Word template and make visible
Set wrdApp = New Word.Application
Set wrdTemplate = wrdApp.Documents.Open(ThisWorkbook.Path & "\Primary Template 2015-16.doc")
wrdApp.Visible = True

AddIdaciCharts  'Copies IDACI charts into bookmarks in the Word template


ReportName = "335" & Range("SchoolDfE") & "_" & Range("SchoolName") & " " & Range("CurrentYear") & " Unvalidated"

wrdApp.Documents(wrdTemplate).Activate

'For Each oStory In wrdApp.ActiveDocument.StoryRanges    'Update all the linked charts and tables in the Word document
  '  oStory.Fields.Update
   ' If oStory.StoryType <> wdMainTextStory Then
      '  While Not (oStory.NextStoryRange Is Nothing)
          '  Set oStory = oStory.NextStoryRange
           ' oStory.Fields.Update
        'Wend
   ' End If
'Next oStory
'Set oStory = Nothing
    
'wrdApp.ActiveDocument.TablesOfContents(1).Update    'Update the contents table so the page numbers are accurate

'Save the new school report in Word format and then create a pdf version
wrdApp.ActiveDocument.SaveAs (ThisWorkbook.Path & "\Unvalidated Oct 2016\" & ReportName & ".doc")
wrdTemplate.ExportAsFixedFormat OutputFileName:=ThisWorkbook.Path & "\Unvalidated Oct 2016\" & ReportName & ".pdf", _
    ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
    Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:=wdExportCreateNoBookmarks, _
    DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False

wrdApp.Documents(wrdTemplate).Close
wrdApp.Quit
Set wrdApp = Nothing
Set wrdTemplate = Nothing
ReportName = ""

ThisWorkbook.Activate

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


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

With wrdApp.ActiveDocument
    .InlineShapes(.InlineShapes.Count).ScaleHeight = 65
End With
'Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

 
IDACIFile.Close False
Set IDACIFile = Nothing
 
End Sub

 

[DIMWIT]

Only just noticed you had replied. Thanks Marcopod. I will give it a try

 

[Macropod]

Re: resize chart once pasted into word

I have tinkered with the code somewhat to try and get it working but as I don't have any experience of VBA (very limited at best) I don't understand what I need to change and am probably making it a whole lot worse to be honest.

Let us know how you get on with the code in post #3.

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.

 

[DIMWIT]

Re: resize chart once pasted into word

Cheers Macropod.

Will do. I gave your code a try and an getting an error:

Method or data member not found?

 

[Macropod]

Re: resize chart once pasted into word

on what line? Simply posting that you got an error without saying where isn't especially helpful.

 

[DIMWIT]

Re: resize chart once pasted into word

this is the line where it gets stuck

If .Bookmarks.Exists(wrdBkMk) Then

 

[Macropod]

Re: resize chart once pasted into word

That is most likely because, as I indicated in post #3, wrdApp is the Word application, not the document. Bookmarks exist at the document level, not at the application level.

Try:

Public Sub RunDSReport()
Application.ScreenUpdating = False: Application.DisplayAlerts = False
Dim xlWkBk As Excel.Workbook, StrPath As String, ReportName As String
Dim wrdApp As New Word.Application, wrdDoc As Word.Document, wrdRng As Word.Range
wrdApp.Visible = False
Const wrdBkMk As String = "IDACI1"
StrPath = ThisWorkbook.Path & "\"

ReportName = "335" & Range("SchoolDfE") & "_" & _
  Range("SchoolName") & " " & Range("CurrentYear") & " Unvalidated"
  
'opens the file based on the school number workbook range
Set xlWkBk = Workbooks.Open(StrPath & _
  "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
xlWkBk.Sheets("School vs LA").ChartArea.Copy
xlWkBk.Close False

'Open Word document
With wrdApp
  Set wrdDoc = .Documents.Open(Filename:=StrPath & "Primary Template 2015-16.doc", AddToRecentFiles:=False)
  With wrdDoc
    'Update the document
    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
    
    'Update all the linked charts and tables in the Word document
    'For Each wrdRng In .StoryRanges
      'wrdRng.Fields.Update
      'If wrdRng.StoryType <> wdMainTextStory Then
        'While Not (wrdRng.NextStoryRange Is Nothing)
          'Set wrdRng = wrdRng.NextStoryRange
          'wrdRng.Fields.Update
        'Wend
      'End If
    'Next wrdRng
    
    'Update the contents table so the page numbers are accurate
    '.TablesOfContents(1).Update

    ReportName = StrPath & "Unvalidated Oct 2016\" & ReportName

    'Save the new school report in Word format and then create a pdf version
    .SaveAs2 Filename:=ReportName & ".doc", FileFormat:=wdFormatDocument, AddToRecentFiles:=False
    .SaveAs2 Filename:=ReportName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    .Close False
  End With
  .Quit
End With
Set wrdRng = Nothing: Set wrdDoc = Nothing: Set wrdApp = Nothing: Set xlWkBk = Nothing
Application.DisplayAlerts = True: Application.ScreenUpdating = True
End Sub

 

[DIMWIT]

Re: resize chart once pasted into word

Thank you again

I have adjusted the code as you suggest but this line throws up an error

.InlineShapes(1).ScaleHeight = 65


This is the same error I had originally where the chart pastes into the word document perfectly but adjusting the size seems a total pain...

Everything else works great

 

[Domenic]

Re: resize chart once pasted into word

I have adjusted the code as you suggest but this line throws up an error


.InlineShapes(1).ScaleHeight = 65

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

.InlineShapes([COLOR=#ff0000].InlineShapes.Count[/COLOR]).ScaleHeight = 65

???

 

[DIMWIT]

Re: resize chart once pasted into word

Hello

I get the same error Domenic