Need to export data from Excel 2013 into a Word Doc (Mail Merge) that is based upon an existing .dotx template

I am having trouble getting this to work. I need to make sure I am late-binding objects due to compatibility issues with various Office versions being used by coworkers.

I have an existing .dotx Word template that needs to be used as the basis for the final document. The data is coming from an SQL query of the Excel sheet.

I am finding most of my issues are with the Word aspect. It has to do with whether Word is already running or not, or if there is an open document or not.

Thanks in advance.
AZshooterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
The standard way of using mailmerge is via the Word user interface. Here you create the 'main' document, i.e. one with mailmerge fields and fixed text. It does not need to be a template in the Word sense, and can be saved as a .docx.

With it open in Word, you click on the Finish & Merge button on the Mailings tab, and the merge takes place.

Are you doing something different? If so what  - and what exactly goes wrong>
0
AZshooterAuthor Commented:
Hello. Thank you for the reply.

Yes, I am looking for the means to do this with a VBA macro in Excel 2013. The workbook stores the inventory of our demo equipment. When a sales-person wants to check it out he prints a request which get populated with the details for a worksheet.

So, in VBA I want to create a new Word document based upon an existing template. Then do the mailmerge with the data from the spreadsheet.

Where I seem to be having problems I think is the new SDI of Office 2013, it used to work very well in 2010. Also, I am trying to late-bind objects in my code now because of variations of Office versions within our group. So, losing IntelliSense probably led to me getting stuff wrong...I'm not that great a programmer.

When this part of my code runs:

    Set wdApp = CreateObject("Word.Application")
    Set wdocSource = wdApp.documents.Add(template:=WordTemplatePath)

Word does not start, nor does a new document get created.

I hope I'm explaining myself clearly.
Thank in advance.
0
AZshooterAuthor Commented:
Here is my solution:

    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set wdocSource = wdApp.documents.Add(template:=WordTemplatePath) 'e.g. "R:\demo\public\EVT_DERF_Catalog.dotx"
    wdocSource.Visible = True

Looks like this works for me.
0
GrahamSkanRetiredCommented:
If visibility is your only problem, then that should do it.

I would ensure that a new instance is only created if none exists already. Multiple instances can give problems since both might modify the Normal template.
    Dim appWord As Object 'Word.Application
    
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application")
    On Error GoTo 0 'resume error checking
    If appWord Is Nothing Then
        'If Word isn't open, create a new instance of Word.
        Set appWord = CreateObject("Word.Application")
        appWord.Visible = True
    End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.