Filling a Word form from an Excel spreadsheet

R

Roel

This must be quite a routine job but I haven't found a
good example anywhere.
I have an Excel worksheet containing data that will be
exported to a Word document which in fact is a form. My
VBA-program should open the Word document, export the
data to the correct locations in the form, save the form
under a new filename and close the Word documents.
Any examples coming close to solving this problem?
Thanks,
Roel
 
T

Tom Ogilvy

http://support.microsoft.com/support/kb/articles/Q123/8/59.asp
ACC: Sample OLE Automation for MS Word and MS Excel

http://support.microsoft.com/support/OfficeDev/FaqVBOffice.asp
Frequently Asked Questions about Microsoft Office Automation Using Visual
Basic

http://support.microsoft.com/support/OfficeDev/offdevinapps.asp
Programming Office from Within Office

http://support.microsoft.com/default.aspx?scid=kb;en-us;138283&Product=xlw
How to Create a Form Letter with a Visual Basic Macro

http://support.microsoft.com/default.aspx?scid=kb;en-us;213316&Product=xlw
XL2000: Macro to Link a Range of Cells in Word

http://support.microsoft.com/default.aspx?scid=kb;en-us;184974&Product=xlw
OFF: How to Use (OLE) Automation with Word
 
D

Debra Dalgleish

If you have bookmarks in the Word document, you could use a macro
similar to the following:

'===========================
Sub CopyToWord()
'set a reference to Word
' -- in VBE, choose Tools>References
Dim ws As Worksheet
Dim r As Long
Dim WdApp As Object
Dim strPath As String
Dim strFile As String
Dim strFileNew As String
Dim doc As Object
Set ws = Sheets("Sheet1")

strPath = "C:\Data\"
strFile = "Test.doc"
strFileNew = "TestNew.doc"

On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
Set WdApp = CreateObject("Word.Application")
End If

On Error GoTo 0
WdApp.Documents.Open Filename:=strPath & strFile, _
ConfirmConversions:=False, ReadOnly:=False
Set doc = WdApp.activedocument
WdApp.Visible = True

With WdApp
.Selection.Goto What:=wdGoToBookmark, Name:="bkmk1"
.Selection.TypeText Text:=CStr(ws.Range("Field01").Value)

.Selection.Goto What:=wdGoToBookmark, Name:="bkmk2"
.Selection.TypeText Text:=CStr(ws.Range("Field02").Value)
End With

doc.SaveAs Filename:=strPath & strFileNew
doc.Close SaveChanges:=wdSaveChanges
Set WdApp = Nothing

End Sub
'======================================
 
R

Roel

Thanks, Debra. This is of great help.
Roel
-----Original Message-----
If you have bookmarks in the Word document, you could use a macro
similar to the following:

'===========================
Sub CopyToWord()
'set a reference to Word
' -- in VBE, choose Tools>References
Dim ws As Worksheet
Dim r As Long
Dim WdApp As Object
Dim strPath As String
Dim strFile As String
Dim strFileNew As String
Dim doc As Object
Set ws = Sheets("Sheet1")

strPath = "C:\Data\"
strFile = "Test.doc"
strFileNew = "TestNew.doc"

On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
Set WdApp = CreateObject("Word.Application")
End If

On Error GoTo 0
WdApp.Documents.Open Filename:=strPath & strFile, _
ConfirmConversions:=False, ReadOnly:=False
Set doc = WdApp.activedocument
WdApp.Visible = True

With WdApp
.Selection.Goto What:=wdGoToBookmark, Name:="bkmk1"
.Selection.TypeText Text:=CStr(ws.Range ("Field01").Value)

.Selection.Goto What:=wdGoToBookmark, Name:="bkmk2"
.Selection.TypeText Text:=CStr(ws.Range ("Field02").Value)
End With

doc.SaveAs Filename:=strPath & strFileNew
doc.Close SaveChanges:=wdSaveChanges
Set WdApp = Nothing

End Sub
'======================================



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.
Click to expand...
 
Top