Solved: add additional data to word document's bookmark in excel

[RonNCmale]

I have a excel report that when I click the merge data places the information onto a word document. I'm trying to see if there is anyway to have additional information added to the word document. It now will list the total in any given section: Assaults; OtherAssaults; Incidents; and Force. I also wish it to have the corresponding report #. example on word document bookmark would look something like
Assaults: total 2 Report Number(s) 111, 118
Other Assaults: total 2 Report Number(s) 112, 117
Incidents: total 2 Report Number(s) 113, 116
Force: total 2 Report Number(s) 114, 115

Any ideas would be helpful!!!!

[Kenneth Hobs]

Can you post the DOT file?

[RonNCmale]

I can't seem to attach it. It is not showing up in the attachment so I can upload it. any suggestions?

[RonNCmale]

Got it. zipped

[macropod]

Cross-posted at: http://windowssecrets.com/forums/sho...-to-a-bookmark

Ron, for cross-posting etiquette, please read: http://www.excelguru.ca/node/7

As for the 'how to', you might consider using Word's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). To see how to group records with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://lounge.windowssecrets.com/ind...owtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

[Kenneth Hobs]

If you would name your column titles with something like InmateAssaultCol or InmateAssualt2 similar to your named ranges using a suffix like those or a prefix for the total range names, you can make the function more concise without the need to add more Cases.

[vba]Sub BCMerge()
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim TodayDate As String
Dim Path As String

Set wb = ActiveWorkbook
TodayDate = Format(Date, "mmmm d, yyyy")
Path = wb.Path & "\Monthly_Report.dot"

On Error GoTo ErrorHandler

'Create a new Word Session
Set pappWord = CreateObject("Word.Application")

On Error GoTo ErrorHandler

'Open document in word
Set docWord = pappWord.Documents.Add(Path)

'Loop through names in the activeworkbook
For Each xlName In wb.Names
'if xlName's name is existing in document then put the value in place of the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
'docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
docWord.Bookmarks(xlName.Name).Range.Text = iReport(xlName)
iReport xlName
End If
Next xlName

'Activate word and display document
With pappWord
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With

'Release the Word object to save memory and exit macro
ErrorExit:
Set pappWord = Nothing
Exit Sub

'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem"
If Not pappWord Is Nothing Then
pappWord.Quit False
End If
Resume ErrorExit
End If
End Sub

Function iReport(theName As Name)
Debug.Print theName.Name
Dim s As String, col As String
Dim cell As Range, rn As Long

Select Case theName.Name
Case "StaffAssault"
col = "E"
Case "InmateAssault"
col = "F"
Case "PREA"
col = "H"
Case "UOF"
col = "J"
Case Else
col = ""
End Select

If col = "" Or Range(theName.Name).Value = "" Then
iReport = ""
Exit Function
End If

s = "total " & Range(theName.Name).Value & " Report Number(s) "
rn = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range(col & "5", Range(col & rn))
If cell.Value = "" Then GoTo NextCell
s = s & Range("B" & cell.Row).Value & ", "
NextCell:
Next cell

s = Left(s, Len(s) - 2)
iReport = s
End Function

[/vba]

[RonNCmale]

After reading cross-posting etiquette, I truly apologize. And I would like to thank you K. Hobs for pointing it out. I also thank you for your time and solution to this problem.

[RonNCmale]

I thought this was solved but have run into another problem.

K. Hobs, your solution works as long as the merge data button is on the excel sheet you are merging to the word dot template. I have six tabs with info on all sheets that are merged to the word dot form: Is there a way you can add the tab name and then the name of the bookmark and column to your code? This way all information from all six tabs will be able to be merged to the dot file bookmarks.

Case "StaffAssault"
col = "E"
Case "InmateAssault'
col = "F"
etc:

[Kenneth Hobs]

Just add another button on each sheet or play the macro in other ways. e.g.

Add an ActiveX command button to a sheet and the code would be:

[VBA]Private Sub CommandButton1_Click()
BCMerge
End Sub[/VBA]

Of course if you have other bookmarknames with associated columns, just add Case lines to the Function.

[RonNCmale]

K. Hobs
I added another active x button and it would only load the bookmarks for that tab in excel. If you will I have uploaded excel with the dot file. I am hoping if you could place a button "Merge Data" on the last tab labeled Grievance, that will place the info in the appropriate bookmarks on the dot file or point me in the direction where I can read how to. Thanks in advance.

[Kenneth Hobs]

Now that other's see that you have additional needs, we can take this discussion off-forum. The solution for your 2nd example is more involved but has some similar elements. The comments below should help with your 2nd example.

What I try to do is to teach people how to do their projects and not do big projects for them. I prefer to teach concepts. Doing a whole project takes lots of time.

If you are still stuck after my comments below, send a completed MSWord file to me in email and I will work on it as I get time. I suspect that you have other requirements based on your first example. Most projects that place Excel data into an MSWord form file, at most just use a sheet of data to fill a one page MSWord form. The data is already in Excel rather than further processing being required as I did in the Function. If you color code an Excel file with the data placed into the MSWord file in the same color, that would help.

The first thing that I want to show you is how to get information about your Excel named ranges.

[vba]Sub NamesInfo()
Dim n As Name
On Error Resume Next
For Each n In ActiveWorkbook.Names
Debug.Print "Boomarkname: " & n.Name
Debug.Print "Value: " & n.Value
Debug.Print "Eval. Value: " & Evaluate(n.Value)
Debug.Print "Worksheet:" & Range(n.Name).Worksheet.Name
Debug.Print "Address:" & Range(n.Name).Address
Debug.Print vbCrLf
Next n
End Sub[/vba]
In VBE's Immediate window you will see that you have some named ranges with #Ref errors. Those will need to be resolved. For your project, it is probably best to use global Names and not local sheet named ranges.

There are two ways to set a range. (1) Activate the worksheet and use relative addressing. (2) Absolute addressing where the range can be found without activating that sheet. Absolute addressing is really just a more qualified range. They can be even more fully qualified or absolute so-to-speak if we also added the workbook reference. To illustrate these concepts for this example, I first added a Sheet to your first example where the range name of InmateAssault exists.

[vba]
Sub RelativeAddressing()
Dim n As Name, col As String, rn As Long

Set n = Range("InmateAssault").Name
Worksheets(Worksheets.Count).Activate 'Force activation of last worksheet.
Worksheets(Range(n.Name).Worksheet.Name).Activate 'Activate the sheet where the name exists.

col = "F"
rn = Range("B" & Rows.Count).End(xlUp).Row
MsgBox Range(col & "5", Range(col & rn)).Address, vbInformation, ActiveSheet.Name
End Sub

Sub AbsoluteAddressing()
Dim n As Name, col As String, ws As Worksheet, rn As Long

Set n = Range("InmateAssault").Name
Worksheets(Worksheets.Count).Activate 'Force activation of last worksheet.

Set ws = Range(n.Name).Worksheet
col = "F"
rn = ws.Range("B" & Rows.Count).End(xlUp).Row
MsgBox ws.Range(col & "5", ws.Range(col & rn)).Address & vbLf & _
"Notice that the active sheet name is: " & ActiveSheet.Name & vbCrLf & _
"and not the sheet name in the caption where the range exists " & vbLf & _
"for the named range, InmateAssault.", _
vbInformation, ws.Name
End Sub[/vba]
Now that you know how to use relative and absolute addressing, you can modify your code according to whichever method you like. I prefer to use absolute addressing normally. I used relative addressing for your first post as this usually meets most people's need.

[RonNCmale]

I still can not get the vba code to work, If anyone has any solution; vba code, formula, etc. Attached is the excel workbook and dot file and a word doc that is what the final result should look like when the merge data button is clicked. Any help would be greatly appreciated.

I have also posted here:
http://www.excelforum.com/excel-prog...-in-excel.html