SELECT UNIQUE some_column FROM MAILMERGE.DATASOURCE

[Lüko Willms]

The MailMerge.Datasource for my MS-Word mailmerge is an Excel-Table,
and the first column in that table contains a group indicator. Rarely
all addresses in the table are to be processed, but they are to be
selected according to the group indicator, excluded or included.

Unfortunately the dialogs both of MS-Word as well as Excel do not
provide enough options to present all the group indicators.

So I would like to build a MS-Userform with a dynamically built
table of RadioButtons for each group indicator found in the Excel
table, but I am not sure how to approach a solution for that problem.
In pure SQL, I would submit a SELECT UNIQUE column_1 FROM the
MailMerge.Datasource, but I don't know if this can be done without
confusing MS-Word's Mailmerge process, and also I don't know if Excel
does not have some home-brew methods to achieve the same result, for
which I would call the Excel-Application to do the work for my MS-Word
VBA program.

Any hints on where to go from all thos most valued experts?


Yours,
L.W.

 

[Peter Jamieson]

<<

In pure SQL, I would submit a SELECT UNIQUE column_1 FROM the
MailMerge.Datasource

You should be able to issue a SELECT DISTINCT (if SELECT UNIQUE is something
else, let me know) using OpenDataSource when the data source is an Excel
worksheet, but I think you may need to provide a table alias to get the Jet
OLEDB provider to process the request properly, e.g. try

SELECT DISTINCT m.mycolumn FROM `Sheet1$` m

rather than

SELECT DISTINCT mycolumn FROM `Sheet1$`

<<
without confusing MS-Word's Mailmerge process,
it is so easy to confuse Word's mailmerge process that it is almost
inevitable that you will do so. The three most likely reasons are:
a. the Jet OLEDB provider/ODBC driver does not return the data you expect
(e.g. truncates memo fields in Excel). I suspect you already know about
that.
b. Word cannot interpret the SQL you provide when the end user tries to
change it in the Mail Merge recipients box, because Word can only deal with
the 5 WHERE conditions its dialog supports, and so on
c. Word fails to reconnect to the data source for a number of reasons (in
particular, it won't connect if the workbook is already open)

As a general rule, if you can create the kind of simple data source that
Word really expects to see, things will probably be easier. However, that
wil have to be a separate step, unless you are using DDE to connect to
Excel, in which case you might be able to do all sorts of stuff in Excel VBA
when Excel opens the data source.

and also I don't know if Excel
does not have some home-brew methods to achieve the same result, for
which I would call the Excel-Application to do the work for my MS-Word
VBA program.

I don't think there's anything really slick you could do in Excel to help.
If you were talking about marking a single contiguous block of Excel cells,
you might be able to retrieve your data from a named range, but you're not
doing that.

Peter Jamieson


When Word Generally speaking

 

[Lüko Willms]

I don't think there's anything really slick you could do in Excel to help.

Thanks a lot for all your comments.

Well, I try to abuse Excel as a database system, because at that
place where I am, I have only Excel and Word available. Just testing
out the limits...

because Word can only deal with the 5 WHERE conditions its dialog supports, and so on

ah! And I thought I could overcome that limitation which I imagined
to be only in the dialog, not in Word's SQL client.

If you were talking about marking a single contiguous block of Excel cells,
you might be able to retrieve your data from a named range, but you're not
doing that.

Maybe I should try that. Working on the Excel sheet thru VBA, using
the Excel Application-Object.


Yours,
L.W.

 

[Peter Jamieson]

because Word can only deal with the 5 WHERE conditions its dialog

ah! And I thought I could overcome that limitation which I imagined
to be only in the dialog, not in Word's SQL client.

Yes, AFAIK the limitation is only in the dialog box - i.e. you can issue the
SQL, and it should work, but my comment was really related to your point
about

"but I don't know if this can be done without
confusing MS-Word's Mailmerge process"

because as soon as you start letting the user change the conditions, or
save/re-open Word documents with data sources attached, Word /may/ object.
So it depends on what you consider to be part of Word's Mailmerge process.

FWIW, when connecting to a data source, Word uses the SQL interpreter
associated with that data source. If the data source is a Word document, or
something that Word would typically read with an internal converter (e.g.
..rtf) or external converter (e.g. the old Excel converter), Word uses a very
simple internal SQL dialect which basically reflects the capabilities of the
Query Options dialog in the Insert Database function (i.e. you can select
columns and do a small number of filters and sorts). When Word connects to
Excel via OLEDB or ODBC, it uses the Jet Engine's SQL interpreter.

Maybe I should try that. Working on the Excel sheet thru VBA, using
the Excel Application-Object.

From the SQL point of view that should work, but only when the range has
column headers - in essence you do a SELECT * FROM `rangename` . Also, I
think "rangename" has to be a range /name/ - I don't think anything like
FROM `Sheet1!R1C1:R3C3` or `Sheet1:A1:C3` works.

It seems to me that Jet SQL will be enough to get the right records and
columns, but it is more difficult to overcome the problems it has with
variable data types in Excel.

Peter Jamieson

 

[Lüko Willms]

Am Wed, 15 Nov 2006 17:44:42 UTC, schrieb "Peter Jamieson"
<[email protected]> auf
microsoft.public.word.vba.general :

Thanks again for all those explanations.

"but I don't know if this can be done without
confusing MS-Word's Mailmerge process"

because as soon as you start letting the user change the conditions, or
save/re-open Word documents with data sources attached, Word /may/ object.
So it depends on what you consider to be part of Word's Mailmerge process.

I think that I have to open the Excel sheet which is opened by
Winword as MailMerge.DataSource, again as a simple database, and that
this double connection could create problems.


Yours,
L.W.

 

[Peter Jamieson]

Yes,
1. I do not think you will be able to open an Excel sheet as a datasource
/and/ in Excel (which you would need to do to use Excel's object model
2. you can open an Excel sheet as a datasource /and/ via ADO, but
a. I would consider modifying user-maintained Excel data via ADO as
potentially "dangerous".
b. you might find you needed to reconnect to the data source anyway to
see anything such as range names that you added.

I've rather lost the plot on what you're trying to achieve, but it seems to
me that as long as you have all the necessary information for connecting to
the Excel data, you should be able to open/close the sheet using the
MailMerge object, ADO, or the Excel object without the user noticing.

Peter Jamieson

 

[Peter Jamieson]

From the SQL point of view that should work, but only when the range has

column headers - in essence you do a SELECT * FROM `rangename` . Also, I
think "rangename" has to be a range /name/ - I don't think anything like
FROM `Sheet1!R1C1:R3C3` or `Sheet1:A1:C3` works.

FWIW, I hadn't tried one of the obvious syntax options:

SELECT * FROM `Sheet1$A1:C3`

opens an unnamed range of cells.

Peter Jamieson