Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Markus Markus is offline
external usenet poster
 
Posts: 37
Default Using Excel as datasource for merge

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
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Using Excel as datasource for merge

Running a macro with the following code would avoid that dialog from being
shown:

Dim fd As FileDialog
Dim strDataSource As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Title = "Select the Data Source"
fd.InitialFileName = Environ("HomePath") & "\Documents\My Data Sources\"
fd.Filters.Add "Excel", "*.xls", 1
Dim vrtSelectedItem As Variant
With fd
If .Show = -1 Then
strDataSource = .SelectedItems(1)
Else
MsgBox "You have not selected a data source."
Set fd = Nothing
Exit Sub
End If
End With
Set fd = Nothing
SendKeys "{Enter}"
ActiveDocument.MailMerge.OpenDataSource (strDataSource)


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Markus" wrote in message
...
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


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Using Excel as datasource for merge

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

  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Markus Markus is offline
external usenet poster
 
Posts: 37
Default Using Excel as datasource for merge

Thanks Doug. This was helpful.
Mark


"Doug Robbins - Word MVP" wrote:

Running a macro with the following code would avoid that dialog from being
shown:

Dim fd As FileDialog
Dim strDataSource As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Title = "Select the Data Source"
fd.InitialFileName = Environ("HomePath") & "\Documents\My Data Sources\"
fd.Filters.Add "Excel", "*.xls", 1
Dim vrtSelectedItem As Variant
With fd
If .Show = -1 Then
strDataSource = .SelectedItems(1)
Else
MsgBox "You have not selected a data source."
Set fd = Nothing
Exit Sub
End If
End With
Set fd = Nothing
SendKeys "{Enter}"
ActiveDocument.MailMerge.OpenDataSource (strDataSource)


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Markus" wrote in message
...
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


.

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Markus Markus is offline
external usenet poster
 
Posts: 37
Default Using Excel as datasource for merge

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

.

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge and Excel Datasource Erika Mailmerge 1 November 23rd 06 06:35 PM
mail merge with excel datasource Deb H Mailmerge 1 February 15th 06 04:23 AM
Editing an Excel DataSource Sue Hughes Mailmerge 1 January 5th 06 11:54 AM
Excel datasource [email protected] Mailmerge 1 November 14th 05 09:10 PM
Formatting using Excel datasource for mail merge Shawna Mailmerge 1 February 26th 05 01:19 AM


All times are GMT +1. The time now is 01:19 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"