Not able to recording macro for mail-merge

[albissim]

Hallo,
I need some help for a macro code ... we've a lot of Word2000 mail-merge
templates originally connected to a .dbf file where in a textbox is possible
put the number we're looking for , now we've to move to Word2003 and the new
source will be a SQL file with the same structure

At the moment we use a code like this:

ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
Set myMMData = ActiveDocument.MailMerge.DataSource
If myMMData.FindRecord(FindText:=TextBox1, _
Field:="COLLAB") = True Then
myMMData.QueryString = _
"SELECT * FROM `COLLABORATORI` WHERE ((`COLLAB` = '" & TextBox1.Text & "'))"
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
Unload Me
Application.ScreenUpdating = True
Else
MsgBox ("Codice non trovato !!!")
--
I tried to record the piece of code to be replaced in the existing macro but
I wasn't able to ...

Thanks in advance for your help

Regards,
Alberto Simone

 

[Peter Jamieson]

Some things to look at...

1. before the code you quote here, Word must connect to your data source. In
other words, setting .QueryString changes the properties of an existing
connection. It does not create a new one.
2. If you changed the data source, you must connect each mail merge document
to the new data source. If you succeeded when you did that manually, then
the problem is likely to be that your QueryString is not valid SQL for your
new datasource.
3. if you want to find out what VBA code is required to connect to your new
data source, try connecting manually. if you succeed, then save your Word
document in HTML format, open the HTML document in Notepad, and look for the
Mailmerge information near the top of the file. That tells you what
parameters Word is /really/ trying to use to open the data source. If you
can't succeed, I can make some suggestions. Maybe you could try that first?

Peter Jamieson

 

[albissim]

Hallo Peter,
first of all thanks for your help ...

I followed your instructions and the following are the information about the
datasource, taken from the html document:

<w:WordDocument>
<w:MailMergeMainDocType>FormLetters</w:MailMergeMainDocType>
<w:MailMergeLinkToQuery/>
<w:MailMergeDataType>ODSO</w:MailMergeDataType>
<w:MailMergeConnectString>Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=Ace_Local;Data
Source=it02wsql003;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=IT02LTOP001;Use Encryption for Data=False;Tag with
column collation when possible=False</w:MailMergeConnectString>
<w:MailMergeQueryString>SELECT * FROM "Collaboratori"
</w:MailMergeQueryString>
<w:MailMergeDataSource
HRef="H:\Workdoc\Archivi\it02wsql003 Ace_Local
Collaboratori.odc"></w:MailMergeDataSource>
<w:MailMergeCheckErrors>3</w:MailMergeCheckErrors>
<w:Odso>
<w:Udl>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=True;Initial Catalog=Ace_Local;Data Source=it02wsql003;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=IT02LTOP001;Use
Encryption for Data=False;Tag with column collation when
possible=False</w:Udl>
<w:Table>Collaboratori</w:Table>
<w:Src>H:\Workdoc\Archivi\it02wsql003 Ace_Local Collaboratori.odc</w:Src>

I'm sorry but I can't see any difference between my QueryString Code and the
above ... any further help will be very appreciated

Regards,

Alberto Simone


"Peter Jamieson" ha scritto:

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?YWxiaXNzaW0=?=,

It's not clear to me what code you're trying to record or where exactly you're
having problems?

I need some help for a macro code ... we've a lot of Word2000 mail-merge
templates originally connected to a .dbf file where in a textbox is possible
put the number we're looking for , now we've to move to Word2003 and the new
source will be a SQL file with the same structure

At the moment we use a code like this:

ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
Set myMMData = ActiveDocument.MailMerge.DataSource
If myMMData.FindRecord(FindText:=TextBox1, _
Field:="COLLAB") = True Then
myMMData.QueryString = _
"SELECT * FROM `COLLABORATORI` WHERE ((`COLLAB` = '" & TextBox1.Text & "'))"
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
Unload Me
Application.ScreenUpdating = True
Else
MsgBox ("Codice non trovato !!!")

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Peter Jamieson]

Sorry, I forgot to get back on this one. Cindy's message prompted me.

Now we know you're using SQl Server, I think you need different syntax,
probably:

myMMData.QueryString = _
"SELECT * FROM ""COLLABORATORI"" WHERE COLLABORATORI.COLLAB = '" & _
TextBox1.Text & "'))"

The quotes around the table name seem to be necessary. I do not know why -
they are syntactically unnecessary unless the table name contains spaces or
other special characters. You can use [ ] instead, but not the backquotes ``
you are using at the moment.

You do not seem to need the quotes around the names after the WHERE (it all
seems very inconsistent to me). If the names included spaces, you would
probably need

""COLLABORATORI"".""COLLAB""

Qualifying the column name COLLAB by the table name COLLABORATORI seems to
be necessary. Again, it is not normally syntactically required in SQL, but
it is here.

Peter Jamieson

Peter Jamieson
b. do not seem to need the quotes around

The backquotes ` ` don't

 

[albissim]

Hallo Peter,
the QueryString command works !

now I've to fix just the first part of the If command :

If myMMData.FindRecord(FindText:=TextBox1, _
Field:="COLLAB") = True Then

where it looks like the FindText parameter is not correct, I tried also
directly with the code to found between brackets i.e. "70601"

Thanks a lot again for your help !

Regards,
Alberto Simone


"Peter Jamieson" ha scritto:

Sorry, I forgot to get back on this one. Cindy's message prompted me.

Now we know you're using SQl Server, I think you need different syntax,
probably:

myMMData.QueryString = _
"SELECT * FROM ""COLLABORATORI"" WHERE COLLABORATORI.COLLAB = '" & _
TextBox1.Text & "'))"

The quotes around the table name seem to be necessary. I do not know why -
they are syntactically unnecessary unless the table name contains spaces or
other special characters. You can use [ ] instead, but not the backquotes ``
you are using at the moment.

You do not seem to need the quotes around the names after the WHERE (it all
seems very inconsistent to me). If the names included spaces, you would
probably need

""COLLABORATORI"".""COLLAB""

Qualifying the column name COLLAB by the table name COLLABORATORI seems to
be necessary. Again, it is not normally syntactically required in SQL, but
it is here.

Peter Jamieson

Peter Jamieson
b. do not seem to need the quotes around

The backquotes ` ` don't

Hallo Peter,
first of all thanks for your help ...

I followed your instructions and the following are the information about
the
datasource, taken from the html document:

<w:WordDocument>
<w:MailMergeMainDocType>FormLetters</w:MailMergeMainDocType>
<w:MailMergeLinkToQuery/>
<w:MailMergeDataType>ODSO</w:MailMergeDataType>
<w:MailMergeConnectString>Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=Ace_Local;Data
Source=it02wsql003;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=IT02LTOP001;Use Encryption for Data=False;Tag
with
column collation when possible=False</w:MailMergeConnectString>
<w:MailMergeQueryString>SELECT * FROM "Collaboratori"
</w:MailMergeQueryString>
<w:MailMergeDataSource
HRef="H:\Workdoc\Archivi\it02wsql003 Ace_Local
Collaboratori.odc"></w:MailMergeDataSource>
<w:MailMergeCheckErrors>3</w:MailMergeCheckErrors>
<w:Odso>
<w:Udl>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=True;Initial Catalog=Ace_Local;Data Source=it02wsql003;Use Procedure
for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=IT02LTOP001;Use
Encryption for Data=False;Tag with column collation when
possible=False</w:Udl>
<w:Table>Collaboratori</w:Table>
<w:Src>H:\Workdoc\Archivi\it02wsql003 Ace_Local
Collaboratori.odc</w:Src>

I'm sorry but I can't see any difference between my QueryString Code and
the
above ... any further help will be very appreciated

Regards,

Alberto Simone


"Peter Jamieson" ha scritto:

 

[albissim]

Hallo Cindy,
as I just said to Peter, the QueryString command works !

now I've to fix just the first part of the If command :

If myMMData.FindRecord(FindText:=TextBox1, _
Field:="COLLAB") = True Then

where it looks like the FindText parameter is not correct, I tried also
directly with the code to found between brackets i.e. "70601"

Thanks a lot again for your help !

Regards,
Alberto Simone

"Cindy M -WordMVP-" ha scritto:

 

[Peter Jamieson]

As far as I know, to make the FindRecord method work
a. there must be at least one MERGEFEILD field in the document
b. the ViewMailMergeFieldCodes property of the MailMerge object must be set
to false (i.e. the document should be in preview mode, displaying field
results.

Peter Jamieson

Hallo Peter,
the QueryString command works !

now I've to fix just the first part of the If command :

If myMMData.FindRecord(FindText:=TextBox1, _
Field:="COLLAB") = True Then

where it looks like the FindText parameter is not correct, I tried also
directly with the code to found between brackets i.e. "70601"

Thanks a lot again for your help !

Regards,
Alberto Simone


"Peter Jamieson" ha scritto:

Sorry, I forgot to get back on this one. Cindy's message prompted me.

Now we know you're using SQl Server, I think you need different syntax,
probably:

myMMData.QueryString = _
"SELECT * FROM ""COLLABORATORI"" WHERE COLLABORATORI.COLLAB = '" & _
TextBox1.Text & "'))"

The quotes around the table name seem to be necessary. I do not know
why -
they are syntactically unnecessary unless the table name contains spaces
or
other special characters. You can use [ ] instead, but not the backquotes
``
you are using at the moment.

You do not seem to need the quotes around the names after the WHERE (it
all
seems very inconsistent to me). If the names included spaces, you would
probably need

""COLLABORATORI"".""COLLAB""

Qualifying the column name COLLAB by the table name COLLABORATORI seems
to
be necessary. Again, it is not normally syntactically required in SQL,
but
it is here.

Peter Jamieson

Peter Jamieson
b. do not seem to need the quotes around

The backquotes ` ` don't

Hallo Peter,
first of all thanks for your help ...

I followed your instructions and the following are the information
about
the
datasource, taken from the html document:

<w:WordDocument>
<w:MailMergeMainDocType>FormLetters</w:MailMergeMainDocType>
<w:MailMergeLinkToQuery/>
<w:MailMergeDataType>ODSO</w:MailMergeDataType>
<w:MailMergeConnectString>Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=Ace_Local;Data
Source=it02wsql003;Use Procedure for Prepare=1;Auto
Translate=True;Packet
Size=4096;Workstation ID=IT02LTOP001;Use Encryption for Data=False;Tag
with
column collation when possible=False</w:MailMergeConnectString>
<w:MailMergeQueryString>SELECT * FROM "Collaboratori"
</w:MailMergeQueryString>
<w:MailMergeDataSource
HRef="H:\Workdoc\Archivi\it02wsql003 Ace_Local
Collaboratori.odc"></w:MailMergeDataSource>
<w:MailMergeCheckErrors>3</w:MailMergeCheckErrors>
<w:Odso>
<w:Udl>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=True;Initial Catalog=Ace_Local;Data Source=it02wsql003;Use
Procedure
for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=IT02LTOP001;Use
Encryption for Data=False;Tag with column collation when
possible=False</w:Udl>
<w:Table>Collaboratori</w:Table>
<w:Src>H:\Workdoc\Archivi\it02wsql003 Ace_Local
Collaboratori.odc</w:Src>

I'm sorry but I can't see any difference between my QueryString Code
and
the
above ... any further help will be very appreciated

Regards,

Alberto Simone


"Peter Jamieson" ha scritto:

Some things to look at...

1. before the code you quote here, Word must connect to your data
source.
In
other words, setting .QueryString changes the properties of an
existing
connection. It does not create a new one.
2. If you changed the data source, you must connect each mail merge
document
to the new data source. If you succeeded when you did that manually,
then
the problem is likely to be that your QueryString is not valid SQL for
your
new datasource.
3. if you want to find out what VBA code is required to connect to
your
new
data source, try connecting manually. if you succeed, then save your
Word
document in HTML format, open the HTML document in Notepad, and look
for
the
Mailmerge information near the top of the file. That tells you what
parameters Word is /really/ trying to use to open the data source. If
you
can't succeed, I can make some suggestions. Maybe you could try that
first?

Peter Jamieson

Hallo,
I need some help for a macro code ... we've a lot of Word2000
mail-merge
templates originally connected to a .dbf file where in a textbox is
possible
put the number we're looking for , now we've to move to Word2003 and
the
new
source will be a SQL file with the same structure

At the moment we use a code like this:

ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
Set myMMData = ActiveDocument.MailMerge.DataSource
If myMMData.FindRecord(FindText:=TextBox1, _
Field:="COLLAB") = True Then
myMMData.QueryString = _
"SELECT * FROM `COLLABORATORI` WHERE ((`COLLAB` = '" & TextBox1.Text
&
"'))"
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
Unload Me
Application.ScreenUpdating = True
Else
MsgBox ("Codice non trovato !!!")
--
I tried to record the piece of code to be replaced in the existing
macro
but
I wasn't able to ...

Thanks in advance for your help

Regards,
Alberto Simone

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?YWxiaXNzaW0=?=,

now I've to fix just the first part of the If command :

If myMMData.FindRecord(FindText:=TextBox1, _
Field:="COLLAB") = True Then

where it looks like the FindText parameter is not correct, I tried also
directly with the code to found between brackets i.e. "70601"

Do you mean it's not finding anything? Or that you're unable to execute
the merge afterwards?

In Word 2000 FindRecord is broken in that, after a successful "Find", mail
merge can't be executed. After a successful find you need to perform an
unsuccessful find to re-enable the mail merge. (This problem also occurs
in the UI).

In Word 2002 and 2003 Microsoft fixed the behavior in the UI, but
FindRecord in the object model is broken completely. The old FindRecord
was renamed to FindRecord2000 and works the same as described above.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail