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

 

[Derek Brown]

If you still need this (I see that you have had no replies, but you may have solved it), let me know and during this week-end I will reply with the answer.
I have used this method several times with the workbook containing the data to be automatically 'mapped' to bookmarks in a Word document - the bookmarks will have the same name/content as the workbook column-header.
The reason for checking first is that it will probably take about an hour to get everything together and I don't want to waste my time if you already have the answer.
It would also be useful to know which version of Office is being used.

 

[samfolds]

Hi,

Thank you so much for your replie. I haven't found a way yet so I would definitely need your help. I'm using office 2003.

I can't thank you enough for the time you'll be spending on this. If you need any other information don't hesitate to contact me.

Samfolds

 

[Derek Brown]

First here are the instructions for creating the Word 'template' and the Excel control workbook. This may look complicated but it is not - just follow it step by step and you will then have an example that you can apply to your requirements.

Create a folder for the files. For the purpose of this test I will use drive C:
Create C:\Letter Creator
Create C:\Letter Creator\Word Templates
Create C:\Letter Creator\Documents

The actual names and locations are not important as they will be located by using a 'Control Sheet' in the workbook.

Create the Word 'template' for use by the process.
1. For the purpose of this example, enter the following exactly as it is written:

Address_Name
Address1
Address2

Letter_Date

Dear Salutation

Please note that you still owe Owed_Amount.

Yours Sincerely

2. Now convert some of those words to bookmarks:
Highlight Address_Name and select Insert | Bookmark (Alt + I + K)
In the dialog box enter the 'Bookmark name' as Address_Name and click 'Add'.
Click Tools | Options | View tab and make sure that 'Bookmarks' is ticked - click OK and you should see square brackets [] around the bookmark that you have just created. This is not essential for the process but it is useful to see where you have set the bookmarks.

Create bookmarks for Address1, Address2, Letter_Date, Salutation and Owed_Amount.

Note that you cannot use spaces in these name - instead, the underscore character aids readability. By keeping the text in the document, the bookmark name and the Excel column headers exactly the same, maintenance becomes easier.

Save the document as C:\Letter Creator\Word Templates\Test Letter.doc
Close the Word document.

Create the Excel Workbook:
Name the first worksheet as "Control Sheet"
In cell A1 enter "Template Folder" and make this bold
In cell B1 enter "C:\Letter Creator\Word Templates" (without the quotes of course)
Give cell B1 the name "Template_Folder". Do this by selecting cell B1 and typing that name in the "Name Box" (just above the heading of Column A) and press 'Enter' (very important to do that).
Similarly, Enter "Data worksheet" in A2; "Data Sheet" in B2 and give B2 the name "Data_Sheet".
And in A3 enter "Document Folder"; B3 enter "C:\Letter Creator\Documents" and give B3 the name "Document_Folder".

Name the second worksheet "Data Sheet" and in Row 1 enter the headings exactly as done for the Word bookmarks: Address_Name, Address1, Address2, Letter_Date, Salutation, Owed_Amount
It is important that these heading match the bookmark names exactly. The order is unimportant - the code will search for the bookmarks in the document and than try to match each name against the worksheet headings. All the bookmarks MUST be in the worksheet but you can have more headings than there are bookmarks (those not used would be ignored). Make the headings 'bold'.
Now in Row 2 and Row 3 enter values for each of the headings.
Save the workbook as "Letter Creator.xls".

Now you said that you wanted to have a specific name for the document so add a new column header (in my test it will be cell G1) "Document Name" and give this cell (G1) the name "Document_Name".
In cell G2 enter the formula ="Report-2010-"&E2&".doc" (E2 for example contains the Salutation) and copy it down to G3.
In H1 enter "Template Name" and give this the name "Template_Name". in cells H2 and H3 enter "Test Letter.doc".
Save the workbook

Now all you need is the VBA code to run the process - I will add that as another post.

 

[Derek Brown]

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.

 

[samfolds]

Thank you so much, I'm going to try to follow all of that step by step and see how it goes.

I'll try to get back to say how it worked out.

Thank you for your time!

Samfolds

 

[samfolds]

Hi Derek,

Just followed the whole procedure, but the code I pasted in the VBA window had a compile error :

Dim oApp As Word.Application

It says :"compile error: User-defined type not defined"

I don't know if that's of any help, but I am using a french version of Office.

I've never used excel to word code so I'm clueless on what to do.

Thanks in advance for your help.

Samfolds

Edit :Oh and I can't find the tool | reference tab to add the reference.

 

[samfolds]

Hi Derek,

Just followed the whole procedure, but the code I pasted in the VBA window had a compile error :

Dim oApp As Word.Application

It says :"compile error: User-defined type not defined"

I don't know if that's of any help, but I am using a french version of Office.

I've never used excel to word code so I'm clueless on what to do.

Thanks in advance for your help.

Samfolds

Edit :Oh and I can't find the tool | reference tab to add the reference.

Ok nevermind about all that I had to browse in the Office folder to add the MSWORD.OBL file. The Microsoft Word Object 11.0 Library then appeared. Will try and run it and get back to you shrotly.

Thanks

Samfolds

 

[samfolds]

It worked beautifully .

Just so you know, I made a minor (very minor) change to your code :

 oDoc.SaveAs strWordDocumentName & ".doc"
    oDoc.Close

Before that, the file would save but didn't have any extension. I still could open it as a word document, but I prefer having the right extension at sight.

Thanks a million for your time! This will help me big time.

Samfolds

 

[Derek Brown]

Thank you for the update and pleased that you now have the solution you were looking for.

Regarding the change you made to the filename - I think that you must have omitted it from the formula that I used:
="Report-2010-"&E2&".doc"
but it makes no difference as long as it works. However, as a rule, I try to put as much as possible in the control sheet because it is much easier for others to update - for example, when you upgrade to Office 2007/2010 you just need to make a simple change to the formula to change ".doc" to "docx".
Regards
Derek