Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Create Multiple Word Docs from Excel Data

    Good Morning/Afternoon/Night,

    I'm looking to create a macro that will take data from Excel, copy it into a Word document (I have a template already made up), save the word document (File name is obtained from the concatenation of two cells which receive the data in an Excel file) and then convert this document into *.pdf format. Because there will be more files that have identical first part of the name, I put each group in a folder. Folder name to be the same as the first part of the file name containing it. Finally, I wish to archive each folder with a *.rar file archiver. I have multiple lines of data so I will need to create multiple *.rar files.

    Here is an example of what I'd like the macro to do:

    0. Create a folder that has the name „Operation”.
    1. Open the Word template I have created.
    2. Copy the Data from excel to word: Cell B2 in Excel will have a name, I need the name copied from excel and pasted into word about 10 spaces down after the text that says "Name". C2 data will have a employer, I need the employer copied from excel and pasted into word about 12 spaces down after the text that says "Employer". Etc...
    3. After all the data has been populated, I would like the document to be saved as a combination of two of the fields in that were populated in Word (Employer_Name). This file will be saved in the folder „Operation”.
    4. The Word document will then be closed.
    5. Word document will be converted to PDF.
    6. Create a folder that has the name Employer_Name.
    6. Steps 1 through 6 are repeated with the next line of excel data.
    7. In the folder "Operation", select each group of files with the same name at the beginning and move to the appropriate folder.
    8. Archive each folder with a *.rar file archiver.

    I know this is pretty detailed but if someone could help me I'd greatly appreciate it!

    Thanks in advance!

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Your order of operations needs refinement. The steps should be:

    1. Setup a loop to iterate through the rows in an excel sheet
    2. Check if a folder exists as derived from the excel row - if not create it
    3. Create a new word document based on a template
    4. Import information into the Word document from cells in the current excel row
    5. Save the document to the named folder
    6. Save the document as a PDF to the same folder
    7. Close the word document
    8. Loop to the next row in Excel
    9. Archive the folders

    I don't know what purpose your Step 7 fulfills but it shouldn't be necessary if you just put the files into the right folder in the first place.

    You will be able to find example code for all these steps so rather than getting someone to do all your work for you, perhaps you should attempt to get this underway yourself and we can help with finetuning it.

    Your RAR file archiving software will need to be scriptable and the code will need to be specific to that software so more information will be required before anyone can help you with that.

    To help you get started on step 1, see http://www.mrexcel.com/articles/excel-to-word-macro.php
    Last edited by Andrew Lockton; 2016-07-31 at 21:38.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    doru.ditu.3 (2016-08-01)

  4. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Andrew,
    Thank you for the advice. I asked for help because I am new to VBA.
    Word document template is not empty, but has written words. Therefore I said that it is necessary to look for the code word from template then insert information taken from the excel document. I did not know this about RAR archiver software. I think you can write VBA code using Windows commands.
    If data from the excel file are of different lengths, ask how to do, because the space for writing in the word document template is limited.

    I have little time to solve this challenge. I do not know if I can solve this unless you help me.
    Thank you in advance!
    Last edited by doru.ditu.3; 2016-08-01 at 01:59.

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Its OK to ask for help here but we don't get paid for providing that help and generally only offer assistance to help others learn or get past sticking points. That said, someone may have time on their hands and feel pity for you and do the necessary coding on your behalf.

    You will need to provide sample documents of both Excel and Word if you want a complete solution. The Word template will need placeholders added to provide locations for the Excel data to be inserted by the code. These placeholders could be bookmarks, fields or Content Controls and the code needs to work with whatever method is chosen.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    doru.ditu.3 (2016-08-06)

  7. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts
    I tried to solve this problem through self-study, but I was stuck. Successive error messages. Mail merge in Word is not an option for me because I want independent files for each person. I would like you to help me solve this problem.
    I attach a set of data from the excel file, template, file a result of working with mail merge in Word, and the error message.
    I did mention that the required setting:
    VBA> Tools> References> Microsoft Word 14.0 Object Library> OK
    toWord.xlsmTemplate.docCERTIFICATE.docError.jpg
    Last edited by doru.ditu.3; 2016-08-06 at 08:58.

  8. #6
    Star Lounger Graham Mayor's Avatar
    Join Date
    Mar 2016
    Location
    Cyprus
    Posts
    68
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Mail merge would make the best sense - see http://www.gmayor.com/individual_merge_letters.htm or http://www.gmayor.com/MergeAndSplit.htm

    As for your macro, it would work better if you began by adding End Sub at the end of the code and you need to declare what 'nameFisier', 'folder' and ' templ' refer to. I suspect nameFisier is a typo for nameFile, but the macro won't know that, and in any case folder is not known to the macro so will error out.

    Instead of using
    objWord.Documents.Open templ
    use
    objWord.Documents.Add templ

    having first provided the full file path for 'templ'

    That should get you started.
    Graham Mayor - Word MVP
    http://www.gmayor.com

  9. The Following User Says Thank You to Graham Mayor For This Useful Post:

    doru.ditu.3 (2016-08-12)

  10. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The Mailmerge Tips and Tricks 'Sticky' thread at the top of the Word forum (http://windowssecrets.com/forums/sho...ips-amp-Tricks) likewise has a macro for sending mailmerge output to individual files. See the Send Mailmerge Output to Individual Files topic.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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