Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data in excel to word doc (2003)

    Can somebody please supply some sample code for extracting data from excel work books and
    1. creating a word document
    2. inserting the data into the word document
    3. saving the word document

    The objective is to use VBA to create standard letters to a group of debtors where I hold the account information including debtor names, addresses etc in a standard format in an excel worksheet

    Best regards

    Alex

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: data in excel to word doc (2003)

    Although it is possible to create and fill a document entirely in VBA code, it is a lot easier to use one or more templates with the fixed text plus mail merge fields. You can then use VBA (if desired) to perform the mail merge.

    If you would like specific help, please provide detailed information.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data in excel to word doc (2003)

    Hans

    Thank you for your reply.

    I attach a copy of the standard letter to be created in which all the information to be extracted from the excel sheet and inserted into the document is highlighted in yellow.

    I have created the excel sheet so that all the relevant information is shown on individual lines.

    I need a macro to run down a column, extract the relevant information from each line and place it into the word document in the highlighted places.

    Extracting the information from excel and storing it as a variable is not a problem. I cannot however work out how to enter the data into the word document.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data in excel to word doc (2003)

    Sorry I previewed before sending which detached the attachement
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: data in excel to word doc (2003)

    Open your document.
    Activate the 'Mail Merge' task pane (Tools | Letters and Mailings | Mail Merge Wizard...)
    In step 1, 'Letters' is already selected. Click Next.
    In step 2, 'Use the current document' is already selected. Click Next.
    In step 3, 'Use an existing list' is already selected. Click Browse...
    Locate your Excel workbook with the addresses and click Open.
    If you have 'Confirm conversion at Open' ticked in the General tab of Tools | Options..., you'll be prompted for a method to connect to the data source. Click OK.
    Next, you'll be prompted to specify a worksheet, and whether the data source contains field names (column header). Click OK.
    You can then select which records yoy want; click OK.
    You're back in Word. Click Next in the Mail Merge task pane.
    In step 4, click 'More items...'
    You can now insert the fields from the Excel workbook into your document.
    When you're done, click Next.
    In step 5, you can see how the merged letters will look. Use the << and >> buttons to move from record to record.
    Click Next.
    In step 6, you can perform the actual merge; either directly to the printer or to a new document ('Edit individual letters')

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data in excel to word doc (2003)

    Hans

    Thanks. That sounds ideal. I will give it a whirl and let you know how I get on.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •