Mail Merge with Excel
in [Word VBA]
|
Prev: Printing doc to Document Image Writer then displays in image viewe
Next: how do you get a file back if it was saved in a temporary file?
From: Jonathan on 27 Apr 2010 20:54 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
From: Peter Jamieson on 28 Apr 2010 03:10 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
From: Jonathan on 28 Apr 2010 16:44 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 "Peter Jamieson" wrote: > 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 > . >
From: Peter Jamieson on 28 Apr 2010 17:19 > 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 On 28/04/2010 21:44, Jonathan wrote: > 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 > > "Peter Jamieson" wrote: > >> 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 >> . >>
From: Jonathan on 28 Apr 2010 18:42
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 "Peter Jamieson" wrote: > > 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 > > On 28/04/2010 21:44, Jonathan wrote: > > 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 > > > > "Peter Jamieson" wrote: > > > >> 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 > >> . > >> > . > |