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

 

[Eric Young]

It's perfect, Thank you so much!!!!

 

[GeneralOb]

This is nearly perfect. The trouble I’m having now though is that because this uses Bookmarks, I have to use Cross-reference fields in my template.

However no matter what I’ve tried so far I can’t seem to get the Cross-reference fields to update correctly! I think this might be because the code replaces the bookmark field with data and therefore when the Cross-reference fields try to update with the same data, the reference source is not found as it no longer is a bookmark.

Please could someone let me know if my understanding of this is correct and hopefully help me find a way around this?

Thanks in advance.

 

[Macropod]

To retain the bookmarks so the cross-references can be updated:
1. Add a new variable - Dim BmkRng As Word.Range
2. Change the 'For Each oBookMark In oDoc.Bookmarks' loop to:

    For Each oBookMark In oDoc.Bookmarks
        With oBookMark
            Set objX = wsData.Rows(1).Find(.Name, LookIn:=xlValues, LookAt:=xlWhole)
            If Not objX Is Nothing Then
            ' found
                Set BmkRng = .Range
                With BmkRng
                    If Right(.Name, 4) = "Date" Then
                        .Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
                    ElseIf Right(.Name, 6) = "Amount" Then
                        .Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
                    Else
                        .Text = wsData.Cells(rng2.Row, objX.Column)
                    End If
                End With
                oDoc.Bookmarks.Add .Name, BmkRng
            Else
                MsgBox "Bookmark '" & .Name & "' not found", vbOKOnly + vbCritical, "Error"
                GoTo Tidy_Exit
            End If
        End With
    Next oBookMark
    oDoc.Fields.Update

 

[GeneralOb]

Thanks for responding Macropod. I’ve made the changes as instructed but unfortunately get the following error message:

Compile error:
Method or data member not found

After I click OK to make the error message go away the following text is automatically selected. I’ve tried following the code and comparing it to the original but cannot see why this part would be affected as it seems to be close enough to the original.

Any chance you could take another look?

Thanks

    For Each oBookMark In oDoc.Bookmarks
        With oBookMark
            Set objX = wsData.Rows(1).Find(.Name, LookIn:=xlValues, LookAt:=xlWhole)
            If Not objX Is Nothing Then
            ' found
                Set BmkRng = .Range
                With BmkRng
                    If Right(.[B][COLOR=#ff0000]Name[/COLOR][/B], 4) = "Date" Then
                        .Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
                    ElseIf Right(.Name, 6) = "Amount" Then
                        .Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
                    Else
                        .Text = wsData.Cells(rng2.Row, objX.Column)
                    End If
                End With
                oDoc.Bookmarks.Add .Name, BmkRng
            Else
                MsgBox "Bookmark '" & .Name & "' not found", vbOKOnly + vbCritical, "Error"
                GoTo Tidy_Exit
            End If
        End With
    Next oBookMark
    oDoc.Fields.Update

 

[Macropod]

Try:

    For Each oBookMark In oDoc.Bookmarks
        With oBookMark
            Set objX = wsData.Rows(1).Find(.Name, LookIn:=xlValues, LookAt:=xlWhole)
            If Not objX Is Nothing Then
            ' found
                Set BmkRng = .Range
                If Right(.Name, 4) = "Date" Then
                    BmkRng.Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
                ElseIf Right(.Name, 6) = "Amount" Then
                    BmkRng.Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
                Else
                    BmkRng.Text = wsData.Cells(rng2.Row, objX.Column)
                End If
                oDoc.Bookmarks.Add .Name, BmkRng
            Else
                MsgBox "Bookmark '" & .Name & "' not found", vbOKOnly + vbCritical, "Error"
                GoTo Tidy_Exit
            End If
        End With
    Next oBookMark
    oDoc.Fields.Update

 

[GeneralOb]

Hi Macropod

Still no luck. This time the error returned is:

Run-time error ‘5825’:
Object has been deleted.

When I hover over the .Name, the display message is:

.Name = Object has been deleted.

Thanks again.

    For Each oBookMark In oDoc.Bookmarks        With oBookMark
            Set objX = wsData.Rows(1).Find(.Name, LookIn:=xlValues, LookAt:=xlWhole)
            If Not objX Is Nothing Then
            ' found
                Set BmkRng = .Range
                If Right(.Name, 4) = "Date" Then
                    BmkRng.Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
                ElseIf Right(.Name, 6) = "Amount" Then
                    BmkRng.Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
                Else
                    BmkRng.Text = wsData.Cells(rng2.Row, objX.Column)
                End If
[COLOR=#ff0000]                oDoc.Bookmarks.Add [B].Name[/B], BmkRng[/COLOR]
            Else
                MsgBox "Bookmark '" & .Name & "' not found", vbOKOnly + vbCritical, "Error"
                GoTo Tidy_Exit
            End If
        End With
    Next oBookMark
    oDoc.Fields.Update

 

[Macropod]

Try :
1. Add a new variable - Dim StrBmk As String
2. Insert 'StrBmk = .Name' after 'Set BmkRng = .Range'
3. Change 'oDoc.Bookmarks.Add .Name, BmkRng' to 'oDoc.Bookmarks.Add StrBmk, BmkRng'

Memo to self: Don't try to help modify someone else's code without data for testing.

 

[GeneralOb]

Thanks Macropod

No error message this time but it seems that the loop just keeps on looping. I think it might be because the first oBookMark remains in the document and therefore keeps looping back on itself.

Any ideas of how we can cycle through all of the Bookmarks in the Word Template?

 

[Macropod]

You might need to use something like:

Dim i As Long
    For i = oDoc.Bookmarks.Count To 1 Step -1
        With oDoc.Bookmarks(i)
            Set objX = wsData.Rows(1).Find(.Name, LookIn:=xlValues, LookAt:=xlWhole)
            If Not objX Is Nothing Then
            ' found
                Set BmkRng = .Range
                If Right(.Name, 4) = "Date" Then
                    BmkRng.Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
                ElseIf Right(.Name, 6) = "Amount" Then
                    BmkRng.Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
                Else
                    BmkRng.Text = wsData.Cells(rng2.Row, objX.Column)
                End If
                oDoc.Bookmarks.Add .Name, BmkRng
            Else
                MsgBox "Bookmark '" & .Name & "' not found", vbOKOnly + vbCritical, "Error"
                GoTo Tidy_Exit
            End If
        End With
    Next

 

[GeneralOb]

Hi Macropod

Still no success and the worst part is that I'm not sure if I understand what is going on here!!

Sorry this is a long post. I’m just trying to add as much detail as possible as I get the feeling that it’s just a slight tweak/s that is required.

I’m looking to initially add format switches manually to the Bookmarks in the actual Word Template and hopefully once the code runs it will fill the Bookmarks in the document with data in the same format as the switches in the Word Template. I’ll also have Cross-references to the Bookmarks in the Word Template which will update with each of the values of the Word Bookmarks and ideally none of the Bookmark or Cross-reference fields will be present in the output document although it won’t matter too much if they are.

Some of the code in this loop had formatting instructions included so I’ve decided to remove those bits to make the code clearer.

After deleting out the formatting code this is what is left:

Dim i As Long

    For i = oDoc.Bookmarks.Count To 1 Step -1
        With oDoc.Bookmarks(i)
            Set objX = wsData.Rows(1).Find(.Name, LookIn:=xlValues, LookAt:=xlWhole)
            If Not objX Is Nothing Then
            ' found
                Set BmkRng = .Range
                   [COLOR=#008000]BmkRng.Text = wsData.Cells(rng2.Row, objX.Column)[/COLOR]
               [COLOR=#ff0000]oDoc.Bookmarks.Add .Name, BmkRng[/COLOR]
            Else
                MsgBox "Bookmark '" & .Name & "' not found", vbOKOnly + vbCritical, "Error"
                GoTo Tidy_Exit
            End If
        End With
    Next

As I step through the code line by line everything appears to be working great up to and including the green line.
If we assume:

· there are six Bookmarks in the Word Template
· the first Bookmark is Surname
· the first Surname in the Excel Data Sheet is Smith

then whilst I’m on the green line and directly before I step onto the red line we have the following values:

i  = 6
oDoc.Bookmarks.Count = 6
oDoc.Bookmarks(i) = 6
objX = Surname
.Name = Surname
BmkRng = Surname
.Range = Surname
BmkRng.Text = Surname

However once I step onto the red line the following values appear:

i  = 6
[B]oDoc.Bookmarks.Count = 5[/B]
oDoc.Bookmarks(i) = 6
objX = Surname
[B].Name = Object has been deleted.[/B]
[B]BmkRng = Smith[/B]
[B].Range = Object has been deleted.[/B]
[B]BmkRng.Text = Smith[/B]

The red line is also the last line I can run through. If I try to go onto the next line of code the following error message appears with the red line highlighted once I click Debug:

Run-time error ‘5825’:
Object has been deleted.

I assume that the following line replaces the entire Bookmark (Surname) in the Word Document with the Data Value (Smith):

[COLOR=#008000]BmkRng.Text = wsData.Cells(rng2.Row, objX.Column)[/COLOR]

Then on the following line when the same Bookmark (Surname) needs to be re-inserted, the code is unable to find the correct location to insert the Bookmark as it has been replaced with the Data Value (Smith):

[COLOR=#ff0000]oDoc.Bookmarks.Add .Name, BmkRng[/COLOR]

I think what might be required is that before each Bookmark (Surname) is replaced with the Data Value (Smith), all of the Cross-reference fields for that Bookmark (Surname) need to be located. Once all of the relevant Cross-reference fields have been found, the Bookmark (Surname) needs to be replaced with the Data Value (Smith) and then the Cross-reference fields somehow re-inserted or re-linked back to the Data Value (Smith)

This is my take on the issue but I’m quite new to all of this so it’s quite likely that I have gone wildly off on a tangent!

Either way if you’re able to help in any way it would be very much appreciated it. Also, thanks for your help so far and HAPPY NEW YEAR!!