Solved

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

Posted on 2014-10-14
8
245 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
macro for finding text and replacing 7 43
Excel case statements 3 24
How do I crate a Pivot table in Excel 2 0
Vlookup formula error 15 0
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,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 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

17 Experts available now in Live!

Get 1:1 Help Now