PDA

View Full Version : Solved: export excel rows to doc



shamsam1
03-10-2010, 05:43 AM
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
03-10-2010, 08:30 AM
Is all the data on sheet "events"?

lucas
03-10-2010, 08:43 AM
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
03-10-2010, 09:42 AM
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
03-10-2010, 10:03 AM
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
03-11-2010, 03:34 AM
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
03-11-2010, 07:48 AM
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(2).Cell(2, 10).Range.Text = Sheet2.Cells(m, 11).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
03-11-2010, 08:10 AM
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.