locked
Export certain data from Excel to Word to generate proposals RRS feed

  • Question

  • Hello all, 

    I need some help with my task here. 

    I need to generate proposals for customers. In the proposal, there would be a summary of the cost that the customers need to bare when purchasing our products. 
    So before i can create the summary, i need to do some calculations in Excel. The specific details of the product with its desciptions would also be in the Excel sheets. 

    What i did was i made a template of the proposal first and saved it lets say in "D:\Proposal.dotx". Then I created an Excel workbook to calculate the cost of product that the customers require and saved it as ""D:\Workbook.xlsx".

    In the workbook, i copied certain cells and used "Paste Special" into certain parts of the Proposal.dotx. This is to link the data between these two files. It worked okay. BUT, there are problems...

    [1]  Sometimes, some customers need more products than what the others need. So when this happens, the range of selected cells had to be changed, so does the spacing/format/margin in Proposal.dotx. This is troublesome because i wont be the only 1 using this template, others that dont know how to manipulate the "Paste Special" would use it to. 

    [2]  When i tried to move these files (Proposal.dotx and workbook.xlsx) to another place, the link doesn't work. There wont be anything pasted into Proposal.dotx when workbook.xlsx is update.

    Can anyone help me? Thank you in advance.
    Wednesday, November 3, 2010 6:41 AM

Answers

  • While you could keep the list of your products and their pricing in Excel, I would create the proposal in Word, using a template containing a userform that contained a listbox that was populated with the data from Excel.  You would then one at a time, you would select the items from the list that you wanted to include in the proposal, enter the number of each item and click on a button that would add it to another list box.  There would be other buttons to allow you to delete and item from the second list box or change the quantity.  You could also have controls on the userform into which you entered information about the user.  Then when everything was ready, you would click on a button and then all of the information would be transferred into the Word document.

    See the following pages of Greg Maxey's website :

    http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

    http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

    You might also want to see the last of the following series of articles:

    Please Fill Out This Form
    Part 1: Create professional looking forms in Word
    http://www.computorcompanion.com/LPMArticle.asp?ID=22

    Part 2: Adding Automation to your Word forms.
    http://www.computorcompanion.com/LPMArticle.asp?ID=46

    Part 3: Learn more VBA (macros) to automate your forms.
    http://www.computorcompanion.com/LPMArticle.asp?ID=119

    Part 4: Use custom dialog boxes in your Word forms
    http://www.computorcompanion.com/LPMArticle.asp?ID=127

    Part 5: Connect your AutoForm to a database to save input time and keep
    better records!
    http://www.computorcompanion.com/LPMArticle.asp?ID=136


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "xtrasher_kingx" wrote in message news:8a8e5a4f-2067-4d4b-b980-c91cab635ae3@communitybridge.codeplex.com...

       Hello all,

    I need some help with my task here.

    I need to generate proposals for customers. In the proposal, there would be a summary of the cost that the customers need to bare when purchasing our products.
    So before i can create the summary, i need to do some calculations in Excel. The specific details of the product with its desciptions would also be in the Excel sheets.

    What i did was i made a template of the proposal first and saved it lets say in "D:\Proposal.dotx". Then I created an Excel workbook to calculate the cost of product that the customers require and saved it as ""D:\Workbook.xlsx".

    In the workbook, i copied certain cells and used "Paste Special" into certain parts of the Proposal.dotx. This is to link the data between these two files. It worked okay. BUT, there are problems...

    [1]  Sometimes, some customers need more products than what the others need. So when this happens, the range of selected cells had to be changed, so does the spacing/format/margin in Proposal.dotx. This is troublesome because i wont be the only 1 using this template, others that dont know how to manipulate the "Paste Special" would use it to.

    [2]  When i tried to move these files (Proposal.dotx and workbook.xlsx) to another place, the link doesn't work. There wont be anything pasted into Proposal.dotx when workbook.xlsx is update.

    Can anyone help me? Thank you in advance.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Bessie Zhao Wednesday, November 10, 2010 9:20 AM
    • Unmarked as answer by xtrasher_kingx Wednesday, November 10, 2010 9:32 AM
    • Marked as answer by xtrasher_kingx Wednesday, November 10, 2010 9:33 AM
    Wednesday, November 3, 2010 6:55 AM

All replies

  • While you could keep the list of your products and their pricing in Excel, I would create the proposal in Word, using a template containing a userform that contained a listbox that was populated with the data from Excel.  You would then one at a time, you would select the items from the list that you wanted to include in the proposal, enter the number of each item and click on a button that would add it to another list box.  There would be other buttons to allow you to delete and item from the second list box or change the quantity.  You could also have controls on the userform into which you entered information about the user.  Then when everything was ready, you would click on a button and then all of the information would be transferred into the Word document.

    See the following pages of Greg Maxey's website :

    http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

    http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

    You might also want to see the last of the following series of articles:

    Please Fill Out This Form
    Part 1: Create professional looking forms in Word
    http://www.computorcompanion.com/LPMArticle.asp?ID=22

    Part 2: Adding Automation to your Word forms.
    http://www.computorcompanion.com/LPMArticle.asp?ID=46

    Part 3: Learn more VBA (macros) to automate your forms.
    http://www.computorcompanion.com/LPMArticle.asp?ID=119

    Part 4: Use custom dialog boxes in your Word forms
    http://www.computorcompanion.com/LPMArticle.asp?ID=127

    Part 5: Connect your AutoForm to a database to save input time and keep
    better records!
    http://www.computorcompanion.com/LPMArticle.asp?ID=136


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "xtrasher_kingx" wrote in message news:8a8e5a4f-2067-4d4b-b980-c91cab635ae3@communitybridge.codeplex.com...

       Hello all,

    I need some help with my task here.

    I need to generate proposals for customers. In the proposal, there would be a summary of the cost that the customers need to bare when purchasing our products.
    So before i can create the summary, i need to do some calculations in Excel. The specific details of the product with its desciptions would also be in the Excel sheets.

    What i did was i made a template of the proposal first and saved it lets say in "D:\Proposal.dotx". Then I created an Excel workbook to calculate the cost of product that the customers require and saved it as ""D:\Workbook.xlsx".

    In the workbook, i copied certain cells and used "Paste Special" into certain parts of the Proposal.dotx. This is to link the data between these two files. It worked okay. BUT, there are problems...

    [1]  Sometimes, some customers need more products than what the others need. So when this happens, the range of selected cells had to be changed, so does the spacing/format/margin in Proposal.dotx. This is troublesome because i wont be the only 1 using this template, others that dont know how to manipulate the "Paste Special" would use it to.

    [2]  When i tried to move these files (Proposal.dotx and workbook.xlsx) to another place, the link doesn't work. There wont be anything pasted into Proposal.dotx when workbook.xlsx is update.

    Can anyone help me? Thank you in advance.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Bessie Zhao Wednesday, November 10, 2010 9:20 AM
    • Unmarked as answer by xtrasher_kingx Wednesday, November 10, 2010 9:32 AM
    • Marked as answer by xtrasher_kingx Wednesday, November 10, 2010 9:33 AM
    Wednesday, November 3, 2010 6:55 AM
  • Thank you very much for the prompt reply, i'll study on your answer and try it afterwards!
    Wednesday, November 3, 2010 6:58 AM