[Markus]

Many thanks for all the details. I've given up on trying to get a dbf to
work with the merge, but this option is working now.

I also will be trying this out with Word 07, which I was having problems
with when using a dbf datasource.

Mark


"Peter Jamieson" wrote:

Markus,

(I also saw your other post).

If you have to leave the selection of the .xls up to the user, Doug's
method should be fine if you definitely know that either there is only
one sheet, or that clicking through the dialog box will make the correct
selection (probably the first sheet) if there is more than one sheet.

Otherwise, you have to know both the pathname of the workbook /and/ the
sheet name. Let's suppose it is "sheet1". Then you can do it like this:


strTableName = "Sheet1$"
ActiveDocument.MailMerge.OpenDataSource _
Name:=strPathName, _
SQLStatement:="SELECT * FROM [" & strTableName & "]"

If you know the workbook pathname but not the sheet name, AFAIK you have
to discover the sheet name. You could either do that by automating
Excel, or possibly rather more easily by using ADO and ADOX, e.g. as
follows (I've left some stuff in that should let you combine it with
Doug's code if necessary)

Sub GetExcelTablenameAndConnect(PathName as String)
' Pathname is the pathname of the workbook
' I am assuming it is a .xls, not a .xlsx
' or .xlsm, as you will probably then have
' to close the workbook before doing this
Dim objConnection As ADODB.Connection
Dim objCatalog As ADOX.Catalog
Dim objTable As ADOX.Table
Dim strDataSource As String
Dim strExtensions As String
Dim strProvider As String
Dim strTableName As String

' comment out one of the following

'strProvider = "Microsoft.Jet.OLEDB.4.0;"
' the user probably has this if they
' have Office 2007 or later.
strProvider = "Microsoft.ACE.OLEDB.12.0;"


' Now open a connection to the selected file
Set objConnection = New ADODB.Connection
' Actually I think ACE uses type 37 for
' .xlsx, but have never found an up-to-date
' list of these values

objConnection.Open _
"Provider=" & _
strProvider & _
";Data Source=" & _
PathName & _
";Mode=Read;Jet OLEDB:Engine Type=35;"

Set objCatalog = New ADOX.Catalog
objCatalog.ActiveConnection = objConnection
If objCatalog.Tables.Count = 1 Then
' use opendatasource
strTableName = objCatalog.Tables(0).Name
Set objCatalog = Nothing
objConnection.Close
Set objConnection = Nothing
' below line only works in recent versions of Word
'ActiveDocument.MailMerge.DataSource.Close
ActiveDocument.MailMerge.OpenDataSource _
Name:=strDataSource, _
sqlstatement:="SELECT * FROM [" & strTableName & "]"
Else
Set objCatalog = Nothing
objConnection.Close
Set objConnection = Nothing
'SendKeys "{Enter}"
ActiveDocument.MailMerge.OpenDataSource _
Name:=PathName
End If
End Sub

BTW, I agree that trying to get Mail merge connections to work can be
very hard work. Microsoft clearly tried to wrap up many of the common
difficulties by creating an "Office Data Source Object" to make data
source connections, but in many cases it just gets in the way.
Personally I would rather the Word Object Model had been improved to
allow us to do what the ODSO does, but it's quite clear that Microsoft's
perception is that anything that cannot be made to work fairly easily
should be programmed using .NET/VSTO and would have to avoid using the
built-in MailMerge facilities (because if you use them, you cannot avoid
using the OpenDataSource method).

Peter Jamieson

http://tips.pjmsn.me.uk

On 01/01/2010 23:53, Markus wrote:
I would like to use Excel as the datasource for a merge. When I do, the user
is presented with a dialog box asking them to pick a Sheet, even tho the .xls
file has only one sheet in it.

Could someone help me withthe syntax that skips this dialog, or is it not
possible?

Many thanks,
Mark
.