mail merge help pulling #recipients from excel

[Heliophobe]

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]

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]

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]

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]

That did it. Works perfectly.

thank you so much.

[MOS MASTER]

Hi, :yes
Great! You're Welcome! :beerchug:

[Heliophobe]

trying to do it now...hehe. first post an all.

[MOS MASTER]

trying to do it now...hehe. first post an all.
See the clue in my signature! :rofl:

[Heliophobe]

I read that when you first posted. I've tried 5 times already. click the circle, click perform action. no change. sorry.

[MOS MASTER]

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