Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    Control Mail Merge to Word from Excel

    I have an Excel spreadsheet with my merge data and have a Word document set up to merge the data from my Excel spreadsheet. I use the following code to control this merge from Excel which works fine:

    Dim wdOutputName, wdInputName As String
    wdOutputName = ThisWorkbook.Path & "\Labels " & Format(Date, "d mmm yyyy")
    wdInputName = ThisWorkbook.Path & "\LABELS Master.doc"
    ' open the mail merge layout file
    Dim wdDoc As Object
    Set wdDoc = GetObject(wdInputName)
    wdDoc.Application.Visible = True
    With wdDoc.MailMerge
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .Execute Pause:=False
    End With
    ' show and save output file
    wdDoc.Application.Visible = True
    wdDoc.Application.ActiveDocument.SaveAs wdOutputName
    ' cleanup
    wdDoc.Close SaveChanges:=False
    Set wdDoc = Nothing

    I have both the Word document and the Excel data file in a specific folder so the two are linked. This works fine providing the Word document remains in this specified folder. However my people have a nasty habit of moving things around. Assuming both the Excel data file and the Word merge document are always in the same folder, is there any way I can add to the code above to reconnect the Word document to the Excel data file if they are moved to another folder?

    Thanks in advance!

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 466 Times in 444 Posts
    Hi Ted

    Puzzled. In your code you already have..

    wdInputName = ThisWorkbook.Path & "\LABELS Master.doc" the file [LABELS Master.doc] is already assumed to be in the same folder as the Excel file (i.e. ThisWorkbook) that is running the code.


Posting Permissions

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