Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Choosing only data that is available - Excel

Posted on 2006-10-29
3
Medium Priority
?
204 Views
Last Modified: 2010-04-30
Hello

I am working on a ordering process and trying to automate it.  I will give you an example of what I am trying to do below.

When I receive orders for some of my products I place the order in an Excel spreadsheet (address, order #, qty of each product).  For each order, it is placed in one row, so if I have an order going to John Smith and he orders 3 out of the 20 products that I have to offer these are all inserted in the same row.  From there I run a mail merge in Word to generate a packing list/invoice.  What I am looking to do is make it so only the products that are ordered show up on the invoice without specifying a specific place on the Word template for each product.  

Is there a Macro that I could use to make this happen?

Please let me know if you need more info.

Best Regards
wdelaney
0
Comment
Question by:wdelaney05
[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
3 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17831153
Hi wdelaney05,

I would recommend you rethink the way this works.  From your description, it shounds like each product
occupies a different column.  From a data modeling perspective, this is a bad choice.

In my opinion, you will be far better off in the long run by using a database app instead.  Access actually
comes delivered with a simple order entry template that I suspect will handle your needs with just a little
tweaking.

Regards,

Patrick
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 1500 total points
ID: 17832667
However, Word mailmerge is not designed to work with the one-to-many query that would be necessary to join the customer details to the order details, so you would have to use Access reporting. There's nothing wrong with that in the long run, but you would have to familiarise yourself with quite a lot.

Meanwhile...

Presumably you have a very limited number of products.

You could make each item only show if the quantity is greater than 0. This requires your MAILMERGE fields to be nested inside IF fields. It would be best to show the field codes (Alt+F9 toggles on and off). Do not type the {} pairs in, either enter them with Ctrl+F9 or as part of a field insertion.

{ IF { MAILMERGE "qtyA" } > 0 { MAILMERGE "ProductA" } "" }                 { IF { MAILMERGE "qtyA" } > 0 { MAILMERGE "qtyA"} "" }
{ IF { MAILMERGE "qtyB" } > 0 { MAILMERGE "ProductB" } "" }                 { IF { MAILMERGE "qtyB" } > 0 { MAILMERGE "qtyB"} "" }  

In Word 2000, you get an option to supress any blank lines that might result. In Word 2003 it's done without asking.

 
0
 

Author Comment

by:wdelaney05
ID: 17838154
Thanks you two for the answers.  I agree with you on the Access database.  That is something I need to do some more research on to utilize.  

Best Regards,
wdelaney05
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

610 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