Extract from EXCEL to MICROSOFT WORD

Question

I write proposals using a very simple Excel Spreadsheet. There are a couple of fields involved and maybe a few IF statements - i.e. the state a project is located in will affect the cost...very basic stuff.

Using this Excel spreadsheet, I generate a Microsoft Word Document from a template, but manually, constantly pressing CTRL+F and manually typing numbers in. How do I go about automatically populating said Word Document - I want to be able to finish my proposal in Excel, and then hit a button and have a word document that's ready to be emailed to the client.

Would be nice to just be able to generate a new word document in the same location from just the excel spreadsheet.

Answer 1

Update Bookmarks in Word; controlled from Excel:

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) 

For Each bmk In doc.Bookmarks 
If bmk.Name = "BrokerFirstName" Then bmk.Range.Text = Range("B1").Value 
If bmk.Name = "BrokerLastName" Then bmk.Range.Text = Range("B2").Value 
Next 

objWord.Visible = True 

End Sub

IF you don't know how this works, do a little Googling for 'Word DocVariable'.

Good luck!!