Export Data from Excel to Word Document and Save Word as PDF

Automating data from Excel to Word bookmarks is relatively easy.  The challenge is the logic associated with the line items.  I'm not sure I'm ready to take this on, but these questions should be asked by anyone wanting to assist you, so let me start with that:

1.  Exactly how are you copy/pasting the line items and total data?  It seems to me its not a straight copy/paste - there's some editing you do as well, correct?
2.  What is the minimum/maximum line items that might be copy/pasted?

The way I'm thinking about it, I would create a word bookmark for every Excel field that would be copy/pasted over.  As a result, from a template standpoint, I would create bookmark lines for the total number of possible line items that might exist.

As a result, a macro could be written to copy/paste bookmarked item by bookmarked item, from Excel fields to the Word document.  If a bookmarked item were blank, it SHOULD remove the bookmarked item in Word.  As a result the Word document could have a 1 to many lineitem list.

Here are a couple tips about using bookmarks and populating Word templates from Excel.  The first is just copy/paste from Excel to existing bookmarks.  http:/Q_27113853.html.  The second is a tip on how to generate the bookmarks in an existing Word document http:/Q_Q_27114358.html, in a semi-automated way, to save the user from some of the steps in creating the bookmarks.

Your thoughts/input?

If this makes sense, can you revise your word template, inserting bookmarks for each of the copy/paste fields, including a set of line item/total bookmarks?  Alternatively, using generic BM1, etc., bookmarks re: reviewing the second tip (above) approach?

Dave

I haven't done much here, but enough to demonstrate what could be done - waiting on your input.

See the modified spreadsheet - note that there are key words that will be used in the automated copy/paste to the Word template.  Note how EVERY item that would get copy paste has a term to the immediate left.  Also, note in the Word document, I've bracketed where those terms go.  The terms in both documents MUST BE exactly the same.

The application then would open the word template, and review both the template and spreadsheet, matching up all the terms.  If there was a mismatch, you should get prompted, however if an item was empty in the Excel worksheet, the term could be deleted from the Word document.

hence, if you had Line Item 10 with no data, the bracketed line item 10 data would be removed from the Word document.

If all this makes sense, from the tips I posted, I should be able to develop the solution in a fairly straight-forward manner.

Let me know.

See attached,

Dave
Customer-Quote.xlsm
Quote.docx

dont forget there are several free pdf writers that will install and then function as a printer - so you could bypass word and just print to pdf direct from excel.

Hi Dave,

This is exactly what I was looking for. I was just not sure how to do this due to the task being my first time at touching word with VB. I note the work you have done on the other posts with Lance are great examples.

Answers to your questions:
1.  Exactly how are you copy/pasting the line items and total data?  It seems to me its not a straight copy/paste - there's some editing you do as well, correct?  Correct, I am copying and pasting the data into word from excel and format it by hand as required. I also key some of it manually, like the date, totals and stuff.
2.  What is the minimum/maximum line items that might be copy/pasted? Some are 1, others go to like 10. Best to allow for unlimited if possible?

I've reviewed the sample files you modified and agree with the approach you have suggested in your second post. If you could provide the solution based on that, that would be great. Let me know if you need anything else from me to help.

Shaun

With Office 2007 MSFT has an add-on for generating PDF from Office apps.  Here's a tip on that:  http:/Q_27422213.html

The opportunity is to leverage Word formatting in the process.  Please confirm, otherwise, it becomes more simple out of Excel, as wolfcamel has articulated.

Dave

Please note, my office 2007 version allows Save (Publish) as PDF as I already have the extension installed. I do not want to install 3rd party PDF writers.

>>I've reviewed the sample files you modified and agree with the approach you have suggested in your second post. If you could provide the solution based on that, that would be great. Let me know if you need anything else from me to help.

I'm working on that solution, now.

The Line items can be unlimited, but you'll need to manage that - instructions on how once I get this structure to work as its in my head at this moment, lol.  I'm working now on creating a more generic routine (than having [[BM]] everywhere for initial bookmark designation) to generate bookmarks that will look for [[STRING]] and define a bookmark in Word for that.  Once I have that, the rest should come together quickly.  I think its more elegant this way, and makes the template easier to read, as you can then change the design to anything and keep using the utility and Excel module which will also be as flexible.

If it comes together nicely, would be good fodder for an article :)  Helping you and getting an article out of it that helps other is the gold I'm after :)

Dave

SaveAsPDF will be straightforward, then - see the code for it in the link I published http:/Q_27422213.html
 if interested ahead of my solution :)

Dave, this is a question that you worked on for me before. Its exactly what I was thinking of doing. So, using the source code already inside my excel file (noting that the excel file in that post https://www.experts-exchange.com/questions/27422213/Powerpoint-to-PDF-from-Excel-VBA.html is the same file for this question.), I would just add this new code to the same file and kill both tasks from the same routine. Your always a step ahead of everyone!! Do you agree with my intentions?

Ah - I didn't check the original OP author - I agree if your intent is to print to PDF following the same methodology, yes.

PS - the starting doc will be a Word Template - .dotx - which can either be saved as a .doc, or printed and then destroyed.  In that way the template should have less chance of getting corrupted or inadvertently saved while updating bookmarks from Excel, etc...

Unless there's anything else, I've got my nose down working on this one ;)

creativefusion - are you around?

Yes Dave. Whats up?

Ok.  I've just about had all the fun I can have tonite.  The app I have meets your original question, but not our desire - to deal with "unlimited" line items.  We need to modify the template such that your table has multiple rows (instead of 1) where each line item goes on a separate row.  THEN, I can examine the table and when all the cells in a row are empty, I can delete that row.  That's my hypothesis in how to deal with this.  Currently, by replacing the bookmark with blank text, it doesn't delete the line where the bookmark resides, thus you have a gap between the last lineitem with data and the rest of the doc.

I just need some time to figure that out - after I get back to it.  Need a break, family, etc., so might be sometime later, later tomorrow.

In the meantime, you can modify the Word template to have as many lineItems as you need, and the app will work.

Here's the test app. I'm not publishing code or a lot of documentation as yet, as we're testing, and I'll do that for the "final" solution.  Suffice it to say, there's two documents - the workbook with macros and a data entry tab with buttons, and there's a Word template.  Rules for using the template are basically this:

1.  Fields in the template that are for replacement (re: copy/paste) should be noted [[NAME]].  Valid names include alphanumeric and underscore.  No spaces.  Also, no duplicates. So, if a field like [[Contact_Name]] is used more than once in the Word document, just increment a number after the field - e.g., [[Contact_Name2]]

2.  in the workbook, the sheet should have all the names that are in the Word document template, or you'll get prompted with an error.  Just a cell with the name in it (WITHOUT the [[ ]] brackets) will suffice.  TO THE IMMEDIATE right of the field should be the data that will get ported to Word.  Ensure duplicate BookMark names (with increment) are on the page as well - examples are on the workbook.

3.  Save the files to a local trusted folder for running macros.  Load up the workbook and then you can run the button to MAKE BOOKMARKS with TEMPLATE - it takes the template file, searches and makes bookmarks, saving the template as a filename_BM.dotx file.  The original Word file should also be a template - dotx file.  To edit a .dotx file, go to Explorer, right-click on the file, and select open.  If you double-click it will create a document off the template, not edit the template.

4.  You should get this far automatically, but in future you'll be modifying the template for other uses.  Note the template filename is also in the Excel Workbook under named range "wordDoc"

5.  Make changes to the Excel data entry, then hit the button "CreateQuote and Save as PDF"

Review and feedback, please.  I know there's a gap for the lineitems that aren't being used.  in the interim, just go to the original template and edit it (right click then open) and delete those lineitem [[LineItem4]] [[Qty4]] etc. through to the last (10th) one, to the extent you don't need them and save.  You can use the tool at that point with 3 line items, if you deleted all the rest.  Just MAKE BOOKMARKS then get on with creating quotes and saving pdf's.

Until tomorrow.

See attached,

Dave
Customer-Quote-r1.xlsm
Quote.dotx

@creativefusion - when you get the chance, if you can modify the template such that each line item is on its own individual row, that would be helpful.  If you can do it with the template I submitted, that would be even MORE helpful, saving me time to work on the app, rather than formatting a template.  The idea is that if a row in the table is blank, that row would get deleted.  Since we can turn border formatting off, to the viewer, you'd not see that they were in their own rows.

Make sense?

Please let me know how your test of this goes, and if you can help out with the template.

I'm hitting the hey.

Dave

Thanks Dave. I'll give it a whirl and provide feedback for you in the morning. Thanks so much for your help.
Shaun

What is your timezone - what time is it for you right now?

I figured it out - my proposed approach to "clean up" for lineitems not needed WILL work - just need to format that table a bit, first.

Dave

PS - I'm also handling the #.## and $ #,###.## formatting of the values, so no worries - that format will carry over in the next version.

Later,

Dave

Hi Dave,

Its 9:48pm here in Sydney, Australia. Im working on testing and modification to the template right now. Will post up tomorrow morning when you are rested.

Thanks again.
Shaun

ok - here's the template where I've already started.  I'm really hitting the hay, now...

Just finish it thru line item 10 and get rid of the borders in between so the table doesn't look like it has these rows and I think we're good.  I'll test with my just now created code and turn it around when I get to it tomorrow.

Cheers,

Dave
Quote-r1.dotx

Morning Dave,

Okay, all has been tested and is working fine on my pc. I have made the adjustments to the template as you requested and all should be ready for you to continue with.

Thanks so much for the help. I will learn a lot from this.

Good day,
Shaun
Quote-r1.dotx

Hi Dave,

Your'e a magician! This is so cool and far exceeds my expectations with the multi-line processing.
The solution you presented here is another great example of how one can achieve great things with VB and a tame logical mindset.

I've loaded the app on my PC and got a library reference error but that was because you used Word 14.0 for development but I am using Word 12.0. It might be worth noting that in the documentation when you publish it to a wider audience. Aside from that, it works a darn treat and will save me a huge amount of time from this point forward.

I'll let you know if it there are any further problems. Thanks so much once again!!

PS: Good ratings for you this week. Cannot wait to see you climb to #1. You deserve it!!

Regards,
Shaun

Outstanding piece of work here guys and gals!!

Thanks for your kind words.  

Good catch on the reference library.  When I publish I'll use late binding with comments on early binding, so no references will be needed at all.

E.g.,

Dim myDict as Object 'early binding , use as Scripting.Dictionary for late binding
dim oWA as Object 'early binding, use as Word.Application for late binding

Cheers,

Dave

Article published: https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_8933-How-to-quickly-and-accurately-populate-Word-documents-with-Excel-data-charts-and-images-including-Automated-Bookmark-generation.html?sfQueryTermInfo=1+30+dlmill

Vote YES if you find it helpful :)

Cheers,

Dave

Hi Dave,

Seasons Greetings and Happy New Year to you and your family as well. We just got back from vacation and I saw your post on EE.

Wow, you really have done a great job with this article and I am sure it will be of use to many users in future. It is much more than I thought you were going to do.

Thanks for the acknowledgement and I wish you all the very best for 2012.

Warm regards,
Shaun

Thanks for your kinds words.  Please vote YES if you found it helpful :)

Thanks!

Dave

Dave... this is awesome! would you know how to automatically pick the PDF output and put it on an email as an attachment with a predefined subject and content? also if I wanted to create lets say 2 different letter but using different date from the workbook.. can I just duplicate the sheet into another?