Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How do I make a query from my datasource?
How do I make a query from my datasource (excel) and get the result in Word?
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How do I make a query from my datasource?
1. You can use Word's Edit recipients to do basic filtering and sorting.
Word generates a SQL query from your specifications and sends it to the source. It may not always work. NB, when you /select individual records/ in Edit Recipients, Word uses a different approach. 2. You can specify your own SQL code in Word VBA, either using the SQLStatement and SQLStatement1 parameters when you issue an OpenDataSource, or setting the value of ActiveDocument.mailMerge.DataSource.QueryString. Typically you would be using Jet SQL to do that. 3. You can use MS Query - if it is installed on your system - to define a data source, specify your Excel workbook, and define a query. In Word 2002 and later use the Tools menu in the "Select Data Source" dialog box to do that. (Windows only - you do not get that stuff on Mac office). However, Word can only connect using ODBC when you do that, which may create additional problems. Once you have set up your data source, Word opens the Excel file directly (or rather, via the ODBC driver/OLE DB provider) - it does not need to go via MS Query again (unless you save your query as a .dqy and use that as the data source). -- Peter Jamieson http://tips.pjmsn.me.uk "nph12" wrote in message news How do I make a query from my datasource (excel) and get the result in Word? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How do I make a query from my datasource?
1) How do I setup a VBA code that make a query to my excel file?
2) How do I make a query to my excel file by using the field code database? I don´t know how I write the code so please help me:-) "Peter Jamieson" wrote: 1. You can use Word's Edit recipients to do basic filtering and sorting. Word generates a SQL query from your specifications and sends it to the source. It may not always work. NB, when you /select individual records/ in Edit Recipients, Word uses a different approach. 2. You can specify your own SQL code in Word VBA, either using the SQLStatement and SQLStatement1 parameters when you issue an OpenDataSource, or setting the value of ActiveDocument.mailMerge.DataSource.QueryString. Typically you would be using Jet SQL to do that. 3. You can use MS Query - if it is installed on your system - to define a data source, specify your Excel workbook, and define a query. In Word 2002 and later use the Tools menu in the "Select Data Source" dialog box to do that. (Windows only - you do not get that stuff on Mac office). However, Word can only connect using ODBC when you do that, which may create additional problems. Once you have set up your data source, Word opens the Excel file directly (or rather, via the ODBC driver/OLE DB provider) - it does not need to go via MS Query again (unless you save your query as a .dqy and use that as the data source). -- Peter Jamieson http://tips.pjmsn.me.uk "nph12" wrote in message news How do I make a query from my datasource (excel) and get the result in Word? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How do I make a query from my datasource?
2) How do I make a query to my excel file by using the field code
database? If you have successfully inserted a DATABASE field that inserts data from an Excel worksheet, one thing you can do is use Alt-F9 to view the existing code. It will look something like this: { DATABASE \d "the full path name of your xls file with \\ separators" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=the full path name of your xls file with \\ separators;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT * FROM `Sheet1$`" \h } With Excel sources, you can remove that \c switch, leaving you with { DATABASE \d "the full path name of your xls file with \\ separators" \s "SELECT * FROM `Sheet1$`" \h } Then you need to modify the SELECT statement, select the field, press F9 to execute it, and if necessary, press Alt-F9 to show the results. Personally I prefer to use square brackets [ ] to surround anything that needs to be surrounded, as it is clearer than using ` ` Word can get very confused if you do not get the syntax of your SELECT exactly how Word expects it. For example, if you try SELECT fielda,fieldb FROM [Sheet1$] you may see a whole bunch of messages about the fields not being in the data source. This is nonsense, but to avoid it, you have to use SQL table alias names, e.g. SELECT s1.fielda,s1.fieldb FROM [Sheet1$] s1 1) How do I setup a VBA code that make a query to my excel file? Once you have worked out what SQL code you need (and the DATABASE field is quite a good way to experiment) you can try a simple VBA macro, e.g. Sub setsql() ActiveDocument.MailMerge.DataSource.QueryString = "SELECT s1.fielda,s1.fieldb FROM [Sheet1$] s1" End Sub This macro assumes that your document already has the correct workbook set up as a mail merge data source. To find out what to do with this macro, see e.g. http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm -- Peter Jamieson http://tips.pjmsn.me.uk "nph12" wrote in message ... 1) How do I setup a VBA code that make a query to my excel file? 2) How do I make a query to my excel file by using the field code database? I don´t know how I write the code so please help me:-) "Peter Jamieson" wrote: 1. You can use Word's Edit recipients to do basic filtering and sorting. Word generates a SQL query from your specifications and sends it to the source. It may not always work. NB, when you /select individual records/ in Edit Recipients, Word uses a different approach. 2. You can specify your own SQL code in Word VBA, either using the SQLStatement and SQLStatement1 parameters when you issue an OpenDataSource, or setting the value of ActiveDocument.mailMerge.DataSource.QueryString. Typically you would be using Jet SQL to do that. 3. You can use MS Query - if it is installed on your system - to define a data source, specify your Excel workbook, and define a query. In Word 2002 and later use the Tools menu in the "Select Data Source" dialog box to do that. (Windows only - you do not get that stuff on Mac office). However, Word can only connect using ODBC when you do that, which may create additional problems. Once you have set up your data source, Word opens the Excel file directly (or rather, via the ODBC driver/OLE DB provider) - it does not need to go via MS Query again (unless you save your query as a .dqy and use that as the data source). -- Peter Jamieson http://tips.pjmsn.me.uk "nph12" wrote in message news How do I make a query from my datasource (excel) and get the result in Word? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 2003 Merge with Sybase Database Query as datasource. | Mailmerge | |||
Shared DataSource | Mailmerge | |||
CHange query/datasource mail merge is bound to | Mailmerge | |||
URL as datasource | Mailmerge | |||
Refreshing query with MS Access datasource | Microsoft Word Help |