Yes, it's pretty annoying.
In the old days I did used to specify the filename of the data source at
runtime, but I guess that wouldn't make any difference now
Well, it may still make the difference. The KB article is a bit ambiguous
and does nto cover all the different ways in which a data source might be
opened. in essence, it talks about "opening a Word document that has a data
source attached" (either manually or using automation) and "A mail merge
main document that is opened by using Microsoft Visual Basic for
Applications(VBA) does not have the data source attached.", i.e. the
situation where you use automation to open a Word document and then use VBA
to attach a data source (I think!) - in this case, if you don't set up that
registry entry, Word will fail the OpenDataSource and the best you can do is
trap the error.
But you /may/ be OK if you
a. save the document with no data source attached
b. open the data source using Word VBA, e.g. in an autoopen macro in your
document.
When you do (a), what you lose is the data source and any sort/filter
settings. Precisely what you need to do depends on the data source but for a
file type such as a Word document or delimited text file it may be enough to
use
Sub autoopen()
ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\mypath\myds.doc"
End Sub
FWIW, you get the "SQL" message even with simple data sources such as plain
text files and Word files because Word does actually issue SQL for all types
of data source. In the case of data sources that Word reads using a file
converter (such as Word documents, and some text files), it uses a very
simple dialect of SQL implemented within Word. However, by default, Word
2002/3 will try to open some delimited text files using the Access/Jet OLE
DB provider (ACE OLE DB provider in Office 2007) which implmenets the full
Jet SQL dialect.
So, if you need to recover sort/filter options, you need to construct and
issue the correct SQL. You can usually discover what that is by looking at
ActiveDocument.MailMerge.DataSource.QueryString
while the data source is attached. You can put it in the OpenDataSource via,
something like
Sub autoopen()
ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\mypath\myds.doc", _
SQLStatement:="SELECT * FROM C:\mypath\myds.doc WHERE (myfield = 'A')"
End Sub
I can't guarantee that it will work - it seems a bit arbitrary to me - but
probably worth a try.
There is another approach that relies on a trick where you save your mail
merge main document as a .rtf file, then manually remove the SQL statement.
For more on that, see the discussion at
http://groups.google.co.uk/group/mic...be83d2b1593016
(you may need to paste that URL back together).
AFAICR it can only be made to work with certain types of data source, and is
vulnerable to further security-related changes made by Microsoft.
The other alternative is to write VBA that grabs the data using whatever
method is appropriate (e.g. reading traditional text files, automation, ADO)
and stuffs it directly into the table cells of your label layout.
Peter Jamieson
"Mike" S wrote in message
...
Is there an alternative to mailmerge, such as templates for building up
pages manually for L7163 address labels (7 rows of 2 labels)
I ask this because I am migrating from Office 2000 to 2003 and am getting
the message running an SQL statement. I know there's a Microsoft KB
document for this kb825765) which tells you to add a registry key to allow
the SQL to run uninterrupted, but this seems to defeat the whole object of
me trying to use digital certificates etc and what about any new document
that may arrive on my computer (e.g. family members downloading etc). I
want to have more control of what I let Microsoft does to my PC
Incidentally, I am not running SQL, but my mail merge data is a flat file
of data records, but I guess that doesn't matter. The mailmerge main
document has the datasource etc all embedded into it, so it runs every
time I open the document, even if I disable the macros. In the old days I
did used to specify the filename of the data source at runtime, but I
guess that wouldn't make any difference now
I use the following code in my mailmerge main document which worked nicely
in 2000 and since I call this several times from an excel spreadsheet,
having the SQL message pop up twice for each document is going to annoy me
Private Sub Document_Open()
MailMerge.Execute
'Set properties of created mail merge, so the 'do you want to
' save' message doesn't appear
ActiveDocument.Saved = True
'Close the mail merge main document
ThisDocument.Close True
End Sub
--
Mike News