PDA

View Full Version : Print records after filtering in mail merge from excel to word



mihaibanro
12-09-2009, 03:09 AM
Hello, I am new in this forum.

I have a problem with a macro that I want to build. I search on internet and didn't find anything to help (I am also relatively new to VBA).
I want to mail merge from an excel data base to a form that I have made in Word (I use Microsoft Office 2007) and after that when I push a buton I want to print all record that contains in one merge field a certain value. I have made a macro that search every record and compare the value from the merge field with one that I want and give the result "OK" or "Not OK", but because I have a lot of record it works very slow (I know it because I have change the macro with that prints the records or skip the one that not meet the criteria).

ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord
NrInregistrari = ActiveDocument.MailMerge.DataSource.ActiveRecord
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
For i = 1 To NrInregistrari
If InStr(1, ActiveDocument.MailMerge.DataSource.DataFields("Tip_medicament").Value, "T") > 0 Then MsgBox ("ok") Else MsgBox ("not ok")
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
Next i

Also, I think that is not the correct answer because when I sent to printer the records make different print jobs....I have tried to record macro and filter records that meet a criteria but when I have stop the record I didn't see anything in the macro....


Please anyone can help me....
Thanks

mdmackillop
12-09-2009, 06:39 AM
If you are not doing all this from Word, I would suggest you do so, as it is much simpler. You can still use Excel as your database. Your question would then be better asked in the Word forum.

mihaibanro
12-09-2009, 06:51 AM
In the excel there are some cell that must be filled and into another 2 sheets are some formulas to complete the database. I have a macro that prepares 2 sheets with formulas and from these 2 sheets is taken the data for mail merge in word. I want that someone else (i know how to do mail merge and it is easy for me) to print records that meet a criteria in a field merge just by pressing a button in excel and not to open the word files.
Thanks

geekgirlau
12-09-2009, 04:38 PM
Why not just set a filter on the mail merge?


Dim strQry As String


strQry = "SELECT * FROM 'SheetName$' WHERE 'Tip_medicament'= 'T'"

With ActiveDocument.MailMerge
.DataSource.QueryString = strQry

.Destination = wdSendToNewDocument
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With