VBA Excel replace Word text with Excel cell value

0
votes

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.

1
excelvbams-word
Where does range("A1") refer to on your word doc? – Samuel Everson
I don't know. I was guessing, that to my table as shown in the pic above. – MKR
I did a few things with Excel and Word together a while back, if I remember correctly, the way to reference the table cells is using the Table.Cell method. I'll leave that there for you but will see if I can dig my project up. – Samuel Everson
That would be fantastic. I tried several time to achieve my goal. On top of that this is not only one place, where I want to input the data. – MKR
It seems I hadn't sent that application home unfortunately, only one that is Word only and I asked questions about data from word to excel. – Samuel Everson

1 Answers

1
votes

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