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

 

[NdNoviceHlp]

Untested code. Read comments. The table bookmark must exist. Please use code tags. HTH. Dave

Public Sub copypaste_TemplateTable()
Dim pvt_tbl As PivotTable
Dim appWrd As Object, objDoc As Object
Dim FilePath As String, FileName As String
Dim TblRng As Range
FilePath = "D:\"
FileName = "AOne.docx"
On Error GoTo ErFix
Set appWrd = CreateObject("Word.Application")
Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName)
appWrd.Visible = True
ThisWorkbook.Worksheets(1).ChartObjects(1).Copy
With objDoc.Bookmarks("Chart_1_1").Range
.Paste
End With
Application.CutCopyMode = False
'**set TblRng to table range
With ThisWorkbook.Worksheets(1)
Set TblRng = .Range(.Cells(1, "A"), .Cells(10, "G"))
End With
TblRng.Copy
'***TblRngBookmark must exist!!
With objDoc.Bookmarks("TblRngBookmark").Range
.Paste
End With
Application.CutCopyMode = False
Exit Sub

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

 

[chandrashekar]

Hello,

I tried working with the code but while pasting chart its gives error. However Bookmark is exist in the word document and chart is exist in the excel file.

Could you please help me on this.


Regards,

Chandru

 

[NdNoviceHlp]

Comment out the "On Error goto ErFix". On what line of code does it error and what is the error? Dave

 

[chandrashekar]

Hi,

I tried to run it once again using F8 Key(step by step execution) once it comes to pasting chart line its pasting both table as well as chart. (don't know yesterday it was giving error today its working but getting different result)

Even its replicating chart & table(its not clearing old images stored in the bookmark).


Regards,

Chandru

 

[NdNoviceHlp]

Trial this. It seems to work for me. Dave

Public Sub copypaste_TemplateTable()
Dim pvt_tbl As PivotTable
Dim appWrd As Object, objDoc As Object
Dim FilePath As String, FileName As String
Dim TblRng As Range
FilePath = "D:\"
FileName = "AOne.docx"
On Error GoTo ErFix
Set appWrd = CreateObject("Word.Application")
Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName)
'appWrd.Visible = True
ThisWorkbook.Worksheets(1).ChartObjects(1).CopyPicture _
           Appearance:=xlScreen, Format:=xlPicture
With objDoc.Bookmarks("Chart_1_1").Range
.Delete 'remove previous contents
.Paste
End With
Application.CutCopyMode = False
'**set TblRng to table range
With ThisWorkbook.Worksheets(1)
Set TblRng = .Range(.Cells(1, "A"), .Cells(10, "G"))
End With
TblRng.Copy
'***TblRngBookmark must exist!!
With objDoc.Bookmarks("TblRngBookmark").Range
.Delete 'remove previous contents
.Paste
End With
Application.CutCopyMode = False
Exit Sub

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

 

[chandrashekar]

Hello,
Thanks for the reply. Will check & let u know....

 

[chandrashekar]

Hello,

I tried with the code first time its pasting correctly if I run same code next time its giving error in line "With objDoc.Bookmarks("Chart_1_1").Range" and even bookmark is getting deleted from the word document.

Could you please help me out on it


Regards,

Chandru

 

[NdNoviceHlp]

Apologies Chandru. I forgot how much I hate Word. Trial this. Third time is a charm. Dave

Public Sub copypaste_TemplateTable()
Dim pvt_tbl As PivotTable
Dim appWrd As Object, objDoc As Object
Dim FilePath As String, FileName As String
Dim TblRng As Range, ORng As Object, ORng2 As Object
FilePath = "D:\"
FileName = "AOne.docx"
On Error GoTo ErFix
Set appWrd = CreateObject("Word.Application")
Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName)
appWrd.Visible = True
'put chart in bookmark
ThisWorkbook.Worksheets(1).ChartObjects(1).CopyPicture _
           Appearance:=xlScreen, Format:=xlPicture
Set ORng = objDoc.Bookmarks("Chart_1_1").Range
With ORng
.Paste
.Bookmarks.Add Name:="Chart_1_1"
End With
Application.CutCopyMode = False

'put table in bookmark
'**set TblRng to table range
With ThisWorkbook.Worksheets(1)
Set TblRng = .Range(.Cells(1, "A"), .Cells(10, "G"))
End With
TblRng.Copy
'***TblRngBookmark must exist!!
Set ORng2 = objDoc.Bookmarks("TblRngBookmark").Range
With ORng2
.Paste
.Bookmarks.Add Name:="TblRngBookmark"
End With
Application.CutCopyMode = False
'save & quit Word
'appWrd.ActiveDocument.Close savechanges:=True
'appWrd.Quit
'Set appWrd = Nothing
Exit Sub

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

 

[chandrashekar]

Hi,

Even me too hate vba code in Word .

Thanks for the code. will try and get back to u.

Regards,

Chandru