?
Solved

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

Posted on 2014-10-14
8
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 2000 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 2000 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

771 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