Ventilaator
08-11-2011, 10:08 AM
I am up with a task to read data from excel table and create a bills as word files for every person (every row). I have managed to type this much code thus far ...
Option Explicit
Public Sub CreateBills()
Dim rng As Range 'Excel table
Dim n As Integer 'Number of rows
Dim wrdApp As New Word.Application 'Word app
Dim wrdDoc As Word.Document 'Word doc
Dim myWordFile As String 'File path
Dim i As Integer 'Iterator
Application.StatusBar = "Reading data from excel..."
Set rng = Range("alg").CurrentRegion
n = rng.Rows.Count
''.DOCX CREATION
Application.StatusBar = "Creating word files..."
Set wrdApp = New Word.Application
For i = 2 To 2 'TEMP
Set wrdDoc = wrdApp.Documents.Add(ThisWorkbook.Path & "\BillTemplate.dotx")
rng.Cells(i, 2).Copy
wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.PasteAndFormat (wdSingleCellText) 'TEMP
wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.InsertParagraphAfter 'TEMP
myWordFile = ThisWorkbook.Path & "\" & rng.Cells(i, 2).Value & " " & rng.Cells(i, 5) & ".docx"
wrdApp.ActiveDocument.SaveAs2 Filename:=myWordFile
Next
Application.StatusBar = "Clearing..."
wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
Application.StatusBar = "Done"
End Sub
I'm stuck at the part where I need to start inserting data into specific regions in my word template. How can this process be most optimally done? Can I somehow name specific regions in my word template and insert data into named regions? Any help will be appreciated.
Best regards,
-Vent
Option Explicit
Public Sub CreateBills()
Dim rng As Range 'Excel table
Dim n As Integer 'Number of rows
Dim wrdApp As New Word.Application 'Word app
Dim wrdDoc As Word.Document 'Word doc
Dim myWordFile As String 'File path
Dim i As Integer 'Iterator
Application.StatusBar = "Reading data from excel..."
Set rng = Range("alg").CurrentRegion
n = rng.Rows.Count
''.DOCX CREATION
Application.StatusBar = "Creating word files..."
Set wrdApp = New Word.Application
For i = 2 To 2 'TEMP
Set wrdDoc = wrdApp.Documents.Add(ThisWorkbook.Path & "\BillTemplate.dotx")
rng.Cells(i, 2).Copy
wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.PasteAndFormat (wdSingleCellText) 'TEMP
wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.InsertParagraphAfter 'TEMP
myWordFile = ThisWorkbook.Path & "\" & rng.Cells(i, 2).Value & " " & rng.Cells(i, 5) & ".docx"
wrdApp.ActiveDocument.SaveAs2 Filename:=myWordFile
Next
Application.StatusBar = "Clearing..."
wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
Application.StatusBar = "Done"
End Sub
I'm stuck at the part where I need to start inserting data into specific regions in my word template. How can this process be most optimally done? Can I somehow name specific regions in my word template and insert data into named regions? Any help will be appreciated.
Best regards,
-Vent