send XL text to word

[Chad]

Hi All

I want to send cell data from Excel to Word as text, in a specific
place in the word document, same place each month. I thought
bookmarks in Word would do the trick but they get overwritten and need
to be reset time and again. I found this post online which the author
said worked nicely using variables. I can not get it to work as I do
not know how to set Variables in Word. Does anyone know how to get
this code working? Or an alternative method if this is all wrong?

Thanks in advance.

Chad

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.Add(sWdFileName)
'Is there a way to have word open say C:\Test.doc instead of above
With doc
..Variables("Test1").Value = Range("Test1").Value
..Variables("Test2").Value = Range("Test2").Value
..Variables("Test3").Value = Range("Test3").Value
..Range.Fields.Update
End With


objWord.Visible = True

End Sub

 

[OssieMac]

Hi Chad,

Suggest that you create a Template file for the variables. Search Help in
word for Create Template. That way you do not mess up or loose the variables.

A couple of tips:-
When creating the template, if at any time you get a message asking if you
want to save changes to Normal.dot then answer NO. (You have probably done
something wrong).

When opening a template (.dot) file for editing, do not double click from
Windows Explorer. Open Word first and then open the template from within
Word. (Double clicking a template opens a new Word document using the
template.)

When you have the template open, insert the fields as follows:-
Position cursor where you want the field.
Select Insert -> Field -> DocVariable (DO NOT double click DocVariable, just
select it.
In the New name field, give it a name. (The code below uses Test1, Test2 and
Test3 to match named ranges in the Excel worksheet.)
Repeat above for additional fields.

Press Alt+F9 to toggle the display of the field codes on and off. You will
see:-

{ DOCVARIABLE [Test1] }

To set the format of the field:- (Originally from Doug Robbins - Word MVP)

Add a \* Charformat switch inside the closing } of the Docvariable field and
then apply the desired format to the D of Docvariable. (If there is a \*
mergeformat switch there, you can delete it, or change it to \* charformat.)

Save as a Word Template. (.dot). When you select Template format in the Save
Dialog box it will default to a folder that holds templates. You can change
the path. I usually put them in the same folder as the Excel project.)

Now the following code example will create a new document based on the
template created. An input box requests a File name to save the new document.
(Watch the status bar for this; the Input box is not automatically on top.)
Have included a couple of commented out lines of code just for info.

In the VBA editor, you will need to select
Tools -> References -> Microsoft Word nn.0 Object Library
where nn.0 is the version number of your Office. (10.0 is 2002, 11.0 is 2003
and 12.0 is 2007)

If using xl2007 then you will have to open word and record a macro to save
the document to get the correct syntax.

Sub CreateWordDoc()

Dim objWord As Object
Dim strPathFileName As String
Dim strSaveFileName As String

Set objWord = CreateObject("Word.Application")

'Save required path and filename of template file.
'This code uses current directory.
'Can edit path to a string representing your required path
strPathFileName = CurDir & "\" & "My Template.dot" 'Template file

With objWord
'Create a new Word document based on the template
.Documents.Add template:=strPathFileName
.Visible = True
.ActiveDocument.Variables("Test1").Value = Range("Test1").Value
.ActiveDocument.Variables("Test2").Value = Range("Test2").Value
.ActiveDocument.Variables("Test3").Value = Range("Test3").Value

.ActiveDocument.Fields.Update

'Turns off Show Fields in the Word Document.
If .ActiveWindow.View.ShowFieldCodes = True Then
.ActiveWindow.View.ShowFieldCodes = False
End If

strSaveFileName = Application.InputBox _
(Prompt:="Enter file name for saving" _
& vbCrLf & "Do not enter file extension", _
Title:="Get file name", _
Default:="My Test Document", _
Type:=2)

'This code uses current directory.
'Can edit path to a string representing your required path
strSaveFileName = CurDir & "\" & strSaveFileName

'Following code to save document to specified name
.ActiveDocument.SaveAs Filename:=strSaveFileName & ".doc"

'Following code to print document if required
'.ActiveDocument.PrintOut

'Following code to save without saving
.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Close word
.Quit
End With

'Cleanup
Set objWord = Nothing

End Sub

 

[Chad]

Hi OssieMac

Thanks for your extensive reply. I will have a good look at it and
reply regarding how I get on. I really appreciate your time and am so
greatful for your time and advice. Take it easy.

Chad

 

[Chad]

Hi Ossie

Can I just say a very heartfelt thankyou to you. I worked so long on
this one and your elegant answer was perfect. This is an excellent
method for shifting data into word. All the very best.

Chad