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]

Hi,

I tried the code but table is pasting repetitively. May I know how to delete previous content.


Regards,

Chandru

 

[NdNoviceHlp]

Chandru please post the code U are using. That code worked for me. However, if your pasting a table and not a range which I suspect U are, then it seems that's a different thing requiring some code adjustment to move the placement of the bookmark. My learning so far is that pasting into a bookmark range replaces its' contents but also destroys the bookmark which is why U have to add another one with the same name if U plan on reusing it. If U paste a table, there's some extra characters pasted along with the table which have to be accomodated when U replace the bookmark. So I believe your tables are pasting repetitively because the bookmark has moved from it's original location (because the extra pasted characters haven't been accomodated). That's my hunch but I have been wrong before... many times I'm sure. Anyways, please post your code and/or specify if U are pasting a range containing the table or the table itself. Dave

 

[chandrashekar]

Hi,

Here is the code. Am pasting range containing table.

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 = "C:\Users\Chandra\Desktop\Excel_Word\"
FileName = "Doc1.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

 

[NdNoviceHlp]

Did U trial the code from post #9? Dave

 

[chandrashekar]

Yes. I have taken code from the post 9.


Regards,

Chandru

 

[gmaxey]

Your code worked here for the chart object but each time I ran it added a table.

Try:

Dim lngIndex as Long
With objDoc
  Set oRng = objDoc.Bookmarks("TableRngBookmark").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

 

[chandrashekar]

Hi,

I will try and get back to you.


Regards,

Chandru

 

[chandrashekar]

Hi,

Still having the same issue bookmarks getting deleted from the word doc. If you don't mind can you please give me the entire code.



Regards,

Chandru

 

[NdNoviceHlp]

I asked for some expert help here... Issue with BookMark
gmaxey I really appreciate U taking the time to become a Member and offer your time to help Chandru & I come to some resolution of this ongoing thread. The code I posted using Office 03 seemed to work. When I trialled this in 2010 Office, the charts also kept pasting repetitively along with repetitive table pasting. Anyways, the following code adapted from gmaxey`s sage contribution works for Office 2010 for pasting an XL chart and XL table(range) to specified bookmarks in a Word document. Chandru your filepath seems to have too many "\"`s so make sure that this is correct first. HTH. Dave
ps. Welcome to the Board gmaxey and again thanks for your time

Public Sub copypaste_TemplateTable()
Dim lngIndex As Long
Dim appWrd As Object, objDoc As Object
Dim FilePath As String, FileName As String
Dim TblRng As Range, ORng As Object
On Error GoTo ErFix
Set appWrd = CreateObject("Word.Application")
FilePath = "D:\"
FileName = "AOne.docx"
Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName)
'put chart 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 in bookmark
'**set TblRng to ws range containing table
With ThisWorkbook.Worksheets(1)
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.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
Set ORng = Nothing
End Sub

 

[chandrashekar]

Hi,

Thanks will try this code.


Regards,

Chandru