[Mike[_9_]]

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

[Graham Mayor]

There's no alternative to using merge if merge is what you are doing. You
will either have to put up with the SQL message or suppress it via the
registry hack.
If you want to manually type labels, simply create a new document from the
envelope/label tool.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Mike wrote:
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

[Peter Jamieson]

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

[Mike[_9_]]

In message
at 10:12:12 on Wed, 17 Oct 2007, Peter Jamieson
wrote
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.

Pete,
Thanks for this - I will digest the information in a while. I didn't
want you to think I was ignoring you whilst posting my other reply
--
Mike News

[Mike[_9_]]

In message
at 08:22:05 on Wed, 17 Oct 2007, Graham Mayor
wrote
There's no alternative to using merge if merge is what you are doing. You
will either have to put up with the SQL message or suppress it via the
registry hack.
If you want to manually type labels, simply create a new document from the
envelope/label tool.

If I have a sheet of Avery Labels, I could possibly create a template
which looks like the MM main document and then do search and replace.
This becomes tricky then if I need more than 14 labels (i.e. more than
one page)

If I can set the registry from excel VBA then I will be happy - this
looks easy enough although I have not seen examples of how to delete the
entry afterwards
--
Mike News

[Graham Mayor]

Mike wrote:
In message
at 08:22:05 on Wed, 17 Oct 2007, Graham Mayor
wrote
There's no alternative to using merge if merge is what you are
doing. You will either have to put up with the SQL message or
suppress it via the registry hack.
If you want to manually type labels, simply create a new document
from the envelope/label tool.

If I have a sheet of Avery Labels, I could possibly create a template
which looks like the MM main document and then do search and replace.
This becomes tricky then if I need more than 14 labels (i.e. more than
one page)

If I can set the registry from excel VBA then I will be happy - this
looks easy enough although I have not seen examples of how to delete
the entry afterwards

This is easier than dismissing the message, or applying the registry hack?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

[Mike[_9_]]

In message
at 08:52:00 on Thu, 18 Oct 2007, Graham Mayor
wrote
If I can set the registry from excel VBA then I will be happy - this
looks easy enough although I have not seen examples of how to delete
the entry afterwards

This is easier than dismissing the message, or applying the registry hack?

The VBA will hack the registry then unhack it again when I detect the
word document has closed (which I already do anyway). There are plenty
of examples on google how to amend registry from excel VBA, which is
where I am calling the word doc from
--
Mike News

[fegf fegf]

Hi there,

Just launched my WordMerge free solution for your review.

http://www.ndados.com

Regards,
Filipe Freire



Mike wrote:

Alternative to mailmerge
16-Oct-07

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

Previous Posts In This Thread:

On Tuesday, October 16, 2007 2:11 PM
Mike wrote:

Alternative to mailmerge
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

On Wednesday, October 17, 2007 1:22 AM
Graham Mayor wrote:

There's no alternative to using merge if merge is what you are doing.
There's no alternative to using merge if merge is what you are doing. You
will either have to put up with the SQL message or suppress it via the
registry hack.
If you want to manually type labels, simply create a new document from the
envelope/label tool.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Mike wrote:

On Wednesday, October 17, 2007 5:12 AM
Peter Jamieson wrote:

Yes, it's pretty annoying.Well, it may still make the difference.
Yes, it's pretty annoying.



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

On Wednesday, October 17, 2007 1:15 PM
Mike wrote:

Alternative to mailmerge
In message
at 10:12:12 on Wed, 17 Oct 2007, Peter Jamieson
wrote

Pete,
Thanks for this - I will digest the information in a while. I didn't
want you to think I was ignoring you whilst posting my other reply
--
Mike News

On Wednesday, October 17, 2007 1:19 PM
Mike wrote:

Alternative to mailmerge
In message
at 08:22:05 on Wed, 17 Oct 2007, Graham Mayor
wrote

If I have a sheet of Avery Labels, I could possibly create a template
which looks like the MM main document and then do search and replace.
This becomes tricky then if I need more than 14 labels (i.e. more than
one page)

If I can set the registry from excel VBA then I will be happy - this
looks easy enough although I have not seen examples of how to delete the
entry afterwards
--
Mike News

On Thursday, October 18, 2007 1:52 AM
Graham Mayor wrote:

Alternative to mailmerge
Mike wrote:

This is easier than dismissing the message, or applying the registry hack?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


On Thursday, October 18, 2007 1:46 PM
Mike wrote:

Alternative to mailmerge
In message
at 08:52:00 on Thu, 18 Oct 2007, Graham Mayor
wrote

The VBA will hack the registry then unhack it again when I detect the
word document has closed (which I already do anyway). There are plenty
of examples on google how to amend registry from excel VBA, which is
where I am calling the word doc from
--
Mike News


Submitted via EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: Effective C#, Second Edition [Addison Wesley]
http://www.eggheadcafe.com/tutorials...fective-c.aspx