Solved

Merge excel data into a word merge file

Posted on 2014-04-23
28
438 Views
Last Modified: 2014-04-29
I have an excel spreadsheet with column headings and a list of names.  there is a column that the user will put a 'X' in if they want to print a letter to this person.  So I would like to add a button to spreadsheet that will go through and get the records that have an 'X' and merge those records to a Word Merge File to create a new Word Document.  

I know how to get the data, my question is how can use the recordset or table created in excel vba and with vba open  the merge document, merge the recordset and save a to a new word file, all being automated.

When I create the merge file in word and define the fields, how do I set that up when the table or recordset isnt built yet and wont be until program execution.

thanks in advnace for the help.
0
Comment
Question by:mgmhicks
  • 15
  • 10
  • 2
  • +1
28 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40018519
I am having a bit of a problem in understanding exactly what is required here.

Do you have a column with a checkbox that needs to be checked for its row to be included in a Mail Merge output?
0
 

Author Comment

by:mgmhicks
ID: 40018711
Yea, what I thought I would do was put a button on the spreadsheet then create a datatable or recordset with just the records I need for the mail merge.  So I can get the data to a table or recordset, what I don't know is how to feed that to a word letter template, so that a letter is created for each recordset row.

thanks
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 500 total points
ID: 40018834
This should do it (tested and working by me):
Sub Demo()

    Dim wrdApp As Object
    Dim doc As Object
    Dim sPathFileTemplate As String

    ' Create an instance of WINWORD.EXE
    Set wrdApp = CreateObject("Word.Application")
    
    ' Set the path of the word merge document
    sPathFileTemplate = ThisWorkbook.Path & "\MyTemplate.docx"

    ' create a new file based on the merge document
    Set doc = wrdApp.Documents.Add(sPathFileTemplate)
    
    ' Hide Word (actually just make sure it doesn't show)
    wrdApp.Visible = False
          
    ' Connect the datasource to the merge document
    doc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, _
                                 ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
                                 AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                                 WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                                 Format:=wdOpenFormatAuto, Connection:= _
                                 "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
                                 "User ID=Admin;" & _
                                 "Data Source=" & ThisWorkbook.FullName & ";" & _
                                 "Mode=Read;Extended Properties=" & _
                                 "HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";", _
                                 SQLStatement:="SELECT * FROM `YourWorksheet$` Where(YourCriteriaColumn = 'x');", _
                                 SQLStatement1:="", _
                                 SubType:=wdMergeSubTypeAccess
    
    ' Execute the merge
    With doc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    
    ' Save merged document to same folder as this file (you can set the path after "wrdApp.ActiveDocument.SaveAs"
    wrdApp.ActiveDocument.SaveAs ThisWorkbook.Path & "\Merged.docx"

    doc.Close False
    Set doc = Nothing
    wrdApp.Quit False
    Set wrdApp = Nothing

    MsgBox "Done"

End Sub

Open in new window


Notes:
1. The data should have a header row.
2. YourWorksheet$ is the name of the worksheet containing the data, with an added dollar sign (IMPORTANT: don't forget the dollar sign!)
3. YourCriteriaColumn is the text of the criteria column in the header row
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40019445
You can put the criteria into your Word merge document to check the the column with "X" whether to include that record.

Not sure of exact syntax but would be along lines of:

IF CheckField = "X" then Next Record ELSE MergeField

Thanks,
Rob H
0
 

Author Comment

by:mgmhicks
ID: 40020137
MacroShadow  my data actually starts on row 14 , I want to look at column z to see if it is blank, if the field is blank then I want to merge that record.   Can I iterate through the recordset and create a separate document for each record, or do I have to create all records into a single.   Also merge field names, how am I associating the merge field with the column name which is Z at this point.

thanks again for helping me.
0
 

Author Comment

by:mgmhicks
ID: 40020205
Receiving error "String is longer than 255 characters."  Here is the new code its erroring on

 doc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, _
                                 ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
                                 AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                                 WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                                 Format:=wdOpenFormatAuto, Connection:= _
                                 "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
                                 "User ID=Admin;" & _
                                 "Data Source=" & ThisWorkbook.FullName & ";" & _
                                 "Mode=Read;Extended Properties=" & _
                                 "HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";", _
                                 SQLStatement:="SELECT * FROM `90 Days Until Renewal$`;", _
                                 SQLStatement1:="", _
                                 SubType:=wdMergeSubTypeAccess
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40020523
Actually, thinking about it rather than use conditional MergeFields, there is an option when setting up the Merge document to filter the source data. This would then only create merge documents for those that match the Filter Criteria; your example would be to check column containing X.

In answer to your question about creating separate documents for each record, the whole point of Mail Merge is that you have one template document into which you populate the data on a record by record basis and then either e-mail or print hard copy for each.

To get round headers being on row 14, I would suggest you give your data in Excel a range name and then use the range name as data source rather than sheet name.

Thanks
Rob H
0
 

Author Comment

by:mgmhicks
ID: 40020533
Thanks Rob, what I am doing now is just getting the appropriate data and moving to a new sheet with only the fields I need and the header on row 1.

Then I thought I would merge that data to create a single document with all records in the one document.

I'll be keeping you posted.
0
 

Author Comment

by:mgmhicks
ID: 40027183
ok, have all the data I need on a separate sheet.  But when I hit this code, I get string is more than 255 characters.  Can we break this down a little to see what it really is?

thanks

 
doc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, _
                                 ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
                                 AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                                 WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                                 Format:=wdOpenFormatAuto, Connection:= _
                                 "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
                                 "User ID=Admin;" & _
                                 "Data Source=" & ThisWorkbook.FullName & ";" & _
                                 "Mode=Read;Extended Properties=" & _
                                 "HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";", _
                                 SQLStatement:="SELECT * FROM `MergeRecords`;", _
                                 SQLStatement1:="", _
                                 SubType:=wdMergeSubTypeAccess

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 40027262
ok, this makes the connection, however it asks for the worksheet to use.  How can I change this so it doesn't ask for worksheet name

 doc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, LinkToSource:=True, AddToRecentFiles:=False
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40027276
That's what this does:
SQLStatement:="SELECT * FROM `MergeRecords`;"
0
 

Author Comment

by:mgmhicks
ID: 40027288
What I get with sqlstatement
attached is what I get with the following added

doc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, LinkToSource:=True, AddToRecentFiles:=False, SQLStatement:="SELECT * FROM `MergeRecords`;"

thanks again for the help
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40027299
I've seen that happen although I've yet to understand why.

Make sure the datasource is the only excel file in that directory.
0
 

Author Comment

by:mgmhicks
ID: 40027306
It is the only one in this folder.  Those files listed are in the root folder, not in the folder that the excel spreadsheet is in, any ideas?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:MacroShadow
ID: 40027317
Can you please upload a "sanitized" sample, I'll create one myself if you don't but that will take some time till I get around to it.
0
 

Author Comment

by:mgmhicks
ID: 40027335
ok the sampledata is what is on mergeRecords worksheet.

the other file is the sample merge file.

thanks again for the help.

I am getting it to merge and it ask me to choose the table (mergerecords) when I don't have the select statement in.  If I put select statement in, then it list spreadsheets in the root of the folder that the document and spreadsheet resides in.
sampledata.txt
Lease-Renewal-Options1---Develop.docx
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 40027469
This seems to work:
doc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, LinkToSource:=True, AddToRecentFiles:=False, SQLStatement:="SELECT * FROM `MergeRecords$`".

If it works for you then you can use the original code I posted only changing the doc.MailMerge.OpenDataSource line, so you can merge only data with x.
0
 

Author Comment

by:mgmhicks
ID: 40027775
ok, one more thing and we have it.  In your code, I removed the
With .DataSource
'            .FirstRecord = wdDefaultFirstRecord
'            .LastRecord = wdDefaultLastRecord
'        End With

and it seems to do great.  I have the original file opened as well as the new file when I leave wrdApp.visible = true

What I want to do is close original and just have the new document open.

How do I change the next lines to keep new document open in word and the original merge document gets closed.

thanks

'    doc.Close False
'    Set doc = Nothing
'    wrdApp.Quit False
'    Set wrdApp = Nothing
0
 

Author Closing Comment

by:mgmhicks
ID: 40027778
Thanks again
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40027930
Actually the original file is never opened, it serves as a template for the new document which has the merge process executed on.
0
 

Author Comment

by:mgmhicks
ID: 40027945
put when I run the code, nothing ever shows.  If I change to wrdapp.visible = true, the original document is open and I can 'X' to close it, but I want just the new document to display.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40027951
If I change to wrdapp.visible = true, the original document is open and I can 'X' to close it
It is not the old doc you see, it is the new one which is basically a copy of the original one.
0
 

Author Comment

by:mgmhicks
ID: 40027959
I see 2 documents, one with the merge fields and then a document with all the letters in it from the mergerecords table.  I can close the document with the merge fields, and still leave the document open with the merged data.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40027966
I know but it isn't the original file that's open it's a copy.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40028032
To acheive the effect you want replace
    doc.Close False
    Set doc = Nothing
    wrdApp.Quit False
    Set wrdApp = Nothing 

Open in new window


with this:

    If Documents("merged.docx").Windows(1).WindowState = wdWindowStateMaximize Then
        Documents("merged.docx").Windows(1).WindowState = wdWindowStateMinimize
        ActiveDocument.Close False
    End If

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 40028080
ok, thanks .. Greatly appreciated for all your help
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40028084
My pleasure. Learned a thing or two myself.
0
 

Author Comment

by:mgmhicks
ID: 40029728
ok, one more thing when I run this code, I end up with 2 open documents.
If I do a wrdapp.documents.count I get 2.

My question, is how to find document name, wrdapp.documents(0).name doesn't work.  Then once I have the name I want to close the document 1 the template, and just keep on document 2 the merge.docx file.

With this code both documents are now open and showing when complete.

 ' Create an instance of WINWORD.EXE
    Set wrdApp = CreateObject("Word.Application")
'    Set exclapp = CreateObject("Excel.Application")
'
'    ' set path of temporary excel spreadsheet
'
'     mydocspath = Environ$("USERPROFILE") & "\MY DOCUMENTS"
'     spathfilesheet = mydocspath & "\TempNotices2014.xls"
'     'Set mybook = exclapp.Workbooks
'    ' exclapp.Workbooks.Add
'
'    Workbooks.Add
'    ActiveWorkbook.SaveAs (spathfilesheet)
'    ActiveWorkbook.Close
    
    ' Set the path of the word merge document
    sPathFileTemplate = ThisWorkbook.Path & "/Lease Renewal Options1 - Developement.docx"
    
    ' create a new file based on the merge document
    Set doc = wrdApp.Documents.Add(sPathFileTemplate)
    
    ' Hide Word (actually just make sure it doesn't show)
    wrdApp.Visible = True

'    ' Connect the datasource to the merge document
   doc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, LinkToSource:=True, AddToRecentFiles:=False, SQLStatement:="SELECT * FROM `MergeRecords$`"
   
    ' Execute the merge
    With doc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
'        With .DataSource
'            .FirstRecord = wdDefaultFirstRecord
'            .LastRecord = wdDefaultLastRecord
'        End With
        .Execute Pause:=False
    End With

Open in new window

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now