Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-03-2012, 09:25 AM
iansan5653 iansan5653 is offline Creating Invoices from CSV Windows XP Creating Invoices from CSV Office 2003
Novice
Creating Invoices from CSV
 
Join Date: Feb 2012
Posts: 3
iansan5653 is on a distinguished road
Unhappy Creating Invoices from CSV

I need help creating invoices from a Very Jane CSV order report. I would just use mail merge, and format it, but the problem is that the person and their address is one one line, then each product they ordered is one the next line, and then another person, and so on. Word just treats each line as a person.



Example: (I can't show private data)


The real CSV is formatted exactly the same way. And yes, some of the fields don't have names because the customer didn't enter theirs.

All I want to do is make an invoice for each one with what they ordered, their address/email, and my address.
Reply With Quote
  #2  
Old 02-06-2012, 07:26 PM
macropod's Avatar
macropod macropod is offline Creating Invoices from CSV Windows 7 64bit Creating Invoices from CSV Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi iansan,

Before that data can be used for a mailmerge, the data for each record must all be on one line. At present, you have two (or more) lines per record. Where there are more than two lines per record, you need to have one line per item, with all of the customer information repeated on that line.

As for having one invoice per person, you can do that using a Directory/Catalog merge, but there is no way Word can tell you which customer an item should be invoiced to if the details aren't in the data.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-07-2012, 10:29 AM
iansan5653 iansan5653 is offline Creating Invoices from CSV Windows XP Creating Invoices from CSV Office 2003
Novice
Creating Invoices from CSV
 
Join Date: Feb 2012
Posts: 3
iansan5653 is on a distinguished road
Default

OK, so to do this I would have to take each product and copy and paste it on to the same line as the customer, with new columns for each product. Not easy for 116 products, but it could be done.

Maybe there is a better way than mail merge?

Thanks
Reply With Quote
  #4  
Old 02-07-2012, 02:21 PM
macropod's Avatar
macropod macropod is offline Creating Invoices from CSV Windows 7 64bit Creating Invoices from CSV Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi iansan,

You could use a macro to re-arrange the data for a mailmerge. Alternatively, you could use an Excel macro to open a Word document and populate it with the data. Whichever way you go, though, you need a reliable method of identifying which records relate to a given client. I doubt that can be done where the client details are missing - unless there's another field in the data to identify the client by an ID code. Then there's the issue of whether the data are sorted by client. A mailmerge can handle this last issue, but you'd have to program it yourself if you were to automate Word from Excel.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-07-2012, 03:53 PM
iansan5653 iansan5653 is offline Creating Invoices from CSV Windows XP Creating Invoices from CSV Office 2003
Novice
Creating Invoices from CSV
 
Join Date: Feb 2012
Posts: 3
iansan5653 is on a distinguished road
Default Explain Please

Could you give me a more detailed explanation? I have never done macros before. If not, that's ok.

Thanks, Ian
Reply With Quote
  #6  
Old 02-07-2012, 05:05 PM
macropod's Avatar
macropod macropod is offline Creating Invoices from CSV Windows 7 64bit Creating Invoices from CSV Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Ian,
Quote:
Originally Posted by iansan5653 View Post
Could you give me a more detailed explanation? I have never done macros before.
Basically, a macro would have to be coded to look at each row to see whether it has a value in your 7th column. If so (let's assume row 1), it would then look at the next row (row 2) to see if that has a value in your 6th column and, if so, cut columns 2-6 from that row and paste them into, say, columns 8-12 on the current row (1). The code would then look at the next row down (3) to see if that has a value in your 6th column. If so, columns 1-7 from row 1 would have to be copied to row 2 and columns 2-6 from row 3 cut & pasted to columns 8-12 on row 2. Row 2 would then become the 'current' row and the process would repeat until another row is found with a value in your 7th column. At that point the whole process would start over. After all this processing to get the data onto a single row per client & category is done, any remaining rows would be deleted.

As you can see, this is quite an involved process.

If you're going to drive the letter generation from Excel, the macro would also need to sort the data by client, start a Word session, export the data for the first client to it, save a copy, then process the next client untill all are done.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
csv, excel 2003, invoices

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating 3D PPT 123music PowerPoint 0 01-03-2012 06:59 AM
Creating Invoices from CSV Creating a contract todd.denoyer Word 3 05-05-2011 04:53 PM
creating macro steveb Word VBA 0 08-14-2010 01:29 AM
Creating Certificate PaulAtrib Office 1 06-19-2009 03:27 AM
Creating new project Using PDS myanilkumar Project 0 01-05-2006 12:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:39 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft