CODEWITHSUNDEEP
excelvbams-word
Geographos
Geographos

Reputation: 1506

VBA Excel replace Word text with Excel cell value

Good morning,

I would like to replace the address in Word using the one, placed in the Excel cell.

My situation looks like in the image below:

enter image description here

I have got an address in Excel and I want to paste in in the Word bracket, based inside the table cell.

There are some solutions:

Write to Word Document from Excel VBA

Pasting a String from excel to Word using word-VBA

VBA from Word to Excel

VBA macros: Excel to Word text replacement

which differs from my situation.

My code so far looks like this:

 Sub RamsOpen()
 Dim appWD As Word.Application
 Set appWD = New Word.Application
 Dim docWD As Word.Document
 Set docWD = appWD.Documents.Open(ActiveWorkbook.path & "\RAMS.docx.docm")
 appWD.Visible = True

 Sheets("Frontsheet").Range("D18").Copy

 docWD.Content.InsertAfter Range("A1")

 End Sub

The Word document is opening, but I don't know, where my text has been copied.

The god hint appears to be here:

https://exceloffthegrid.com/controlling-word-from-excel-using-vba/

but applies to the blank Word document I think.

Upvotes: 0

Views: 1011

Answers (1)

Samuel Everson
Samuel Everson

Reputation: 2102

The easiest way I know of inserting data to a Word document is by using the Bookmarks object (Documentation here) (wordmvp writeup here - very easy to follow).

Taking that into account, as you are controlling this from Excel, I'd put your address value into a String variable and assign that variable to a bookmark on the document.

Something like: 

Dim appWD As Word.Application
Set appWD = New Word.Application
Dim docWD As Word.Document
Set docWD = appWD.Documents.Open(ActiveWorkbook.path & "\RAMS.docx.docm")
appWD.Visible = True
Dim ExcelAddressValue as String

ExcelAddressValue = Sheets("Frontsheet").Range("D18").Value

docWd.Bookmarks("YourBookmarkNameHere").Range.Text = ExcelAddressValue

Upvotes: 1

Related Questions