Results 1 to 4 of 4

Thread: Loop_Find & Copy to word document

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0

    Loop_Find & Copy to word document

    Hi,
    Below code Search string "FORM" on excel worksheet and copy data to word document in table only.
    Now i need help to
    1. Add loop in below code for find ( Do...Loop)
    2. Select row below existing table on word document & copy next table below from excel
    i need help to modify below code to find each string "Form" from worksheet & copy data to word document

    i will appreciate any help on this

    Code:
    Sub test()
       Dim intNoOfRows
       Dim intNoOfColumns
       Dim objWord
       Dim objDoc
       Dim objRange
       Dim objTable
       Dim WordDoc As Object
    'set table row & column
     intNoOfRows = 7
     intNoOfColumns = 2
     'create wrd file
      Set objWord = CreateObject("Word.Application")
      objWord.Visible = True 'add this to see the Word instance and document
      Set objDoc = objWord.Documents.Add
      Dim rng As Range
    'Do While True
    AppActivate "Microsoft Excel"
    Sheet1.Select
    'find form on worksheet in column1
    Set rng = Columns(1).Find(what:="FORM", LookIn:=xlValues, lookat:=xlPart)
    If rng Is Nothing Then Exit Sub
    Temp = rng.Row + 1
    Temp2 = rng.Row + 7
    Temp3 = rng.Row + 5
    AppActivate "Microsoft Word"
     objDoc.Range.Select 'select everything in the word document
            With objWord.Selection
            .EndKey 6 'move pointer to the end
            .TypeParagraph 'insert return
            .TypeParagraph 'insert return
            '.Paste 'paste whatever was copied from Excel
        End With
    Set objSelection = objWord.Selection
    Set objRange = objDoc.Range
    objDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns
    Set objTable = objDoc.Tables(1)
    objTable.Borders.Enable = True
      objTable.Cell(1, 1).Range.Text = Sheet1.Cells(Temp, 1).Value
      objTable.Cell(2, 1).Range.Text = Sheet1.Cells(Temp + 1, 1).Value
      objTable.Cell(3, 1).Range.Text = Sheet1.Cells(Temp + 2, 1).Value
      objTable.Cell(4, 1).Range.Text = Sheet1.Cells(Temp + 3, 1).Value
      objTable.Cell(5, 1).Range.Text = Sheet1.Cells(Temp + 4, 1).Value
      objTable.Cell(6, 1).Range.Text = Sheet1.Cells(Temp + 5, 1).Value
      objTable.Cell(7, 1).Range.Text = Sheet1.Cells(Temp + 6, 1).Value
    
      objTable.Cell(1, 2).Range.Text = Sheet1.Cells(Temp, 2).Value
      objTable.Cell(2, 2).Range.Text = Sheet1.Cells(Temp + 1, 2).Value
      objTable.Cell(3, 2).Range.Text = Sheet1.Cells(Temp + 2, 2).Value
      objTable.Cell(4, 2).Range.Text = Sheet1.Cells(Temp + 3, 2).Value
      objTable.Cell(5, 2).Range.Text = Sheet1.Cells(Temp + 4, 2).Value
      objTable.Cell(6, 2).Range.Text = Sheet1.Cells(Temp + 5, 2).Value
      objTable.Cell(7, 2).Range.Text = Sheet1.Cells(Temp + 6, 2).Value
      
     objTable.Cell(6, 2).Split NumColumns:=3
      objTable.Cell(6, 3).Range.Text = "AGE"
      objTable.Cell(6, 4).Range.Text = Sheet1.Cells(Temp + 5, 4).Value
     
    objDoc.Tables(1).Cell(1, 1).Select
    objSelection.SplitTable
    objSelection.TypeText Text:="Form 1"
     objDoc.Range.Select 'select everything in the word document
        With objWord.Selection
            .EndKey 6 'move pointer to the end
            .TypeParagraph 'insert return
            .TypeParagraph 'insert return
            '.Paste 'paste whatever was copied from Excel
        End With
    AppActivate "Microsoft Word"
    AppActivate "Microsoft Excel"
    
        Set objDoc = Nothing 'release memory
        Set objWord = Nothing 'release memory
    End Sub
    Attached Files Attached Files

Similar Threads

  1. Replies: 7
    Last Post: 08-24-2015, 10:58 PM
  2. Replies: 1
    Last Post: 05-21-2013, 11:58 AM
  3. Replies: 2
    Last Post: 04-17-2013, 11:53 PM
  4. Replies: 1
    Last Post: 10-16-2012, 01:53 PM
  5. Send Outlook Email With Word Document
    By Murali K in forum Excel Help
    Replies: 2
    Last Post: 06-27-2012, 08:42 PM

Posting Permissions

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