Solved

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

Posted on 2014-10-14
8
248 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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 32

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Outlook Free & Paid Tools
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

786 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