PDA

View Full Version : Problems with merge fields



rodtt
07-15-2013, 08:08 AM
Hi to all!

I opened a thread with this title on word forum, but after some tests, I think the problem is happening in excel vba code.

I'm in the process of finishing an excel file, but I'm having a difficult time with mail merge on word. I have a macro on excel to open my word document, but there's a problem. There are 3 options that happen, as following:

1) Everytime I open this word document through the macro, I have to setup mail merge once again.

2) When I directly open this word document, with my excel file closed, I get the message that data from database will be replaced on word, with the option to select yes, no or help, and selecting yes does the work fine.

3) When I open directly my word document, but with excel file also opened, I get the message that data from database will be replaced on word, as on option 2, but after selecting yes, I get another message, that this word document have links that may refer to other files and it asks me if I want to update with data from the linked files. Chosing yes or no here doesn't seem to make any difference.

I'll put the excel code to open this word document here, to see if I have to make anything differently.

Sub OpenWordFile()
Set wordapp = CreateObject("word.Application")

wordapp.documents.Open "C:\Users\rodtt\Desktop\SES\SERVIÇO PUBLICO ESTADUAL.docx"

wordapp.Visible = True
End Sub

Thanks!

snb
07-15-2013, 08:18 AM
I'd use:


Sub OpenWordFile()
getobject "C:\Users\rodtt\Desktop\SES\SERVIÇO PUBLICO ESTADUAL.docx"
End Sub

rodtt
07-15-2013, 08:41 AM
Thanks for your fast answer, snb!

It solved the problem, but now I'm getting the message that data from database will be replaced on word, while the word document it self doesn't show up, even after clicking yes.

Since we are on the topic, what I really want the macro to do is to automate the whole process:

1) Open word doc.
2) Select it (because in my code it opens word doc, but doesn't select it).
3) Print it.
4) Close it.

It would be even better if it didn't show up at all, just updated and printed it.

snb
07-15-2013, 09:28 AM
what if


Sub OpenWordFile()
with getobject("C:\Users\rodtt\Desktop\SES\SERVIÇO PUBLICO ESTADUAL.docx")
.mailmerge.execute
.application.activedocument.printout false
.application.activedocument.close 0
.close 0
End with
End Sub

rodtt
07-15-2013, 11:03 AM
what if


Sub OpenWordFile()
with getobject("C:\Users\rodtt\Desktop\SES\SERVIÇO PUBLICO ESTADUAL.docx")
.mailmerge.execute
.application.activedocument.printout false
.application.activedocument.close 0
.close 0
End with
End Sub


Thanks again, snb!

It automaticaly started the process of printing it. I can't say I'm 100% sure it works fine, since I currently don't have a printer at home, but I set it up to create a pdf file, and it worked nice.

There's only a minor tweak I wanted. After running your code, it opens that window asking me if I want to update my word doc, but doesn't prompt me there. I have to manually select it through alt+tab and click yes. It would be nice if the user didn't have to do anything after running the macro, or, at least, that it directly prompted the user to the window to confirm the update to the merge fields.

Also forgot to say, if it also saved the updated file, it would be very helpful, in case print fails (what happens all the time in my experience).

Sorry to keep asking things up!

snb
07-15-2013, 12:58 PM
Sub OpenWordFile()
With getobject("C:\Users\rodtt\Desktop\SES\SERVIÇO PUBLICO ESTADUAL.docx")
.mailmerge.execute
.application.activedocument.printout False
.application.activedocument.close 0
.saveas2 "G:\OF\reserve 001.docx",12
.close 0
End With
End Sub

rodtt
07-15-2013, 02:34 PM
Thanks again!

It's saving the file, but that window to confirm merge is still openning, and I'm not getting prompted there. Actually, a few times it printed without openning this window, but I don't know why nor how to reproduce it.

Also, there's a problem I didn't preview. This form, besides merge fields, has a link to a spreadsheet and it's not updating itself when I use the macro. It must need to open word doc to update it. If so, can I update it through excel macro?

snb
07-16-2013, 03:28 AM
Sub OpenWordFile()
With getobject("C:\Users\rodtt\Desktop\SES\SERVIÇO PUBLICO ESTADUAL.docx")
.fields.update
.mailmerge.execute
.application.activedocument.printout False
.application.activedocument.close 0
.saveas2 "G:\OF\reserve 001.docx",12
.close 0
End With
End Sub

rodtt
07-17-2013, 10:13 AM
Thanks, snb, this was very helpful! Sad this forum doesn't have a karma button, so I could give you some.

I still have to manually select this window and confirm merge before it starts printing, but I guess this is ok for now. Everything else is working fine.

I'm opening another thread to solve another specific problem.

Again, thanks!