Solved: merge-Excel to Word Bookmarks

[lucas]

The attached is an excel file and an word template.

If you run the macro in the excel file it will take each rows info and place it in copies of the template at bookmarked positions.

All works well but would like some versatility added.

I have added code to column A of the excel sheet to click in that column to add a checkmark. It is actually a marlett small letter a.

Can we check to see if a row or mult rows has the small letter a in column A and just print those rows easily.

If that is easy to accomplish I would like to add the ability or option to save the files with the name of bookmark bm_1 or row B in the excel file(same thing) and save to the same location as the excel file.

This could be a completely seperate setup so that's all it does or if we could have the option that would be excellent.....option to save the files or just print.....


Input welcome........I've been working on this but have some time constraints but the need for this remains close to the front of other events at the same time..........in a little bind guess you could say.

[Dave]

Wasn't able to unzip your file but I was wondering if loading up your significant data rows into an array would help. You could then pick a blank area of your sheet as a print area and load whatever format you want from the array. As for saving the file to the workbook path, something like the following maybe? Good luck. Dave

Sub test()
Dim oWDBasic As Object, oWDDoc As Object, Rownum As Integer
Rownum = 1 ' set to whatever
On Error GoTo ErrFix
Set oWDBasic = CreateObject("Word.Application")
Set oWDDoc = oWDBasic.Documents.Open("C:\test.DOT")
'etc
oWDDoc.SaveAs ActiveWorkbook.Path & "\" & _
Sheets("Sheet1").Range("B" & rownum).Value & ".doc"
oWDDoc.Close savechanges:=False
oWDBasic.Quit
Set oWDBasic = Nothing
Set oWDDoc = Nothing
Exit Sub
ErrFix:
oWDBasic.Quit
Set oWDBasic = Nothing
Set oWDDoc = Nothing
End Sub

edit: fixed some saveas code

[TonyJollans]

The answer to your question is easy: use the same condition you check in the SelectionChange event to decide whether to create and print the document, viz:

For Each rCell In rngList

If rCell.Offset(0, -1).Font.Name = "Marlett" Then

Set oDoc = .Documents.Add(Template:=strFPath & "tpl.dot")

For iCol = 1 To 6
oDoc.Range(oDoc.Bookmarks("bm_" & iCol).Range.Start, _
oDoc.Bookmarks("bm_" & iCol).Range.End).Text _
= rCell.Offset(0, iCol - 1).Value

Next iCol

oDoc.PrintOut
oDoc.Close SaveChanges:=False

End If

Next rCell

Whether you should be using a mail merge is an open question - if the real thing is as straightforward as what you have posted - and you want to drive it from Excel - and you already have that working - then I wouldn't bother to change it. If it could get a whole lot more complex, it would be worth investigating.

Just mulling it over, if the end result you want is just the table as you have it in the Word template, could you not just use another sheet in your workbook and do the whole thing in Excel - you're not really making use of any Word features?

Adding a Save option is, again, straightforward - how would want the option? Per row (just add another 'checkbox' column)? Or per run (use a msgbox)?

[lucas]

Hi Tony,
I had actually worked out that part but I used a slightly different approach.....only in that I checked for the letter a instead of a font.name:

If rCell.Offset(0, -1).Value = "a" Then


The option to save the files is mostly for the purpose of saving ink while setting this up.

It could just be code that can be uncommented and the code to print commented during a maintenance cycle......changing the setup. and then changed back to print for actual use......


The table is irrelivant as I just want the ability to move the items to specific places in the document using the bookmarks. bm_1, etc.

[lucas]

What I'm trying to do, and this is not crutial, is to save each checked document as the persons name. ie its column B value or it's bm_1 text.

The exact same except instead of printing it just saves the docs for the purpose of confirming that the printing when it occurs will go smoothly.


PS. Thanks to you and Dave for looking at this with me. It helps to sort out priorities and direction when you have people to bounce ideas off of.

[lucas]

I have resolved all questions regarding this thread. Thanks for the help guys.

I am using this to save the documents instead of printing:

oDoc.SaveAs ActiveWorkbook.Path & "\" & rCell.Offset(0, 0).Value & ".doc"

[lucas]

Final files attached.

[TonyJollans]

That's quite enough posts for one night, Steve :)

[lucas]

It's early afternoon here Tony, I still have time to bloat this poor thread even more if you want to hear how my kids are doing......just kidding.

I get a little carried away trying to communicate with others especially when things are finally going my way......interpretation: I finally had time to address this.

Cheers.

[Dave]

lucas I'm happy that I was able to assist. I figured the part about setting up a designated print area might be a bit much. I messed up my previous code suggestion. I believe that it is important to release your objects correctly (ie. set them = nothing) and my previous post did not reflect this. I think the rule is to release the last object you opened first. So I hope that for posterity, and others, the following is better. Dave

Sub test()
Dim oWDBasic As Object, oWDDoc As Object, Rownum As Integer
Rownum = 1 ' set to whatever
On Error GoTo ErrFix
Set oWDBasic = CreateObject("Word.Application")
Set oWDDoc = oWDBasic.Documents.Open("C:\test.DOT")
'etc
oWDDoc.SaveAs ActiveWorkbook.Path & "\" & _
Sheets("Sheet1").Range("B" & Rownum).Value & ".doc"
oWDDoc.Close savechanges:=False
oWDBasic.Quit
Set oWDDoc = Nothing
Set oWDBasic = Nothing
Exit Sub
ErrFix:
oWDBasic.Quit
Set oWDDoc = Nothing
Set oWDBasic = Nothing
End Sub