Not able to recording macro for mail-merge

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
Wed Jan 18 13:26:59 CST 2006

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" <leonardo.simone@fastwebnet.it> wrote in message
news:0CDAAEB8-E4FC-44A0-95D5-0C50ED57F0C2@microsoft.com...
> 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

leonardo
Thu Jan 19 07:26:02 CST 2006

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
>
> "albissim" <leonardo.simone@fastwebnet.it> wrote in message
> news:0CDAAEB8-E4FC-44A0-95D5-0C50ED57F0C2@microsoft.com...
> > 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
Tue Jan 24 10:24:41 CST 2006

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 !!!")
> --
> I tried to record the piece of code to be replaced in the existing macro but
> I wasn't able to ...
>

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
Tue Jan 24 11:51:59 CST 2006

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" <leonardo.simone@fastwebnet.it> wrote in message
news:D3F3ACAF-946F-46C3-8438-6227CCDB469B@microsoft.com...
> 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
>>
>> "albissim" <leonardo.simone@fastwebnet.it> wrote in message
>> news:0CDAAEB8-E4FC-44A0-95D5-0C50ED57F0C2@microsoft.com...
>> > 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
>>
>>
>>

leonardo
Wed Jan 25 04:01:02 CST 2006

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
> "albissim" <leonardo.simone@fastwebnet.it> wrote in message
> news:D3F3ACAF-946F-46C3-8438-6227CCDB469B@microsoft.com...
> > 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
> >>
> >> "albissim" <leonardo.simone@fastwebnet.it> wrote in message
> >> news:0CDAAEB8-E4FC-44A0-95D5-0C50ED57F0C2@microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>
>

leonardo
Wed Jan 25 04:03:03 CST 2006

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:

> 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 !!!")
> > --
> > I tried to record the piece of code to be replaced in the existing macro but
> > I wasn't able to ...
> >
>
> 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
Wed Jan 25 05:41:07 CST 2006

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

"albissim" <leonardo.simone@fastwebnet.it> wrote in message
news:9EBC6EAA-780C-425D-BD7E-21B436A35F79@microsoft.com...
> 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
>> "albissim" <leonardo.simone@fastwebnet.it> wrote in message
>> news:D3F3ACAF-946F-46C3-8438-6227CCDB469B@microsoft.com...
>> > 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
>> >>
>> >> "albissim" <leonardo.simone@fastwebnet.it> wrote in message
>> >> news:0CDAAEB8-E4FC-44A0-95D5-0C50ED57F0C2@microsoft.com...
>> >> > 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
Wed Jan 25 09:37:55 CST 2006

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