Issue with Bookmark

[chandrashekar]

Hello,

am trying to paste chart & table to word document using excel vba code. I have created few bookmarks in word. but when I run code am getting multiple chart & graphs.

May I know what is the issue?

Public Sub copypaste_TemplateTable()

Dim pvt_tbl As PivotTable
Dim appWrd As Object
Dim objDoc As Object
Dim FilePath As String
Dim FileName As String
Dim wb As Workbook
Dim wbfile As String


FilePath = "D:\"
FileName = "AOne.docx"


Set appWrd = CreateObject("Word.Application")
Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName)

appWrd.Visible = True

On Error Resume Next

appWrd.Selection.Goto What:=wdGoToBookmark, Name:="Chart_1_1"
ThisWorkbook.Worksheets(1).Select
ThisWorkbook.Worksheets(1).ChartObjects(1).Copy
appWrd.Selection.Paste xlPasteAll
appWrd.Bookmarks(1).Add "Chart_1_1", objrange

end sub

Regards,

Chandru

 

[chandrashekar]

Hello All,

Thanks its working fine now. Is there any way other than bookmark to paste data in word(Learning purpose)?

Could you let me know is there any materials is there to learn VBA code for word and PowerPoint.

Thanks in advance.


Regards,

Chandru

 

[NdNoviceHlp]

Thanks for posting your outcome. There is mailmerge, U can move to any selection point in the document and do whatever, U can also paste to formfields or tables and probably do some stuff that I haven't mentioned. Google is a really good at helping U find any resource materials that U need. Have a nice day. Dave

 

[gmaxey]

Dave/chandrashekar,

Sorry I have not be back earlier and I'm glad I could help. Dave, thanks for the warm welcome.

 

[chandrashekar]

Hello All,

Thanks Much.


Regards,

Chandru

 

[Macropod]

Is there any way other than bookmark to paste data in word(Learning purpose)?

You could insert the output into a content control, table, a defined Section or range within the document; it's really up to you to decide what best suits your needs.

 

[Macropod]

Hi Greg,

Welcome to the fray!

 

[NdNoviceHlp]

I'm not happy with the code I posted... it was missing the "Set objDoc = Nothing" and that lack of clarity re. file path was probably making it useless for less advanced users. Also, I'm guessing repetitive use of this routine will eventually cause the clipboard to overfill and crash. API's should probably be used to open, empty and then close the clipboard after each use. So...
module code...

'Open the clipboard to read
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
'Empty the clipboard 
Public Declare Function EmptyClipboard Lib "user32" () As Long
'Close the clipboard
Public Declare Function CloseClipboard Lib "user32" () As Long

To operate adjust the code for file path to suit, the chart name to suit, the range to suit and both bookmark names to suit. Then to operate...

Call XLTable_Rng_Chart_toWordBookmark
OpenClipboard
EmptyClipboard
CloseClipboard

Here's the updated code. Dave

Public Sub XLTable_Rng_Chart_toWordBookmark()
Dim appWrd As Object, objDoc As Object
Dim FilePath As String, FileName As String
Dim TblRng As Range, ORng As Object, LngIndex As Long
'Transfers chart(1) of sheet(1) to existing Word bookmark
'Transfers table/range to existing Word bookmark

FilePath = "D:\" '***change to suit
FileName = "AOne.docx" '***change to suit
On Error GoTo ErFix
Set appWrd = CreateObject("Word.Application")
Set objDoc = appWrd.Documents.Open(FilePath & FileName)

'put chart(1) from sheet(1) in bookmark
ThisWorkbook.Worksheets(1).ChartObjects(1).CopyPicture _
           Appearance:=xlScreen, Format:=xlPicture
With objDoc
'*** "Chart_1_1" bookmark must exist!!
Set ORng = objDoc.Bookmarks("Chart_1_1").Range
ORng.Delete
ORng.Paste
.Bookmarks.Add "Chart_1_1", ORng
End With
Application.CutCopyMode = False

'put table/range in bookmark
'**set TblRng to ws range containing table
With ThisWorkbook.Worksheets(1)
'***change TblRng to suit
Set TblRng = .Range(.Cells(1, "A"), .Cells(6, "G"))
End With
TblRng.Copy
With objDoc
'*** "TblRngBookmark" must exist!!
Set ORng = objDoc.Bookmarks("TblRngBookmark").Range
ORng.Delete
For LngIndex = ORng.Tables.Count To 1 Step -1
ORng.Tables(LngIndex).Delete
Next
ORng.Paste
.Bookmarks.Add "TblRngBookmark", ORng
End With
Application.CutCopyMode = False

Set ORng = Nothing
'save & quit Word
appWrd.objDoc.Close savechanges:=True
Set objDoc = Nothing
appWrd.Quit
Set appWrd = Nothing
Exit Sub

ErFix:
On Error GoTo 0
MsgBox "Error"
Set ORng = Nothing
Set objDoc = Nothing
appWrd.Quit
Set appWrd = Nothing
End Sub

 

[Macropod]

I can't see the point of all the clipboard manipulations and you should test for the presence of the Word bookmark, instead of assuming it exists:

With objDoc
  'Confirm the "Chart_1_1" bookmark exists!!
  If .Bookmarks("Chart_1_1").Exists Then
    Set ORng = .Bookmarks("Chart_1_1").Range
    ORng.Delete
    ORng.Paste
    .Bookmarks.Add "Chart_1_1", ORng
  End If
End With

 

[Macropod]

.Bookmarks("Chart_1_1").Exists
should be:
.Bookmarks.Exists("Chart_1_1")