Solved

Need help creating custom data extraction from Excel to use for mail merge

Posted on 2014-10-14
8
249 Views
Last Modified: 2014-10-21
Please see attached Spreadsheet sample.   There are three worksheets which are "Master List",  "Enter Date Range", and "Data for Mail Merge"

What I'm trying to accomplish is to extract data from the Master List worksheet using a date range (based on column D of the Master List) and then have that data populate the worksheet named "Data for Mail Merge".    I have a separate worksheet where I want to put a start date and end date.     Basically after I enter a start and end date, I want data from the Master List to populate into the worksheet named Data for Mail Merge.

The reason for this spreadsheet is to use it with a mail merge using MS Word based on the information in the "Data for Mail Merge" worksheet.

Any suggestions on how to make something like this work?    I want to keep this as simple as possible and understand this idea might not even be the best way to accomplish this.  I am open to suggestions.
Spreadsheet-Example.xlsx
0
Comment
Question by:anctech
  • 4
  • 3
8 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40380765
Hello,

I created VBA procedure which will take entered dates and based on those dates it will copy data to last sheet. Since the code uses exact names of the sheets you need to preserve them.

 Attached is the file and screenshot of the filter sheet, you just enter dates and click Extract button and the sheet with extracted rows will automatically show.
Capture-1.PNG
Spreadsheet-Example.xlsm
0
 

Author Comment

by:anctech
ID: 40380901
Thank you very much for your quick reply, the VBA code works great with extracting data to the separate worksheet, however it's not working as a data source for a mail merge.    I searched online and found that word can not use .xlsm files for mail merge.   Is there a way without using custom VBA code to make this extraction work?   I need the data source to be a standard .xlsx excel file.
0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 500 total points
ID: 40380906
Hello,

since
Word can not use .xlsm files as a data source, but it can use .xlsb (Excel binary format) files as a data source. .xlsb files are smaller, faster, and can contain macros, so that is the format I would recommend you use.

I attached the xlsb format ... Try with this
Spreadsheet-Example.xlsb
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:anctech
ID: 40381235
Thank you.  I actually found that same article after seeing that the .xlm format wasn't compatible with a mail merge. I had already resaved the file in the .xlsb format and gave that a try.  The mail merge with word was able to see the data this time, however for some reason the merge pulls is showing a record for all entries in the master and not just the filtered data in the worksheet that gets populated with the VB code.  What I mean by that is the merge is showing the filtered data but is also showing many blanks records.  

 For example, there are a total of 47 records in the master sheet.  I selected a data range so only records with a date of 10/03/2014 would populate the  "data for mail merge" worksheet.  However when I do the mail merge and word is linked to the "data for mail merge" worksheet,  it is showing the records that have the 10/03/2014 date however the merge is also pulling blank lines  (See screen shot attachments)
Options-when-choosing-linked-data-source
0
 

Author Comment

by:anctech
ID: 40381240
Screen shots regarding my previous comment.
Pulling-blank-records-also.JPG
Options-when-choosing-linked-data-source
0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 500 total points
ID: 40381445
Hello,

there is option filter when importing data so you can setup to ignore the blanks like in the attached screenshot...

Try and see if it helps..
Capture4.PNG
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40384344
Taking the suggestion from samo4fun a little further, you can use the Filter Option on the Master List and filter for the required dates rather than filtering to a separate sheet first.

Thanks
Rob H
0
 

Author Closing Comment

by:anctech
ID: 40394383
Thank you for your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

839 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