Copy data from excel and paste it in to a word template at different places

[sharad.sony]

I have some data in an Excel Sheet which have been pull from the master data in another sheet by the VBA. The data is showing in excel sheet as below:




Column A
Column B


1
Type
Ready Mix Concrete


2
EA
5000


3
TA
4500


4
Tender no.
272512


5
Dated
01.06.16


6
Opened on
07.06.16



And I have an MS-Word template as detail below:


“ An estimate amounting to Rs. [TA] based on approved rates for the above [Type] was sanctioned by the competent authority. Against this estimate tenders amounting to Rs. [TA] were invited vide Tender No. [Tender No.] Dated [Dated] which has opened on [opened on] . ”



I want to create a macro in the Excel by which the value of different cells of column ‘B’ be paste in template in place of respective [ ] to create a new word doc as below:

Please help me.

[gmayor]

It would have made things much simpler had you used six columns and one row (column B values) plus a header row (column A values) for this data. You could then have used mail merge to put the data in a document based on your template.

However it can be done with an Excel macro, and the sheet as you have it, as follows:

Option Explicit

Sub CreateADocument()
Dim wdApp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim xlSheet As Worksheet
Dim i As Integer
On Error Resume Next
Set wdApp = GetObject(, "Word.Application") 'Process is MUCH faster if Word is already running.
If Err Then
Set wdApp = CreateObject("Word.Application")
End If
wdApp.Visible = True
On Error GoTo 0
Set xlSheet = ActiveSheet
Set wdDoc = wdApp.Documents.Add("C:\Path\Estimate.dotx") 'the template
For i = 2 To 7 'assumes a header row, if not change 1 to 6
Set oRng = wdDoc.Range
With oRng.Find
Do While .Execute(FindText:="[" & xlSheet.Cells(i, 1) & "]")
oRng.Text = xlSheet.Cells(i, 2)
oRng.Collapse 0
Loop
End With
Next i
lbl_Exit:
Set wdApp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Set xlSheet = Nothing
Exit Sub
End Sub

[sharad.sony]

Hurrah!

Its work, thanks alot from the deep of my heart Mr. Gmayor. Little more advice will become very useful. Can we copy the data Range("A2:B5") from active sheet to word after 2nd para (or wherever we need any specific location)

[gmayor]

After the line

Next iadd the following
xlSheet.Range("A2:B5").Copy
wdDoc.Paragraphs(2).Range.InsertParagraphAfter
Set oRng = wdDoc.Paragraphs(3).Range
oRng.collapse 1
oRng.Paste
This assumes that there are at least two paragraphs in the document and the range is inserted after paragraph 2.

[mdmackillop]

I'd also refer you to SNB's code here (http://www.vbaexpress.com/forum/showthread.php?56174-VBA-to-Add-3-Aligned-Footers-with-Custom-Formatting-and-Text&p=344110&viewfull=1#post344110) which could be easily adapted