locked
Mail merge a word doc from VBA excel - Defining the datasource RRS feed

  • Question

  • Hi guys,

    I have a macro that will open word from excel, and mail merge the document. The onyl issue is the datasource.

    At the moment I have got the following set up:

    Dim wordDoc As Word.Document
        Set wordDoc = Documents.Open("H:/My Documents/file.doc")

    wordDoc.MailMerge.OpenDataSource Name:="H:/My Documents/Raw Data.xls", ConfirmConversions:=False, Connection:="Entire Spreadsheet", SQLStatement:="SELECT * FROM 'sheet1'", SubType:=wdMergeSubTypeWord2000

    The issue with this is that word has to find and open the datasource each time. I have timed it and it takes just over a minitue to open excel from word. Considering the rest of the macro takes seconds its really annoying.

    Is there any way of having the datasouce as open the linking it that way, for example something crazy like:

    dim datasour as excel.workbook
    set datasour = workbook.open ("path to datasource")
    worddoc.mailmergre.useactivedocument

    Bacially in just an looking for a quick way around this issue.

    I have already looked into linking the word doc with the datasource, however when a linked document is opened via vba it defualts to "no" when prompted to update the document.

    Thanks in advance,

    Dom

    Friday, May 10, 2013 12:47 PM

Answers

  • How about using DocVariables?

    Add a couple DocVariables to your Word file, and run this code from Excel...

    Sub PushToWord()

    Dim objWord As New Word.Application
    Dim doc As Word.Document
    Dim bkmk As Word.Bookmark
    sWdFileName = Application.GetOpenFilename(, , , , False)
    Set doc = objWord.Documents.Open(sWdFileName)
    'On Error Resume Next

    objWord.ActiveDocument.variables("BrokerFirstName").Value = Range("BrokerFirstName").Value
    objWord.ActiveDocument.variables("BrokerLastName").Value = Range("BrokerLastName").Value
    objWord.ActiveDocument.variables("Ryan").Value = Range("Ryan").Value


    objWord.ActiveDocument.Fields.Update

    'On Error Resume Next
    objWord.Visible = True

    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Damon Zheng Wednesday, May 15, 2013 3:52 PM
    Friday, May 10, 2013 9:44 PM