How do I make a query from my datasource?

[nph12]

How do I make a query from my datasource (excel) and get the result in Word?

 

[Peter Jamieson]

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).

 

[nph12]

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]

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=\"\";User 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