Solved

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

Posted on 2014-10-14
8
242 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
 

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
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.

 

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 31

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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