Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Word with Excel (Word/Excel 2003)

    Just wondering what is the easiest procedure to create labels in Word by using data in Excel cells to create the appropriate label fields. I'm thinking of using Mail Merge of course but how can one specify which Excel file and cells to use. Thanks much in advance for any info.
    BW

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Linking Word with Excel (Word/Excel 2003)

    BW,

    During the merge process, you are asked to select a list in Step 3 assuming you're using the Task Pane to guide you thru the steps in Word 2003. This is where you specify the file which contains the info to be merged.

    The file itself has to be arranged like a DB. Using Excel, your 1st row of some sheet should have column headings for the field names and the next rows should contain your data (records). When you specify the file, Word will pick up the column headings from the file. In step 4, you will place the fields in the Word main document so that Word knows where the info goes during the Merge process (steps 5 and 6).

    As to what I think was your last question on how to specify which cells to use. This is part of step 3 where you also specified the file. Since you've specified an Excel file, the merge process in this step will also present you, once you've id'd the Excel file, with a list of things it thinks could be your DB. At least for me, this consisted of sheet names (Sheet1, Sheet2, XYZ if you've renamed a sheet) in quotes as well as all named ranges. So what I did is give a range name to my DB within Excel (something like grade_DB) so I can see it when I'm prompted in Word to "select a table" (this is the name of the dialog where Word gives you the list of sheets and range names).

    I wasn't sure if you've already decided to use Excel for your DB (you asked for the easiest procedure. If not, just note that your data base can also be stored in a Word table or an Access DB. If using the latter, you'll be prompted when going thru Step 3 to select an Access table or query. I haven't used Access for my DB in a long time, so I'm not exactly sure what the Word prompts will look like.

    HTH

    Fred

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Word with Excel (Word/Excel 2003)

    Hi Fred:
    Thanks for your reply and time. I guess I should have been more specific in one area i.e what if the data you want to create the labels from is scattered about the sheet? For example, cell A10 has "Part Number" as a heading and B10 has the actual number. Then cell A24 has the "Part Name" and B24 has the actual name. Without actually changing the sheet, how do you think one would go about using Step 3 of mail merge to "gather" all the scattered data including associated headings so that proper labels could be created.
    Bob (BW)

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Linking Word with Excel (Word/Excel 2003)

    Bob,

    I don't know if anyone else will have any thoughts but I don't have any easy thoughts.

    I have a DB that I use in a mail merge to provide students with a "status" report after each test. This report says what they need to pass the class. You may have noticed that I gave an example of grade_DB. This is a named ranged on a sheet with a similar name. What this DB/named range consists of is:
    - row 1: labels for the fields that the Mail-Merge process will pull into word (eg, Student_Name, Test1_score, Present_days, lab_score)
    - rows 2 thru n are one record per student

    I don't recall how many columns I have in the grade_DB range - it's something like 12. The rows are n+1 (students+1 for header row).

    I designed this sheet specifically because I wanted to do a mail merge to create these status reports. The info is scattered around the workbook on different sheets: there's one sheet where I keep attendance and pull the information into the grade_DB for each student (all it is is something like ='Attendance'!B5 or whatever the correct syntax is for an off-sheet reference). There's another sheet for lab scores and I do the same thing to get the info into the grade_DB range. I think my workbook has 3 or 4 sheets that make it easy for me to enter info (not too much info per sheet so that I have to do a lot of horizontal scrolling) and the necessary info gets pulled into the one sheet where grade_DB is defined.

    If your info is not so "orderly", it will be harder.

    HTH

    Fred

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Word with Excel (Word/Excel 2003)

    Fred:
    Thanks very much for your very time consuming and detailed description.
    I guess what I'm looking for is the syntax you spoke of. And that should lead us to believe that, if we were able to use the correct sytax somewhere in Word's Mail Merge steps, we should be able to "grab" any info from any cell that has a label and just import that info into Word labels. I, like you, am teaching at a College near Toronto, Canada and this student approached me with this question. His dilemma is that he takes care of coding for car parts and needs to apply a printed "Avery" label to these parts to identify their number, name etc. However, the spreadsheethe created and showed me has the label items scattered throughout the sheet and I wondered how we could get them together as fields in a label without having to get into VB coding etc.
    I see you have some syntax in your message so I might "fiddle" with that a bit to see if it works.
    Thanks again for your trouble.
    Bob

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Linking Word with Excel (Word/Excel 2003)

    Bob,

    I may have mislead you about the syntax.

    In my grade_DB range, all I'm doing is assigning a formula to the cell. There are NO calculations since they've been done in the cells being copied. If the cell was on the same sheet as grade_DB, then you'd click say on B2 and type the formula =AA25 as an example. An easier way to create the formula could be click on B2, type the =, then scroll to AA25 and click on that, and hit enter. In B2, you now have =AA25.

    When the cell to be assigned to B2 is on another sheet, you do roughly the same thing. Click on B2 to begin with. Type the =. Now you could type the sheet name and the cell but this is the syntax I never remember. So after typing =, click on the sheet tab that contains the cell you want to copy to B2; say it's called Quiz in my workbook. Now click on AA25. Hit enter. You now have in B2 the formula

    =Quiz!AA25

    (without the quotes; I was wrong to include them before - see, I never type it, I just click, click, enter).

    This is the syntax I was talking about.

    In my grade_DB range, the students are arranged alphabetically, the same as they are in the Attendance sheet, the Test sheet, the Quiz sheet, etc. So once I've copied, say, the Quiz grade for the first student using the above (say in B2), I can "fill" the range of students in the grade_DB just by dragging the fill-squarefrom B2 down for all the students. Excel will fill in =Quiz!AA26, =Quiz!AA27, etc. as you drag down.

    Then I go over to C2 in the grade_DB, enter =Attendance!H28 (by clicking, etc.) and fill down again.

    The key here is that the arrangement of cells in my source sheets (Quiz, Attendance, etc) is in an orderly fashion - arranged by student name. This makes filling the grade_DB trivial.

    But in your case, it's not clear that your arrangement is so orderly even for one piece of information (car part). The car parts sound like they're all over the place. So you will have to end up doing a lot of clicking to get the information into an orderly DB that Word wants for a mail-merge. I'm not sure there's any other way to do it. I don't think Word's mail-merge can be directed somewhat arbitrarily as to where to get the nfo. It's not that you can't put it together in Excel, which is what Word is expecting you to do. But where you lose out in creating the orderly DB in Excel is by not being able to use the fill feature to quickly create the grade_DB (or its equivalent).

    HTH

    Fred

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Linking Word with Excel (Word/Excel 2003)

    Just to add to my previous reply:

    if the info to be "entered" into the Excel DB comes from all over the place, it may not be too hard for someone more skilled than I in VBA to create a macro that:
    - creates a new sheet with the proper column headings for fields (or you could just do this)
    - searches the other sheets for labels (eg, car part) and then uses the info in the cell next to it (presumably to the right) to get the info to enter into the new sheet
    - go across each column of the new sheet to read the labels and searches for the next piece of info for that record
    - continues searching for new records.

    The trick is that the difficulty of writing the macro would be dependent on how the info is arranged (more orderly ==> less difficulty). If there's not too much info, just manually clicking might be a lot easier.

    Fred

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Word with Excel (Word/Excel 2003)

    Thanks Fred. Really appreciate your time with this and I may end up going the macro route. Or I may just suggest to the student to organize the data in a more orderly matter (on an empty sheet perhaps) and go from there. Thanks again.
    Bob

Posting Permissions

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