2

I would like to do the following:

  1. In Excel, create tables and define the range (e.g. A1 to B13 is "Arf", saved in the file

  2. With VBA, move from Excel to an open Word document

  3. Within the open Word document, search for a string (e.g. "[Table goes here]")

  4. Replace Word string with Excel defined range (e.g. [Table goes here] is replaced by "Arf")

  5. I would have many defined ranges for many different tables and depending on which pre-defined table I am working with in Excel, I would macro to have that defined range imported to the respective string in Word

So far I have found lots of code for going in and changing text in a file location, but I would like this to be for whatever Word file is currently open (working in both files, Excel & Word, concurrently).

I don't really have existing code for this, since I don't know how to do it, but let me try to give what I'm thinking:

Sub Replace_Word_String_with_Excel_Range()

Application.Goto Reference:="Arf"
    Selection.Copy

Application(Word).Find.Replacement
With Selection.Find
    .Text = "[Table goes here]"
    .Replacement.value = "Arf"
End With

End Sub

I feel like I can create an Excel file and create templates, defining the tables. I would have them all named (e.g. Arf, Bark, & Woof). What I don't understand is how to "alt-tab" to Word via code. Additional confusion on my part is how to paste the whole Excel defined section via the Replacement.Value function.

Any help to figure this out would be much appreciated. As I am actively working on this, I will try to edit this post and update accordingly.

Thanks!

Community
  • 1
  • 1
Cyril
  • 6,448
  • 1
  • 18
  • 31

1 Answers1

2

Use DocVariables in Word. If you don't know what DocVariables are, just do a little research to see how to get it setup and working. Then, simply run the script below, and as always, modify to suit your specific needs.

Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next

objWord.ActiveDocument.variables("FirstName").Value = Range("FirstName").Value
objWord.ActiveDocument.variables("LastName").Value = Range("LastName").Value
objWord.ActiveDocument.variables("AnotherVariable").Value = Range("AnotherVariable").Value


objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

This code sits in Excel. Don't forget to set a reference in Excel, to MS Word.

  • Just want to chime in and say thanks for adding this code. I need to test it out but have been strapped for time to work on that project over the past few days. I will be back to add an update when I get that far. Again, thank you! – Cyril Nov 08 '16 at 18:17
  • This worked for me. Thank you much! DocVariables info helped a lot in trying to understand this all. – Cyril Nov 10 '16 at 17:00
  • For anybody who needs to lookup doc variables, this question is a good place to start: http://stackoverflow.com/questions/205179/what-is-a-docvariable-in-word – Eddy Mar 10 '17 at 12:51