Solved: Writing to a word file from within excel

[gscarter]

Hi,

Is it possible to write straight to Microsoft Word document from vba within an excel workbook?

[CreganTur]

It's very possible, but you'll need to set a reference to Microsoft Word Object Library (Tools -> References).

Put this code in a new module. Make sure there is text in A1 & A2 for this test.

This is a very basic example, but hopefully it'll point you in the right direction.

[VBA]Sub WordTest()
Dim doc As Word.Document
Dim myWord As Word.Application
Dim strHead As String
Dim strRecords As String
Dim ws As Worksheet
On Error GoTo ErrorHandler
Set ws = Sheet1
strHead = ws.Range("A1").Text
strRecords = ws.Range("A2").Text
Set myWord = New Word.Application
Set doc = myWord.Documents.Add
myWord.Visible = True
'paste variables into word document
doc.Paragraphs(1).Range.Text = strHead & vbCrLf
doc.Paragraphs(2).Range.Text = strRecords
doc.Close SaveChanges:=wdPromptToSaveChanges
EndProc:
myWord.Quit
Set myWord = Nothing
Exit Sub
ErrorHandler:
If Err = 4198 Then MsgBox "You refused to save this document"
Resume EndProc
End Sub
[/VBA]

[gscarter]

Hi,

Thanks a lot for the information, i'll give it a go.

With regards to setting up the reference to Microsoft Word Object, will that be saved in the file or within my version of office?

What i mean is, if i created the object reference, then gave the file to another user would they have to set up the reference within their Excel for this to work?

Thanks
Gary

[Bob Phillips]

It's very possible, but you'll need to set a reference to Microsoft Word Object Library (Tools -> References).

Not necessarily so.

Assuming your code works

[vba]

Sub WordTest()
Dim doc As Object
Dim myWord As Object
Dim strHead As String
Dim strRecords As String
Dim ws As Worksheet
On Error GoTo ErrorHandler
Set ws = Sheet1
strHead = ws.Range("A1").Text
strRecords = ws.Range("A2").Text
Set myWord = CreateObject("Word.Application")
Set doc = myWord.Documents.Add
myWord.Visible = True
'paste variables into word document
doc.Paragraphs(1).Range.Text = strHead & vbCrLf
doc.Paragraphs(2).Range.Text = strRecords
doc.Close SaveChanges:=wdPromptToSaveChanges
EndProc:
myWord.Quit
Set myWord = Nothing
Exit Sub
ErrorHandler:
If Err = 4198 Then MsgBox "You refused to save this document"
Resume EndProc
End Sub
[/vba]

[gscarter]

Not necessarily so.

Assuming your code works

[vba]

Sub WordTest()
Dim doc As Object
Dim myWord As Object
Dim strHead As String
Dim strRecords As String
Dim ws As Worksheet
On Error GoTo ErrorHandler
Set ws = Sheet1
strHead = ws.Range("A1").Text
strRecords = ws.Range("A2").Text
Set myWord = CreateObject("Word.Application")
Set doc = myWord.Documents.Add
myWord.Visible = True
'paste variables into word document
doc.Paragraphs(1).Range.Text = strHead & vbCrLf
doc.Paragraphs(2).Range.Text = strRecords
doc.Close SaveChanges:=wdPromptToSaveChanges
EndProc:
myWord.Quit
Set myWord = Nothing
Exit Sub
ErrorHandler:
If Err = 4198 Then MsgBox "You refused to save this document"
Resume EndProc
End Sub
[/vba]

Is there a way of adapting:

doc.Close SaveChanges:=wdPromptToSaveChanges

to save to a specified path without prompting the user to save?

Gary

[CreganTur]

This will save automatically:
[VBA]

doc.Close SaveChanges:=wdSaveChanges

[/VBA]

[Bob Phillips]

Sorry, I should have replaced the Word constants with late binding.

Change

wdPromptToSaveChanges

to

-2

[gscarter]

This will save automatically:
[vba]

doc.Close SaveChanges:=wdSaveChanges

[/vba]

Hi, thanks for the suggestion

Where will this automatically save to?
Can i specify the path it saves to?

Gary

[gscarter]

Sorry, I should have replaced the Word constants with late binding.

Change

wdPromptToSaveChanges

to

-2

Sorry, you've confused me with that.

What does that do exactly?

[Bob Phillips]

It the value equivalent of wdPromptToSaveChanges as you cannot use Word constants in a late bound procedure. If you don't have Option Explicit, your code will probably treat that as an unitialised variable, a value of 0.

[gscarter]

Ah ok, thanks a lot for your help.

A quick question, in the code above you can specify what data is written to different paragraphs. How can i reference other section of the word document, headers and footers for example.

I've tried searching google for this, as it seems a simple question, but i cannot seem to find the answer.

Gary