Mail Merge with Excel

[Jonathan]

Hi I am using MS Access 2003 to run the following

mergeDocument.MailMerge.OpenDataSource FullNameDataSourceFile, , False,
True, True, False

However in Word a Select Table dialog is displayed. How do have the range
default to the first sheet so that this dialog is not displayed?

Any ideas appreciated

Many thanks,
Jonathan

 

[Peter Jamieson]

Unfortunately, you have to know the sheet name. Let's assume it is
"mysheet". Then use

dim strSheetName As String
strSheetName = "mysheet"
mergeDocument.MailMerge _
Name:=fullNameDataSourceFile, _
SQLStatement:="SELECT * FROM [" _
& strSheetName & "$]"

(notice the "$" at the end of the name).

If you don't know the sheet name, I think you would probably have to
automate Excel, or perhaps use ADO to discover the name first - i.e. I
don't know of a way to specify "the first sheet regardless of its name"

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Jonathan]

Awesome, thanks Peter. Actually I'm running this from MS Access and the first
sheet has the name of the table/query that is the source of the Excel data.

Jonathan

Unfortunately, you have to know the sheet name. Let's assume it is
"mysheet". Then use

dim strSheetName As String
strSheetName = "mysheet"
mergeDocument.MailMerge _
Name:=fullNameDataSourceFile, _
SQLStatement:="SELECT * FROM [" _
& strSheetName & "$]"

(notice the "$" at the end of the name).

If you don't know the sheet name, I think you would probably have to
automate Excel, or perhaps use ADO to discover the name first - i.e. I
don't know of a way to specify "the first sheet regardless of its name"

Peter Jamieson

http://tips.pjmsn.me.uk

Hi I am using MS Access 2003 to run the following

mergeDocument.MailMerge.OpenDataSource FullNameDataSourceFile, , False,
True, True, False

However in Word a Select Table dialog is displayed. How do have the range
default to the first sheet so that this dialog is not displayed?

Any ideas appreciated

Many thanks,
Jonathan

.

 

[Peter Jamieson]

Awesome, thanks Peter. Actually I'm running this from MS Access and
the first

sheet has the name of the table/query that is the source of the Excel

data.

Sorry, that looked like a circular reference to me

If you have what you need, fine - if all you have is the name of a
workbook, then you still need to discover what the name of the first
sheet is - if that is still the problem, ADO is still a possible way to
discover the name; DAO might be a better bet from Access but I don't
know what you can discover via the dAO interface.

Peter Jamieson

http://tips.pjmsn.me.uk

Awesome, thanks Peter. Actually I'm running this from MS Access and the first
sheet has the name of the table/query that is the source of the Excel data.

Jonathan

Unfortunately, you have to know the sheet name. Let's assume it is
"mysheet". Then use

dim strSheetName As String
strSheetName = "mysheet"
mergeDocument.MailMerge _
Name:=fullNameDataSourceFile, _
SQLStatement:="SELECT * FROM [" _
& strSheetName& "$]"

(notice the "$" at the end of the name).

If you don't know the sheet name, I think you would probably have to
automate Excel, or perhaps use ADO to discover the name first - i.e. I
don't know of a way to specify "the first sheet regardless of its name"

Peter Jamieson

http://tips.pjmsn.me.uk

Hi I am using MS Access 2003 to run the following

mergeDocument.MailMerge.OpenDataSource FullNameDataSourceFile, , False,
True, True, False

However in Word a Select Table dialog is displayed. How do have the range
default to the first sheet so that this dialog is not displayed?

Any ideas appreciated

Many thanks,
Jonathan

.

 

[Jonathan]

It's all good Peter, there's no circular reference. In MS Access I use the
following to create an excel workbook data source...

DoCmd.OutputTo acOutputQuery, sourceQuery, acSpreadsheetTypeExcel9,
ExcelDataSourceName

Then using vba to open/create word template and the following to link to the
excel data source...

wordTemplate.MailMerge.OpenDataSource ExcelDataSourceName, , False, True,
True, False, , , , , , , _
"SELECT * FROM [Data$]"

and it all seems to work nicely thanks to your input.

However, should I not know the name of the first work sheet I can always
programatically open the workbook to get the name.

Many thanks,
Jonathan

Awesome, thanks Peter. Actually I'm running this from MS Access and the first
sheet has the name of the table/query that is the source of the Excel

data.

Sorry, that looked like a circular reference to me

If you have what you need, fine - if all you have is the name of a
workbook, then you still need to discover what the name of the first
sheet is - if that is still the problem, ADO is still a possible way to
discover the name; DAO might be a better bet from Access but I don't
know what you can discover via the dAO interface.

Peter Jamieson

http://tips.pjmsn.me.uk

Awesome, thanks Peter. Actually I'm running this from MS Access and the first
sheet has the name of the table/query that is the source of the Excel data.

Jonathan

Unfortunately, you have to know the sheet name. Let's assume it is
"mysheet". Then use

dim strSheetName As String
strSheetName = "mysheet"
mergeDocument.MailMerge _
Name:=fullNameDataSourceFile, _
SQLStatement:="SELECT * FROM [" _
& strSheetName& "$]"

(notice the "$" at the end of the name).

If you don't know the sheet name, I think you would probably have to
automate Excel, or perhaps use ADO to discover the name first - i.e. I
don't know of a way to specify "the first sheet regardless of its name"

Peter Jamieson

http://tips.pjmsn.me.uk

On 28/04/2010 01:54, Jonathan wrote:
Hi I am using MS Access 2003 to run the following

mergeDocument.MailMerge.OpenDataSource FullNameDataSourceFile, , False,
True, True, False

However in Word a Select Table dialog is displayed. How do have the range
default to the first sheet so that this dialog is not displayed?

Any ideas appreciated

Many thanks,
Jonathan
.

.

 

[Peter Jamieson]

Hi Jonathan,

Glad it's all OK.

I just wasn't quite sure whether you had actually solved it the last
time, but now it's clear that you have the name you need

Peter Jamieson

http://tips.pjmsn.me.uk

It's all good Peter, there's no circular reference. In MS Access I use the
following to create an excel workbook data source...

DoCmd.OutputTo acOutputQuery, sourceQuery, acSpreadsheetTypeExcel9,
ExcelDataSourceName

Then using vba to open/create word template and the following to link to the
excel data source...

wordTemplate.MailMerge.OpenDataSource ExcelDataSourceName, , False, True,
True, False, , , , , , , _
"SELECT * FROM [Data$]"

and it all seems to work nicely thanks to your input.

However, should I not know the name of the first work sheet I can always
programatically open the workbook to get the name.

Many thanks,
Jonathan

Awesome, thanks Peter. Actually I'm running this from MS Access and the first
sheet has the name of the table/query that is the source of the Excel

data.

Sorry, that looked like a circular reference to me

If you have what you need, fine - if all you have is the name of a
workbook, then you still need to discover what the name of the first
sheet is - if that is still the problem, ADO is still a possible way to
discover the name; DAO might be a better bet from Access but I don't
know what you can discover via the dAO interface.

Peter Jamieson

http://tips.pjmsn.me.uk

Awesome, thanks Peter. Actually I'm running this from MS Access and the first
sheet has the name of the table/query that is the source of the Excel data.

Jonathan

:

Unfortunately, you have to know the sheet name. Let's assume it is
"mysheet". Then use

dim strSheetName As String
strSheetName = "mysheet"
mergeDocument.MailMerge _
Name:=fullNameDataSourceFile, _
SQLStatement:="SELECT * FROM [" _
& strSheetName& "$]"

(notice the "$" at the end of the name).

If you don't know the sheet name, I think you would probably have to
automate Excel, or perhaps use ADO to discover the name first - i.e. I
don't know of a way to specify "the first sheet regardless of its name"

Peter Jamieson

http://tips.pjmsn.me.uk

On 28/04/2010 01:54, Jonathan wrote:
Hi I am using MS Access 2003 to run the following

mergeDocument.MailMerge.OpenDataSource FullNameDataSourceFile, , False,
True, True, False

However in Word a Select Table dialog is displayed. How do have the range
default to the first sheet so that this dialog is not displayed?

Any ideas appreciated

Many thanks,
Jonathan
.

.