Word Report using excel database

[samfolds]

Hello all,

I'm looking for a way to save/print several reports by changing only certain information in that report. Those information are placed in an excel database and all I want to do is somehow create a word template and link each column to a certain place. For example, say my excel database had the columns :

NamesAddressPhoneSam1234 pool st.123-456-7891Jack4321 sack st.323-525-9874Gil654 8th avenue325-525-8787Andrew987 9th st.322-252-5252

And my reports looks like this :

*****************************************
Hello "NAME",

blablablabla "PHONE" blablablabla.

blablablablablabla "ADDRESS".
*****************************************


I would like to loop through all the rows (1 report per row) and save the report in the form : Report-2010-"NAME".doc ...

Can someone enlight me on how to do that?

Thank you very much.

Samfolds

 

[drm027]

Hello, I know this is a pretty old thread, but I am hoping someone can help me.
I used the code and it worked well for me, except the newly created word doc does not maintain the formatting of the original word doc. For example the template is sized "Legal" and the page margins are "Narrow". The new doc is sized "Letter" and "Normal" margins.

Other than that the VBA works great for me!

Thanks for some suggestions.

 

[Macropod]

The problem you're encountering is related to these lines:
Set oDoc = oApp.Documents.Add
oApp.Selection.InsertFile strTemplate
The Add method accepts a template name as a parameter. However, when no template is specified, Word's 'Normal' template is used. It seems the original code was trying to get around this via .InsertFile strTemplate. If strTemplate is an actual template name, however, you'd do better to replace both those lines with:
Set oDoc = oApp.Documents.Add(strTemplate)
Alternatively, you need to specify the appropriate template's path & name. For example:
Set oDoc = oApp.Documents.Add("C:\Templates\Report Template.dotm")
Of course, if, as is often the case, someone merely modified a document so that its layout no longer reflects its own template, you'll need to take an unused copy of one of those documents and save it as a template.

 

[drm027]

That worked out perfectly! Thank you for your help.
I'm new to VBA, but I'm finding that it can do some pretty incredible things to make life easier!

 

[Dark8]

Hi All,

Just been reading this thread and its been an absolute godsend!

In my case, i had a form that had fields such as address, name, amounts etc that needed to be completed. I have been able to import those from Excel perfectly using the VBA code. However, the letter had write restrictions on it, and was a "form" only. Some portions of the form still need to be completed manually and i wanted to know what code i could add to the Excel VBA to reinstate the restrictions after the initial data was copied across?

Any assistance would be greatly appreciated!

Thanks!

 

[Macropod]

i wanted to know what code i could add to the Excel VBA to reinstate the restrictions after the initial data was copied across?

The core code for this would be something like:

Sub Demo()
Application.ScreenUpdating = False
Const Pwd As String = ""
With ActiveDocument
  If .ProtectionType <> wdNoProtection Then .Unprotect Password:=Pwd
  'Do your processing here
  .Protect Type:=wdAllowOnlyFormFields, NoReset:=False, Password:=Pwd
End With
Application.ScreenUpdating = True
End Sub

Simply add the password, if any, between the "".

 

[Dark8]

thanks!

 

[Delphy010]

Here is the code - press Alt +F11 and create a new code module and enter the following code:
You will need to use Tools | Reference to add a reference to the Microsoft Word Object 11.0 Library (it would be 12.0 for Office 2007).

Option Explicit
Sub Create_Letters()
' Note: you will need to add error-trapping
Dim objX As Object
Dim rng1 As Range
Dim rng2 As Range
Dim wb As Workbook
Dim wsControl As Worksheet
Dim wsData As Worksheet
'
Dim oApp As Word.Application
Dim oBookMark As Word.Bookmark
Dim oDoc As Word.Document
'
Dim strDocumentFolder As String
Dim strTemplate As String
Dim strTemplateFolder As String
Dim lngTemplateNameColumn As Long
Dim strWordDocumentName As String
Dim lngDocumentNameColumn As Long
Dim lngRecordKount As Long ' not used but retained for future use
'
Set wb = ThisWorkbook
Set wsControl = wb.Worksheets("Control Sheet")
wsControl.Activate
Set wsData = wb.Worksheets(wsControl.[Data_Sheet].Value)
strTemplateFolder = wsControl.[Template_Folder].Value
strDocumentFolder = wsControl.[Document_Folder].Value
wsData.Activate
lngTemplateNameColumn = wsData.[Template_Name].Column
lngDocumentNameColumn = wsData.[Document_Name].Column
' number of letters required:
' must not have any blank cells in column A - except at the end
Set rng1 = wsData.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
lngRecordKount = rng1.Rows.Count
'
'Set oApp = CreateObject("Word Application")
Set oApp = New Word.Application
' Process each record in turn
For Each rng2 In rng1
    strTemplate = strTemplateFolder & "\" & wsData.Cells(rng2.Row, lngTemplateNameColumn)
    strWordDocumentName = strDocumentFolder & "\" & wsData.Cells(rng2.Row, lngDocumentNameColumn)
    ' check that template exists
    If Dir(strTemplate) = "" Then
        MsgBox strTemplate & " not found"
        GoTo Tidy_Exit
    End If
    Set oDoc = oApp.Documents.Add
    oApp.Selection.InsertFile strTemplate
    ' locate each bookmark
    For Each oBookMark In oDoc.Bookmarks
        Set objX = wsData.Rows(1).Find(oBookMark.Name, LookIn:=xlValues, LookAt:=xlWhole)
        If Not objX Is Nothing Then
            ' found
            If Right(oBookMark.Name, 4) = "Date" Then
                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
            ElseIf Right(oBookMark.Name, 6) = "Amount" Then
                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
            Else
                oBookMark.Range.Text = wsData.Cells(rng2.Row, objX.Column)
            End If
        Else
            MsgBox "Bookmark '" & oBookMark.Name & "' not found", vbOKOnly + vbCritical, "Error"
            GoTo Tidy_Exit
        End If
    Next oBookMark
    '
    oDoc.SaveAs strWordDocumentName
    oDoc.Close
Next rng2
'
Tidy_Exit:
On Error Resume Next
Set oDoc = Nothing
Set oBookMark = Nothing
Set objX = Nothing
Set rng1 = Nothing
Set rng2 = Nothing
oApp.Quit
Set oApp = Nothing
'
Set wsData = Nothing
Set wsControl = Nothing
Set wb = Nothing
'
End Sub

This has been tested - in fact it was all created from scratch. I use both 2003 and 2007 and my code was more complex and had different requirements, so I created this Excel 2003 example to meet your requirements.
If you have any problems or something has not been explained well enough, let me know please.

Just want to say a big THANK YOU for your code back in 2010, its a great help to what I want to do! I've also combined it with a vlookup function so that data entry for the data of the letter is automated.

Anyway thanks a lot!

 

[lizl heunis]

Hi Derek

i know this post is old, but i used your advise (step by step) and it works perfectly, i was just wondering if you perhaps have a solution for the Word doc keeping it's formatting when it creates from the template
it's not the dates or percentages i'm having an issue with, for some reason when the macro creates and saves the word docs, it adds spaces etc., the template file of course stays the same, any ideas

many thanks once again

 

[sztibor]

Hello Derek,
I know it's an old post, but I found it googling my problem and suits my needs almost perfectly. Very nice code and helpful explanation.
I am trying to implement into it the option to create the word documents for only certain rows (asked as imput, or stored in a cell, like start line - finish line).
I am quite new to VBA, so I have problems figuring it out. Could you take a look at it and help me with some suggestions?
Many thanks,
Tibor