Call Word from Excel by vba (2000)

Topic

New Reply

My spreadsheet contains date orientated data and I want the users to be able to click a button and generate a mailmerge document which is date sensitive
I.e. naive user clicks button and Week1 mailmerge is generated using only data that falls into the week1 parameters
same with week2 etc…
Can anyone help with the vba syntax for calling Word to open an existing document please?

Reply | Quote

Replies

Does the example code in post 191959 answer your question?

Steve

Reply | Quote

Does the example code in post 191959 answer your question?

Steve

Reply | Quote

Here is a procedure that initiates a mail merge from Excel. To use it, you must set a reference (in Tools | References… in the Visual Basic Editor) to the Microsoft Word 9.0 Object Library. You will have to modify it to suit your needs; comments indicate where you must substitute the correct values.

Sub StartMailMerge()
Dim wrdApp As Word.Application
Dim wrdMergeDoc As Word.Document
Dim wrdResultDoc As Word.Document
Dim fNotActive As Boolean
Dim strSQL As String
Dim lngWeek As Long

On Error Resume Next

lngWeek = Val(InputBox("Enter week number", , Format(Date, "ww")))
If lngWeek 53 Then
Beep
Exit Sub
End If

' Substitute sheet name and name of date column
strSQL = "SELECT * FROM `Sheet1$` WHERE Format([Date],'ww') = '" & lngWeek & "'"

Set wrdApp = GetObject(, "Word.Application")
If wrdApp Is Nothing Then
Set wrdApp = CreateObject("Word.Application")
If wrdApp Is Nothing Then
MsgBox "Can't start Word.", vbExclamation
Exit Sub
End If
fNotActive = True
End If

On Error GoTo ErrHandler

' Substitute path and name of Word document
Set wrdMergeDoc = wrdApp.Documents.Open("C:WordTest.doc")

With wrdMergeDoc.MailMerge
.OpenDataSource Name:=ActiveWorkbook.FullName, _
ReadOnly:=True, LinkToSource:=True, AddToRecentFiles:=False, _
SQLStatement:=strSQL
' Optional: merge to new document (if it already contains merge fields)
.Execute
End With

ExitHandler:
Set wrdMergeDoc = Nothing
Set wrdApp = Nothing
Exit Sub

ErrHandler:
On Error Resume Next
MsgBox Err.Description, vbExclamation
wrdMergeDoc.Close SaveChanges:=wdDoNotSaveChanges
If fNotActive And Not (wrdApp Is Nothing) Then
wrdApp.Quit
End If
Set wrdApp = Nothing
Resume ExitHandler
End Sub

I hope this works correctly in Office 2000 (I’m using Office XP, where mail merge has changed considerably)

Reply | Quote

Steves response is simple and straightforward but prints the mailmerge document, rather than running the mailmerge and printing all the pages
I changes the mailmerge document into a *.dot file, buit it then prints only page 1
Is there a way to make it print all the pages?
I tried – odoc.printout range:=wdPrintFromTo, from:=”1″, to:=”9″
but everything after odoc.prinout appears to be ignored

Reply | Quote

Steves response is simple and straightforward but prints the mailmerge document, rather than running the mailmerge and printing all the pages
I changes the mailmerge document into a *.dot file, buit it then prints only page 1
Is there a way to make it print all the pages?
I tried – odoc.printout range:=wdPrintFromTo, from:=”1″, to:=”9″
but everything after odoc.prinout appears to be ignored

Reply | Quote

Hans – your response generates an error
‘Complie error: user defined type not identified’ on the first line (Dim wrdApp As Word.Application)

I have got ‘MS Office 9.0 object library’ ticked in Tools/References – can you point me any further?
Thanks

Reply | Quote

Hans – your response generates an error
‘Complie error: user defined type not identified’ on the first line (Dim wrdApp As Word.Application)

I have got ‘MS Office 9.0 object library’ ticked in Tools/References – can you point me any further?
Thanks

Reply | Quote

1) Try:

Dim wrdApp As object

2) concerning the code I referenced, this was not written by me. All the coding you are doing in the wrdApp is actually coding in Word VB and not excel VB. SInce I do not use word, I am very limited in my knowledge of word vb and will leave that to those who know that object model.

Steve

Reply | Quote

1) Try:

Dim wrdApp As object

2) concerning the code I referenced, this was not written by me. All the coding you are doing in the wrdApp is actually coding in Word VB and not excel VB. SInce I do not use word, I am very limited in my knowledge of word vb and will leave that to those who know that object model.

Steve

Reply | Quote

As I wrote in my previous reply, you must set a reference to the Microsoft Word 9.0 Object Library.

Reply | Quote

As I wrote in my previous reply, you must set a reference to the Microsoft Word 9.0 Object Library.

Reply | Quote