View Full Version : mail merge help pulling #recipients from excel
Heliophobe
05-18-2005, 11:26 AM
In searching for mail merge automation I came across this site and found this example quite helpful as it seems to fit my needs. I will be calling it from an excel macro:
Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveDocument.Close
End Sub
Sub DoMerge()
ActiveWorkbook.FollowHyperlink Address:="C:\Atest\MPrint.doc", _
NewWindow:=True
End Sub
The only thing I need to change is the default LastRecord value to a value contained in a cell in the excel workbook sheet. I have a macro that filters the information based on user input and as a result the number of recipients varies. The cell in excel calculates the number of recipients. Using the default value prints numerous extra pages.
I know how to pull data from closed workbooks in excel but I can't seem to do it in word for some reason.
Any help would be appreciated.
MOS MASTER
05-18-2005, 11:54 AM
Hi and Welcome to VBAX! :hi:
Is this value in a cell or has the cell a named range?
You could use a recordset (DAO or ADO) to get that value from your Worksheet.
In this topic: http://www.vbaexpress.com/forum/showthread.php?t=2964
theres lot's of code that can get you started.
If you have a problem adapting it pleast post back.
Enjoy! :whistle:
Heliophobe
05-18-2005, 12:30 PM
this value is in a cell. "h5" on sheet6 to be exact. ( C:\Documents and Settings\mlong\desktop\manpower\1 manpower.xls )
I am 100% self taught through the help menus and recording macros and looking at what code was generated. Unfortunately I have no clue what DAO or ADO is. And this is my first attempt utilizing a macro in Word.
What References/Libraries do I need to check to get it working so I can step through and figure out what it's doing? I am using Office 2003 professional.
Thanks
MOS MASTER
05-18-2005, 01:04 PM
Hi, :D
Ah ok in that case I'll set you up!
Copy this code in to a module and set the reference as stated:
Option Explicit
Sub GetCellValue()
'Set Reference (Tools/reference) to:
'Microsoft ActiveX DataObjects 2.8 Library
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\mlong\desktop\manpower\1 manpower.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No"""
sql = "SELECT * From [sheet6$H5:H5]" 'Value of sheet6 Cel: H5
Set rst = New ADODB.Recordset
rst.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not IsNull(rst.Fields(0).Value) Then
MsgBox rst.Fields(0).Value
End If
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
End Sub
Now check the value of the path in the code with the path of your workbook.
Check the value where it says: [sheet6] Make shure the name is not sheet 6 (Space) and check the cell ref!
Well then I would say execute! :rofl:
I've added the full example that should run in your situation. The value returned can be used for your mailmerge sub. (That I let you play with first)
Enjoy! :whistle:
Heliophobe
05-18-2005, 02:13 PM
That did it. Works perfectly.
thank you so much.
MOS MASTER
05-18-2005, 02:15 PM
Hi, :yes
Great! You're Welcome! :beerchug:
Heliophobe
05-18-2005, 02:15 PM
trying to do it now...hehe. first post an all.
MOS MASTER
05-18-2005, 02:16 PM
trying to do it now...hehe. first post an all.
See the clue in my signature! :rofl:
Heliophobe
05-18-2005, 02:19 PM
I read that when you first posted. I've tried 5 times already. click the circle, click perform action. no change. sorry.
MOS MASTER
05-18-2005, 02:23 PM
Hi, :D
Ah ok no problem.
On the top of this thread you see the button: Thread Tools dropdown
Press it a submenu folds down.
In there is a radiobutton that's called: Mark thread solved.
Check this radiobutton and press the button Perform action.
If you have done this and it's still not working no worries than I'll ask a moderator to take care of it.
Till we meet again! :thumb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.