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(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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.