none
import one field from Excel to Word RRS feed

  • Question

  • Hello

    I have a Word 2003 table(1 cols, n rows)

    each row contains:

    emplyee name:

    code#:

    the employee name should be imported from a specific field in Excel(Excel sheet contains employee name, DOB, salary.....)

    and the code# is continuous #s from 100-199

    Can I use Macros to do that?

    Thanks a lot in advance

    Friday, December 3, 2010 6:44 PM

Answers

  • Hi icp-dev,

    Here are two basic macros to create a Word document with a single table, populated with the values from column A of the active workbook. Choose which method you'd prefer to use - the second one preserves any text formatting in the Excel cells.

    Sub SendRangeToDoc()
    Dim LastRow As Long, i As Long
    Dim wdApp As Word.Application, WdDoc As String
    'Determine the Excel range
    LastRow = ActiveWorkbook.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
    'Start Word
    Set wdApp = New Word.Application
    wdApp.Visible = True
    With wdApp
      'Create the Word Document
      .Documents.Add
      With .ActiveDocument
        'Create the single-column table at the start of the document
        .Range.Tables.Add Range:=.Range(0), NumRows:=LastRow, NumColumns:=1
        With .Tables(1)
          'Populate the Word table with the Excel data from Column A, plus the entry number.
          For i = 1 To LastRow
            .Cell(i, 1).Range.Text = ActiveWorkbook.Sheets(1).Cells(i, 1) & vbTab & i
          Next
        End With
      End With
    End With
    Set wdApp = Nothing
    End Sub

    Sub SendRangeToDoc()
    Dim LastRow As Long, i As Long
    Dim wdApp As Word.Application, WdDoc As String
    'Determine the Excel range
    LastRow = ActiveWorkbook.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
    ActiveWorkbook.Sheets(1).Range("A1", "A" & LastRow).Copy
    'Start Word
    Set wdApp = New Word.Application
    wdApp.Visible = True
    With wdApp
      'Create the Word Document
      .Documents.Add
      With .ActiveDocument
        'Paste the copied data into the the document
        .Range.Paste
        With .Tables(1)
          'Add the record # to each row in the Word table
          For i = 1 To LastRow
            .Cell(i, 1).Range.InsertAfter vbTab & i
          Next
        End With
      End With
    End With
    Set wdApp = Nothing
    End Sub

    Note: For either macro, you'll need to set a reference in your Excel vba project to the Word vba library.


    Cheers

    macropod MS MVP - Word

    • Marked as answer by Bessie Zhao Monday, December 13, 2010 11:28 AM
    Sunday, December 5, 2010 2:36 AM

All replies

  • It is not really clear what you want to do, but it should be possible to do it with a macro if not by the use of mail merge.

    We need a better description than that which you have given to be able to provide a precise method.

    -- Hope this helps.

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

    "icp-dev" wrote in message news:d5cd7668-5c81-43c3-a5c6-a914762294b1@communitybridge.codeplex.com...

    Hello

    I have a Word 2003 table(1 cols, n rows)

    each row contains:

    emplyee name:

    code#:

    the employee name should be imported from a specific field in Excel(Excel sheet contains employee name, DOB, salary.....)

    and the code# is continuous #s from 100-199

    Can I use Macros to do that?

    Thanks a lot in advance


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, December 3, 2010 9:48 PM
  • I have Excel document contains employees data:

    (employee name, date of birth, salary, qualifications, ……)

    I want to create Excel Macro that:

    1.     open empty Word document

    2.     create table with 1 column and n rows(n=number of employee in Excel sheet)

    3.     in each row in the Word table I should write:

    (employee name, employee code)

    The employee name is taken from Excel

    BUT

    Employee code is a number from 100 to 199

     

    Friday, December 3, 2010 10:28 PM
  • Hi icp-dev,

    You could create such a table entirely within Excel, without Word ever being involved. Even so, if you wanted to, you could link the Excel range to a Word table, if need be, without any code at all. That approach would also allow the Word document to update automatically to reflect the changes in Excel.

    So:
    Does Word need to be involved (eg because there's more to go into the document than just the table)?
    Do you want a dynamic link?


    Cheers

    macropod MS MVP - Word

    Saturday, December 4, 2010 5:25 AM
  • Hi icp-dev,

    You could create such a table entirely within Excel, without Word ever being involved. Even so, if you wanted to, you could link the Excel range to a Word table, if need be, without any code at all. That approach would also allow the Word document to update automatically to reflect the changes in Excel.

    So:
    Does Word need to be involved (eg because there's more to go into the document than just the table)?
    Do you want a dynamic link?


    Cheers

    macropod MS MVP - Word


    Thanks a lot macropod

    yes I should use Word(the Manager wants it)

    and, a dynamic link is not needed

    Saturday, December 4, 2010 11:20 PM
  • Hi icp-dev,

    Here are two basic macros to create a Word document with a single table, populated with the values from column A of the active workbook. Choose which method you'd prefer to use - the second one preserves any text formatting in the Excel cells.

    Sub SendRangeToDoc()
    Dim LastRow As Long, i As Long
    Dim wdApp As Word.Application, WdDoc As String
    'Determine the Excel range
    LastRow = ActiveWorkbook.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
    'Start Word
    Set wdApp = New Word.Application
    wdApp.Visible = True
    With wdApp
      'Create the Word Document
      .Documents.Add
      With .ActiveDocument
        'Create the single-column table at the start of the document
        .Range.Tables.Add Range:=.Range(0), NumRows:=LastRow, NumColumns:=1
        With .Tables(1)
          'Populate the Word table with the Excel data from Column A, plus the entry number.
          For i = 1 To LastRow
            .Cell(i, 1).Range.Text = ActiveWorkbook.Sheets(1).Cells(i, 1) & vbTab & i
          Next
        End With
      End With
    End With
    Set wdApp = Nothing
    End Sub

    Sub SendRangeToDoc()
    Dim LastRow As Long, i As Long
    Dim wdApp As Word.Application, WdDoc As String
    'Determine the Excel range
    LastRow = ActiveWorkbook.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
    ActiveWorkbook.Sheets(1).Range("A1", "A" & LastRow).Copy
    'Start Word
    Set wdApp = New Word.Application
    wdApp.Visible = True
    With wdApp
      'Create the Word Document
      .Documents.Add
      With .ActiveDocument
        'Paste the copied data into the the document
        .Range.Paste
        With .Tables(1)
          'Add the record # to each row in the Word table
          For i = 1 To LastRow
            .Cell(i, 1).Range.InsertAfter vbTab & i
          Next
        End With
      End With
    End With
    Set wdApp = Nothing
    End Sub

    Note: For either macro, you'll need to set a reference in your Excel vba project to the Word vba library.


    Cheers

    macropod MS MVP - Word

    • Marked as answer by Bessie Zhao Monday, December 13, 2010 11:28 AM
    Sunday, December 5, 2010 2:36 AM