PDA

View Full Version : Solved: Writing to a word file from within excel



gscarter
07-18-2008, 02:24 AM
Hi,

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

CreganTur
07-18-2008, 05:25 AM
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.

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

gscarter
07-18-2008, 06:06 AM
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
07-18-2008, 06:10 AM
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



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

gscarter
07-25-2008, 08:05 AM
Not necessarily so.

Assuming your code works



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


Is there a way of adapting:



doc.Close SaveChanges:=wdPromptToSaveChanges


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

Gary

CreganTur
07-25-2008, 08:29 AM
This will save automatically:


doc.Close SaveChanges:=wdSaveChanges

Bob Phillips
07-25-2008, 08:34 AM
Sorry, I should have replaced the Word constants with late binding.

Change

wdPromptToSaveChanges

to

-2

gscarter
07-25-2008, 12:05 PM
This will save automatically:


doc.Close SaveChanges:=wdSaveChanges

Hi, thanks for the suggestion

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

Gary

gscarter
07-25-2008, 12:08 PM
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
07-25-2008, 01:09 PM
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
07-26-2008, 06:12 AM
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