Export excel rows to doc

[shamsam1]

i have excel with 4 key tabs and fields as per columns..
I update this every day or week...Word template is flexible but will have placeholders for all fields from xls for every company

i wan to export all the value in excel row based on the input names to word doc

example as per excel attacehd : xyz details to word doc then abc details to word doc .

its should be separate word doc

[lucas]

Is all the data on sheet "events"?

[lucas]

attached is an example that shows how to do this.

I use it all the time.

You must have the bookmarks placed in the template.

[shamsam1]

Hi steve,

i will be working on excel based on the sample you provided.

i want to search name abc or xyx(name) in 4 work sheet(events, executive,fanancil,profile) then put the value in the book mark (word). it based on name selected in first sheet.

Thanks for the support

[lucas]

then put the value in the book mark (word). it based on name selected in first sheet

But the first sheet is events. Please clarify.

Are you saying the xyx must appear on all four sheets and if it does to use the data on sheet events?

[shamsam1]

yes first sheet name is events in that i will select name then click generate report, it should select the whole rows of xyz then it should search from next sheets where value is xyz i.e name it should select whole row from that then again the next sheet same procedure..

i will be generating a consolidated report

[shamsam1]

i have used this code to get my work done

Option Explicit

Private Sub CommandButton1_Click()
Dim key As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim i1 As Integer
Dim i2 As Integer
Dim objword As Object
Dim rng As Range                    'our source range
Dim wdApp As New Word.Application   'a new instance of Word
Dim wdDoc As Word.Document          'our new Word document
Dim t As Word.Range                 'the new table in Word as a range
Dim myWordFile As String
key = ActiveCell.Value
For i = 2 To 200
    If Sheet1.Cells(i, 3) = key Then
        'MsgBox ("No record")
        'path to Word template
        myWordFile = ThisWorkbook.Path & "\Sno.docx"
        Set wdDoc = wdApp.Documents.Add(myWordFile)
        'initialize the Word template path
        'here, it's set to be in the same directory as our source workbook
        ' For k = 2 To 200
            wdDoc.Tables(1).Cell(2, 1).Range.Text = Sheet1.Cells(i, 2).Value
            wdDoc.Tables(1).Cell(2, 2).Range.Text = Sheet1.Cells(i, 3).Value
            wdDoc.Tables(1).Cell(2, 3).Range.Text = Sheet1.Cells(i, 4).Value
            wdDoc.Tables(1).Cell(2, 4).Range.Text = Sheet1.Cells(i, 5).Value
            wdDoc.Tables(1).Cell(2, 5).Range.Text = Sheet1.Cells(i, 6).Value
            wdDoc.Tables(1).Cell(2, 6).Range.Text = Sheet1.Cells(i, 7).Value
            wdDoc.Tables(1).Cell(2, 7).Range.Text = Sheet1.Cells(i, 8).Value
            wdDoc.Tables(1).Cell(2, 8).Range.Text = Sheet1.Cells(i, 9).Value
            wdDoc.Tables(1).Cell(2, 9).Range.Text = Sheet1.Cells(i, 10).Value
            'wdDoc.Tables(1).Cell(2, 10).Range.Text = Sheet1.Cells(i, 11).Value
            'until now the Word app has been a background process
            For m = 2 To 200
                If Sheet2.Cells(m, 7) = key Then
                    wdDoc.Tables(1).Cell(2, 10).Range.Text = Sheet2.Cells(m, 1).Value
                    wdDoc.Tables(2).Cell(2, 1).Range.Text = Sheet2.Cells(m, 2).Value
                    wdDoc.Tables(2).Cell(2, 2).Range.Text = Sheet2.Cells(m, 3).Value
                    wdDoc.Tables(2).Cell(2, 3).Range.Text = Sheet2.Cells(m, 4).Value
                    wdDoc.Tables(2).Cell(2, 4).Range.Text = Sheet2.Cells(m, 5).Value
                    wdDoc.Tables(2).Cell(2, 5).Range.Text = Sheet2.Cells(m, 6).Value
                    wdDoc.Tables(2).Cell(2, 6).Range.Text = Sheet2.Cells(m, 7).Value
                    wdDoc.Tables(2).Cell(2, 7).Range.Text = Sheet2.Cells(m, 8).Value
                    wdDoc.Tables(2).Cell(2, 8).Range.Text = Sheet2.Cells(m, 9).Value
                    wdDoc.Tables(2).Cell(2, 9).Range.Text = Sheet2.Cells(m, 10).Value
                    wdDoc.Tables(3).Cell(2, 1).Range.Text = Sheet2.Cells(m, 12).Value
                    wdDoc.Tables(3).Cell(2, 2).Range.Text = Sheet2.Cells(m, 13).Value
                    wdDoc.Tables(3).Cell(2, 3).Range.Text = Sheet2.Cells(m, 14).Value
                    wdDoc.Tables(3).Cell(2, 4).Range.Text = Sheet2.Cells(m, 15).Value
                    wdDoc.Tables(3).Cell(2, 5).Range.Text = Sheet2.Cells(m, 16).Value
                    wdDoc.Tables(3).Cell(2, 6).Range.Text = Sheet2.Cells(m, 17).Value
                End If
                If Sheet2.Cells(i, 7) = "" Then
                    Exit For
                End If
            Next m
            For k = 1 To 200
                If Sheet3.Cells(k, 1) = key Then
                    wdDoc.Tables(3).Cell(2, 7).Range.Text = Sheet3.Cells(k, 1).Value
                    wdDoc.Tables(3).Cell(2, 8).Range.Text = Sheet3.Cells(k, 2).Value
                    wdDoc.Tables(3).Cell(2, 9).Range.Text = Sheet3.Cells(k, 3).Value
                    wdDoc.Tables(3).Cell(2, 10).Range.Text = Sheet3.Cells(k, 4).Value
                    wdDoc.Tables(4).Cell(2, 1).Range.Text = Sheet3.Cells(k, 5).Value
                    wdDoc.Tables(4).Cell(2, 2).Range.Text = Sheet3.Cells(k, 6).Value
                    wdDoc.Tables(4).Cell(2, 3).Range.Text = Sheet3.Cells(k, 7).Value
                    wdDoc.Tables(4).Cell(2, 4).Range.Text = Sheet3.Cells(k, 8).Value
                    wdDoc.Tables(4).Cell(2, 5).Range.Text = Sheet3.Cells(k, 9).Value
                    wdDoc.Tables(4).Cell(2, 6).Range.Text = Sheet3.Cells(k, 10).Value
                    wdDoc.Tables(4).Cell(2, 7).Range.Text = Sheet3.Cells(k, 11).Value
                    wdDoc.Tables(4).Cell(2, 8).Range.Text = Sheet3.Cells(k, 12).Value
                    wdDoc.Tables(4).Cell(2, 9).Range.Text = Sheet3.Cells(k, 13).Value
                    wdDoc.Tables(4).Cell(2, 10).Range.Text = Sheet3.Cells(k, 14).Value
                    wdDoc.Tables(5).Cell(2, 1).Range.Text = Sheet3.Cells(k, 15).Value
                    wdDoc.Tables(5).Cell(2, 2).Range.Text = Sheet3.Cells(k, 16).Value
                    wdDoc.Tables(5).Cell(2, 3).Range.Text = Sheet3.Cells(k, 17).Value
                    wdDoc.Tables(5).Cell(2, 4).Range.Text = Sheet3.Cells(k, 18).Value
                    wdDoc.Tables(5).Cell(2, 5).Range.Text = Sheet3.Cells(k, 19).Value
                    wdDoc.Tables(5).Cell(2, 6).Range.Text = Sheet3.Cells(k, 20).Value
                    wdDoc.Tables(5).Cell(2, 7).Range.Text = Sheet3.Cells(k, 21).Value
                    wdDoc.Tables(5).Cell(2, 8).Range.Text = Sheet3.Cells(k, 22).Value
                    wdDoc.Tables(5).Cell(2, 9).Range.Text = Sheet3.Cells(k, 23).Value
                    wdDoc.Tables(5).Cell(2, 10).Range.Text = Sheet3.Cells(k, 24).Value
                    wdDoc.Tables(6).Cell(2, 1).Range.Text = Sheet3.Cells(k, 25).Value
                    wdDoc.Tables(6).Cell(2, 2).Range.Text = Sheet3.Cells(k, 26).Value
                    wdDoc.Tables(6).Cell(2, 3).Range.Text = Sheet3.Cells(k, 27).Value
                End If
                If Sheet3.Cells(k, 1) = "" Then
                    Exit For
                End If
           Next k
           'For i1 = 1 To 200
                ' If Sheet3.Cells(i1, 1) = key Then
                '   wdDoc.Tables(3).Cell(2, 8).Range.Text = Sheet2.Cells(i1, 1).Value
                '  wdDoc.Tables(3).Cell(2, 9).Range.Text = Sheet2.Cells(i1, 2).Value
                '   wdDoc.Tables(3).Cell(2, 10).Range.Text = Sheet2.Cells(i1, 3).Value
                '   wdDoc.Tables(4).Cell(2, 1).Range.Text = Sheet2.Cells(i1, 4).Value
                '  wdDoc.Tables(4).Cell(2, 2).Range.Text = Sheet2.Cells(i1, 5).Value
                '  wdDoc.Tables(4).Cell(2, 3).Range.Text = Sheet2.Cells(i1, 6).Value
                '  wdDoc.Tables(4).Cell(2, 4).Range.Text = Sheet2.Cells(i1, 7).Value
                '  wdDoc.Tables(4).Cell(2, 5).Range.Text = Sheet2.Cells(i1, 8).Value
                ' wdDoc.Tables(4).Cell(2, 6).Range.Text = Sheet2.Cells(i1, 9).Value
                '  wdDoc.Tables(4).Cell(2, 7).Range.Text = Sheet2.Cells(i1, 10).Value
                ' wdDoc.Tables(4).Cell(2, 8).Range.Text = Sheet2.Cells(i1, 11).Value
                ' wdDoc.Tables(4).Cell(2, 9).Range.Text = Sheet2.Cells(i1, 12).Value
                '  wdDoc.Tables(4).Cell(2, 10).Range.Text = Sheet2.Cells(i1, 13).Value
                '  wdDoc.Tables(5).Cell(2, 1).Range.Text = Sheet2.Cells(i1, 14).Value
                '  wdDoc.Tables(5).Cell(2, 2).Range.Text = Sheet2.Cells(i1, 15).Value
                '  wdDoc.Tables(5).Cell(2, 3).Range.Text = Sheet2.Cells(i1, 16).Value
                '  wdDoc.Tables(5).Cell(2, 4).Range.Text = Sheet2.Cells(i1, 17).Value
                '  wdDoc.Tables(5).Cell(2, 5).Range.Text = Sheet2.Cells(i1, 18).Value
                ' wdDoc.Tables(5).Cell(2, 6).Range.Text = Sheet2.Cells(i1, 19).Value
                '  wdDoc.Tables(5).Cell(2, 7).Range.Text = Sheet2.Cells(i1, 20).Value
                '  wdDoc.Tables(5).Cell(2, 8).Range.Text = Sheet2.Cells(i1, 21).Value
                ' wdDoc.Tables(5).Cell(2, 9).Range.Text = Sheet2.Cells(i1, 22).Value
                ' wdDoc.Tables(5).Cell(2, 10).Range.Text = Sheet2.Cells(i1, 23).Value
                ' wdDoc.Tables(6).Cell(2, 1).Range.Text = Sheet2.Cells(i1, 24).Value
                ' wdDoc.Tables(6).Cell(2, 2).Range.Text = Sheet2.Cells(i1, 25).Value
                ' wdDoc.Tables(6).Cell(2, 3).Range.Text = Sheet2.Cells(i1, 26).Value
                ' wdDoc.Tables(6).Cell(2, 4).Range.Text = Sheet2.Cells(i1, 27).Value
            '    End If
            'If Sheet3.Cells(i, 7) = "" Then
                'Exit For
            'End If
        'Next i1
    End If
    If Sheet1.Cells(i, 3) = "" Then
        Exit For
    End If
Next i
    wdApp.Visible = True
    'we could use the Word app object to finish off
    'you may also want to things like generate a filename and save the file
    wdApp.Activate
'For j = 1 To 200
    'If Sheet3.Cells(j, 1) = key Then
        'MsgBox ("bnbcmxnc")
        'Set objword = CreateObject("Word.Application")
        'objword.Visible = True
        'objword.Documents.Open ("D:\Sno.docx")
    'End If
    'If Sheet3.Cells(j, 1) = "" Then
        'Exit For
    'End If
'Next j
End Sub

[lucas]

So the requirement is different on each sheet and you used tables in Word instead of bookmarks.

If everything stays the same in the excel file you should be fine.