VBA to copy data in Excel and export to Word Document

[ak_254]

Hi all,

I am currently working on a VBA code where I am trying to export data from a cell in Excel to bookmarked location(s) in a Word Doc.

VBA Code features I am currently struggling with are..

1) VBA code to automatically replace the text that is in the bookmarked location of the Word Doc
1) VBA code to keep the text format of the data in Excel (colored text) when pasting/inserting into Word Doc
2) VBA code to automatically save and close the file upon auto filling necessary fields.

Below is what I currently have and would greatly appreciate any help or point in the right direction.

Sub Update_Catalog_Data()
' Update Accessory Status in Catalog

Dim objWord As Object
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Accessory List")

Set objWord = CreateObject("Word.Application")

objWord.Visible = True

objWord.Documents.Open "C:\Users\ant\Desktop\Testing\Catalog.docx"

With objWord.ActiveDocument

.Bookmarks("Q8").Range.Text = ws.Range("J10").Value
.Bookmarks("AY8").Range.Text = ws.Range("M10").Value
.Bookmarks("Q10").Range.Text = ws.Range("J12").Value
.Bookmarks("AY10").Range.Text = ws.Range("M12").Value
.Bookmarks("Q11").Range.Text = ws.Range("J13").Value
.Bookmarks("AY11").Range.Text = ws.Range("M13").Value
.Bookmarks("Q12").Range.Text = ws.Range("J14").Value
.Bookmarks("AY12").Range.Text = ws.Range("M14").Value
.Bookmarks("Q9").Range.Text = ws.Range("J11").Value
.Bookmarks("AY9").Range.Text = ws.Range("M11").Value

Set objWord = Nothing

End Sub

 

[sc0001]

Hi all,

I am currently working on a VBA code where I am trying to export data from a cell in Excel to bookmarked location(s) in a Word Doc.

VBA Code features I am currently struggling with are..

1) VBA code to automatically replace the text that is in the bookmarked location of the Word Doc
1) VBA code to keep the text format of the data in Excel (colored text) when pasting/inserting into Word Doc
2) VBA code to automatically save and close the file upon auto filling necessary fields.

Below is what I currently have and would greatly appreciate any help or point in the right direction.

Sub Update_Catalog_Data()
' Update Accessory Status in Catalog

Dim objWord As Object
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Accessory List")

Set objWord = CreateObject("Word.Application")

objWord.Visible = True

objWord.Documents.Open "C:\Users\ant\Desktop\Testing\Catalog.docx"

With objWord.ActiveDocument

.Bookmarks("Q8").Range.Text = ws.Range("J10").Value
.Bookmarks("AY8").Range.Text = ws.Range("M10").Value
.Bookmarks("Q10").Range.Text = ws.Range("J12").Value
.Bookmarks("AY10").Range.Text = ws.Range("M12").Value
.Bookmarks("Q11").Range.Text = ws.Range("J13").Value
.Bookmarks("AY11").Range.Text = ws.Range("M13").Value
.Bookmarks("Q12").Range.Text = ws.Range("J14").Value
.Bookmarks("AY12").Range.Text = ws.Range("M14").Value
.Bookmarks("Q9").Range.Text = ws.Range("J11").Value
.Bookmarks("AY9").Range.Text = ws.Range("M11").Value

Set objWord = Nothing

End Sub

After 5 years since the existence of this post, I am having the same question too. Even AI cannot provide an accurate solution, would greatly appreciate an excel expert who knows how to do it (p.s. I reckon the bookmarked areas are 'fields'?)