Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Call Word from Excel by vba (2000)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Call Word from Excel by vba (2000)

    Does the example code in <post#=191959>post 191959</post#> answer your question?

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Call Word from Excel by vba (2000)

    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.

    <code>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 < 1 Or lngWeek > 53 Then
    Beep
    Exit Sub
    End If

    ' Substitute sheet name and name of date column
    strSQL = "SELECT * FROM `Sheet1$` WHERE Format(<!t>[Date]<!/t>,'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</code>

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

  4. #4
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call Word from Excel by vba (2000)

    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

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call Word from Excel by vba (2000)

    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Call Word from Excel by vba (2000)

    1) Try:
    <pre>Dim wrdApp As object</pre>


    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Call Word from Excel by vba (2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •