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: Peter Jamieson on 28 Apr 2010 19:51 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 On 28/04/2010 23:42, Jonathan wrote: > 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 >>>> . >>>> >> . >> |