[tskogstrom]

Hi,

I have a mailmerge document connected to a excel named range. The link
reference is to a library below my 'office standard library'.

When I send the excel source file and the mailmerge word document to
other users, the link/connection doesn't work. I guess it still is
pointing to my own library path and not theirs.

How can I update the refererence path /mailmerge connection so it will
be correct for the user?

I have some code from excel i probably can use in word also (se below
if your are interested) to find the right path, but i'm not used with
VBA in word. Should I create a add-in that do this on opening event
and when this filename is the right one? Or can you have som vba code
follow the word file? When I try to save VBA in the ThisDocument
module, it will get lost after reopening...

Kind regards

-----------------------------------
In a excel application i use this function in vba to identify standard
library:

Function stdDocumentPath()
'Let you know the standard document catalog of the user = myDocPath
'*************************************''

Dim myDocPath As String
On Error GoTo stdDocumentPath_Error
Let myDocPath = Application.DefaultFilePath

'Add a "\" at the end of the path, unless the setting is already
followed by a "\" -
'which it will be if the setting is set to a root folder
If Not Right$(myDocPath, 1) = "\" Then
myDocPath = myDocPath + "\"
End If

Let stdDocumentPath = myDocPath
Debug.Print "stdDocumentPath: " & stdDocumentPath

On Error GoTo 0
Exit Function

stdDocumentPath_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in
procedure ""stdDocumentPath"" of Module UsedCode"
Debug.Print myDocPath
Debug.Print stdDocumentPath
Sheets("Show").Range("a1").Value = "Fel"
On Error GoTo 0
Exit Function

End Function


I call it with:
'Open source file, copy content and paste in this workbook
Workbooks.Open (orgStdPath & FolderName & "\" & SourceDoc)

.... where FolderName is the name of the folder "Merge" and SourceDoc
is the name of the Excelfile that is the data source to the word
mailmerge document.

[Peter Jamieson]

First, before you distribute the solution, ensure that the Mail Merge Main
Document has been disconnected from its data source. You have to do that
because nothing you do in VBA can change the data source path /before/ Word
tries to open it.

You can disconnect the data source while the mail merge main document is
open by using

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in the Word VBA Editor Immediate WIndow.

Then, in Word VBA, create an ordinary module in your Document and put a
routine such as the following in it - this assumes that you know the name of
the .xls and that it will be in the same folder as the .doc

Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String


' set this to be the file name of your data source
strDataSource = "myexcelfile.xls"


' set this to be the connection string for your data source
'strConnection = ""

' set this to be the query for your data source
' if you need to sort aor filter, you need to add
' the appropriate ORDER BY and WHERE clauses
' strQuery = "SELECT * FROM [myrangename]"

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
With .MailMerge
.OpenDataSource _
Name:=strDataSource, _
SQLStatement:=str
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument

' NB the above code does not execute the merge.
End With
End With
End Sub

Unfortunately, you, or your user, will probably also have to take account of
the following artiicle:

http://support.microsoft.com/kb/825765/en-us

--
Peter Jamieson
http://tips.pjmsn.me.uk

"tskogstrom" wrote in message
ups.com...
Hi,

I have a mailmerge document connected to a excel named range. The link
reference is to a library below my 'office standard library'.

When I send the excel source file and the mailmerge word document to
other users, the link/connection doesn't work. I guess it still is
pointing to my own library path and not theirs.

How can I update the refererence path /mailmerge connection so it will
be correct for the user?

I have some code from excel i probably can use in word also (se below
if your are interested) to find the right path, but i'm not used with
VBA in word. Should I create a add-in that do this on opening event
and when this filename is the right one? Or can you have som vba code
follow the word file? When I try to save VBA in the ThisDocument
module, it will get lost after reopening...

Kind regards

-----------------------------------
In a excel application i use this function in vba to identify standard
library:

Function stdDocumentPath()
'Let you know the standard document catalog of the user = myDocPath
'*************************************''

Dim myDocPath As String
On Error GoTo stdDocumentPath_Error
Let myDocPath = Application.DefaultFilePath

'Add a "\" at the end of the path, unless the setting is already
followed by a "\" -
'which it will be if the setting is set to a root folder
If Not Right$(myDocPath, 1) = "\" Then
myDocPath = myDocPath + "\"
End If

Let stdDocumentPath = myDocPath
Debug.Print "stdDocumentPath: " & stdDocumentPath

On Error GoTo 0
Exit Function

stdDocumentPath_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in
procedure ""stdDocumentPath"" of Module UsedCode"
Debug.Print myDocPath
Debug.Print stdDocumentPath
Sheets("Show").Range("a1").Value = "Fel"
On Error GoTo 0
Exit Function

End Function


I call it with:
'Open source file, copy content and paste in this workbook
Workbooks.Open (orgStdPath & FolderName & "\" & SourceDoc)

... where FolderName is the name of the folder "Merge" and SourceDoc
is the name of the Excelfile that is the data source to the word
mailmerge document.

[tskogstrom]

Great, thank you. I'll test this.
How should I do, to make the code to follow the word file ... or does
I had to create a add-ins to react only if this particulat file is
opened?
I'm used to vba in excel, but word is new to me and when I tried
earlier to have code in ThisDocument module under the file, it always
vanish on repopening...

Kind regards
Tskogsrrom
----------------------------------------------------

On 27 Aug, 15:31, "Peter Jamieson"
wrote:
First, before you distribute the solution, ensure that the Mail Merge Main
Document has been disconnected from its data source. You have to do that
because nothing you do in VBA can change the data source path /before/ Word
tries to open it.

You can disconnect the data source while the mail merge main document is
open by using

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in the Word VBA Editor Immediate WIndow.

Then, in Word VBA, create an ordinary module in your Document and put a
routine such as the following in it - this assumes that you know the name of
the .xls and that it will be in the same folder as the .doc

Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String

' set this to be the file name of your data source
strDataSource = "myexcelfile.xls"

' set this to be the connection string for your data source
'strConnection = ""

' set this to be the query for your data source
' if you need to sort aor filter, you need to add
' the appropriate ORDER BY and WHERE clauses
' strQuery = "SELECT * FROM [myrangename]"

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
With .MailMerge
.OpenDataSource _
Name:=strDataSource, _
SQLStatement:=str
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument

' NB the above code does not execute the merge.
End With
End With
End Sub

Unfortunately, you, or your user, will probably also have to take account of
the following artiicle:

http://support.microsoft.com/kb/825765/en-us

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

"tskogstrom" wrote in message

ups.com...



Hi,

I have a mailmerge document connected to a excel named range. The link
reference is to a library below my 'office standard library'.

When I send the excel source file and the mailmerge word document to
other users, the link/connection doesn't work. I guess it still is
pointing to my own library path and not theirs.

How can I update the refererence path /mailmerge connection so it will
be correct for the user?

I have some code from excel i probably can use in word also (se below
if your are interested) to find the right path, but i'm not used with
VBA in word. Should I create a add-in that do this on opening event
and when this filename is the right one? Or can you have som vba code
follow the word file? When I try to save VBA in the ThisDocument
module, it will get lost after reopening...

Kind regards

-----------------------------------
In a excel application i use this function in vba to identify standard
library:

Function stdDocumentPath()
'Let you know the standard document catalog of the user = myDocPath
'*************************************''

Dim myDocPath As String
On Error GoTo stdDocumentPath_Error
Let myDocPath = Application.DefaultFilePath

'Add a "\" at the end of the path, unless the setting is already
followed by a "\" -
'which it will be if the setting is set to a root folder
If Not Right$(myDocPath, 1) = "\" Then
myDocPath = myDocPath + "\"
End If

Let stdDocumentPath = myDocPath
Debug.Print "stdDocumentPath: " & stdDocumentPath

On Error GoTo 0
Exit Function

stdDocumentPath_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in
procedure ""stdDocumentPath"" of Module UsedCode"
Debug.Print myDocPath
Debug.Print stdDocumentPath
Sheets("Show").Range("a1").Value = "Fel"
On Error GoTo 0
Exit Function

End Function

I call it with:
'Open source file, copy content and paste in this workbook
Workbooks.Open (orgStdPath & FolderName & "\" & SourceDoc)

... where FolderName is the name of the folder "Merge" and SourceDoc
is the name of the Excelfile that is the data source to the word
mailmerge document.- Dölj citerad text -

- Visa citerad text -

[tskogstrom]

Following question two:

Regardgin the warning "You receive the "Opening this will run the
following SQL command" message when" .

You said that the connection should be disconnected before running the
connection code - but in that case the MS security popup shouldn't
run, right? Will it occure when the code is executing instead?
And if I will run the code on opening event, I guess I should add the
"ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument" code
in the end?

Have I understand the problem correct?

Kind regards
Tskogstrom

---------------------------------------------
On 27 Aug, 15:31, "Peter Jamieson"
wrote:
First, before you distribute the solution, ensure that the Mail Merge Main
Document has been disconnected from its data source. You have to do that
because nothing you do in VBA can change the data source path /before/ Word
tries to open it.

You can disconnect the data source while the mail merge main document is
open by using

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in the Word VBA Editor Immediate WIndow.

Then, in Word VBA, create an ordinary module in your Document and put a
routine such as the following in it - this assumes that you know the name of
the .xls and that it will be in the same folder as the .doc

Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String

' set this to be the file name of your data source
strDataSource = "myexcelfile.xls"

' set this to be the connection string for your data source
'strConnection = ""

' set this to be the query for your data source
' if you need to sort aor filter, you need to add
' the appropriate ORDER BY and WHERE clauses
' strQuery = "SELECT * FROM [myrangename]"

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
With .MailMerge
.OpenDataSource _
Name:=strDataSource, _
SQLStatement:=str
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument

' NB the above code does not execute the merge.
End With
End With
End Sub

Unfortunately, you, or your user, will probably also have to take account of
the following artiicle:

http://support.microsoft.com/kb/825765/en-us

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

"tskogstrom" wrote in message

ups.com...



Hi,

I have a mailmerge document connected to a excel named range. The link
reference is to a library below my 'office standard library'.

When I send the excel source file and the mailmerge word document to
other users, the link/connection doesn't work. I guess it still is
pointing to my own library path and not theirs.

How can I update the refererence path /mailmerge connection so it will
be correct for the user?

I have some code from excel i probably can use in word also (se below
if your are interested) to find the right path, but i'm not used with
VBA in word. Should I create a add-in that do this on opening event
and when this filename is the right one? Or can you have som vba code
follow the word file? When I try to save VBA in the ThisDocument
module, it will get lost after reopening...

Kind regards

-----------------------------------
In a excel application i use this function in vba to identify standard
library:

Function stdDocumentPath()
'Let you know the standard document catalog of the user = myDocPath
'*************************************''

Dim myDocPath As String
On Error GoTo stdDocumentPath_Error
Let myDocPath = Application.DefaultFilePath

'Add a "\" at the end of the path, unless the setting is already
followed by a "\" -
'which it will be if the setting is set to a root folder
If Not Right$(myDocPath, 1) = "\" Then
myDocPath = myDocPath + "\"
End If

Let stdDocumentPath = myDocPath
Debug.Print "stdDocumentPath: " & stdDocumentPath

On Error GoTo 0
Exit Function

stdDocumentPath_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in
procedure ""stdDocumentPath"" of Module UsedCode"
Debug.Print myDocPath
Debug.Print stdDocumentPath
Sheets("Show").Range("a1").Value = "Fel"
On Error GoTo 0
Exit Function

End Function

I call it with:
'Open source file, copy content and paste in this workbook
Workbooks.Open (orgStdPath & FolderName & "\" & SourceDoc)

... where FolderName is the name of the folder "Merge" and SourceDoc
is the name of the Excelfile that is the data source to the word
mailmerge document.- Dölj citerad text -

- Visa citerad text -

[Peter Jamieson]

How should I do, to make the code to follow the word file ... or does

Put the code in the .doc


I'm used to vba in excel, but word is new to me and when I tried
earlier to have code in ThisDocument module under the file, it always
vanish on repopening...


It's generally better to put code in a new (non-class) module. In this case
it doesn't matter what themodule is called, but the Sub needs to be called
AutoOpen() . (In fact there are other ways to get Word to run code when you
open a .doc but that should do for now).

Although I wouldn't expect code in ThisDocument to disappear,
a. it usually results in the "control toolbox" appearing when you open the
document, which you don't usually want
b. if you put the code in the ThisDocument /of the attached template/ or in
Normal.dot, it won't follow the .doc around anyway.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"tskogstrom" wrote in message
ups.com...
Great, thank you. I'll test this.
How should I do, to make the code to follow the word file ... or does
I had to create a add-ins to react only if this particulat file is
opened?
I'm used to vba in excel, but word is new to me and when I tried
earlier to have code in ThisDocument module under the file, it always
vanish on repopening...

Kind regards
Tskogsrrom
----------------------------------------------------

On 27 Aug, 15:31, "Peter Jamieson"
wrote:
First, before you distribute the solution, ensure that the Mail Merge Main
Document has been disconnected from its data source. You have to do that
because nothing you do in VBA can change the data source path /before/
Word
tries to open it.

You can disconnect the data source while the mail merge main document is
open by using

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in the Word VBA Editor Immediate WIndow.

Then, in Word VBA, create an ordinary module in your Document and put a
routine such as the following in it - this assumes that you know the name
of
the .xls and that it will be in the same folder as the .doc

Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String

' set this to be the file name of your data source
strDataSource = "myexcelfile.xls"

' set this to be the connection string for your data source
'strConnection = ""

' set this to be the query for your data source
' if you need to sort aor filter, you need to add
' the appropriate ORDER BY and WHERE clauses
' strQuery = "SELECT * FROM [myrangename]"

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
With .MailMerge
.OpenDataSource _
Name:=strDataSource, _
SQLStatement:=str
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument

' NB the above code does not execute the merge.
End With
End With
End Sub

Unfortunately, you, or your user, will probably also have to take account
of
the following artiicle:

http://support.microsoft.com/kb/825765/en-us

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

"tskogstrom" wrote in message

ups.com...



Hi,

I have a mailmerge document connected to a excel named range. The link
reference is to a library below my 'office standard library'.

When I send the excel source file and the mailmerge word document to
other users, the link/connection doesn't work. I guess it still is
pointing to my own library path and not theirs.

How can I update the refererence path /mailmerge connection so it will
be correct for the user?

I have some code from excel i probably can use in word also (se below
if your are interested) to find the right path, but i'm not used with
VBA in word. Should I create a add-in that do this on opening event
and when this filename is the right one? Or can you have som vba code
follow the word file? When I try to save VBA in the ThisDocument
module, it will get lost after reopening...

Kind regards

-----------------------------------
In a excel application i use this function in vba to identify standard
library:

Function stdDocumentPath()
'Let you know the standard document catalog of the user = myDocPath
'*************************************''

Dim myDocPath As String
On Error GoTo stdDocumentPath_Error
Let myDocPath = Application.DefaultFilePath

'Add a "\" at the end of the path, unless the setting is already
followed by a "\" -
'which it will be if the setting is set to a root folder
If Not Right$(myDocPath, 1) = "\" Then
myDocPath = myDocPath + "\"
End If

Let stdDocumentPath = myDocPath
Debug.Print "stdDocumentPath: " & stdDocumentPath

On Error GoTo 0
Exit Function

stdDocumentPath_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in
procedure ""stdDocumentPath"" of Module UsedCode"
Debug.Print myDocPath
Debug.Print stdDocumentPath
Sheets("Show").Range("a1").Value = "Fel"
On Error GoTo 0
Exit Function

End Function

I call it with:
'Open source file, copy content and paste in this workbook
Workbooks.Open (orgStdPath & FolderName & "\" & SourceDoc)

... where FolderName is the name of the folder "Merge" and SourceDoc
is the name of the Excelfile that is the data source to the word
mailmerge document.- Dölj citerad text -

- Visa citerad text -

[Peter Jamieson]

You said that the connection should be disconnected before running the
connection code - but in that case the MS security popup shouldn't
run, right?


The troble is that this setting is not just to prevent that dialog. If you
try to connect to a data source programmatically, the connection will
probably fail if the registry entry is not set up. If you have error
trapping code, your code should notice. If you don't the sub completes but
no connection is made. I think you'll just have to try it on a friendly end
user's system and see what happens.

It's there to prevent rogue Word documents from grabbing information from
another part of a user's system without "permission". And because Microsoft
left the permissions door wide open a long time ago in Windows, almost
everything on most systems has "permission" to do almost anything. WHile I
can see that you might need to have this setting for mail merge main
documents in which no VBA code actually runs (i.e. the data source was left
connected and Word would just reconnect it on open)
a. I don't really see why anything special is needed to prevent
programmatic connection to a data source - why not rely on the macro
security settings? After all, you can write a macro that uses ADO and SQL to
get data, stuff it in a document, and email it out via SMTP, and you won't
see this silly message
b. even when Word is doing the reconnecting without any VBA, perhaps they
could have had Word check the macro security settings as if it were running
VBA.

But I expect the logic looked different when Microsoft looked at it.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"tskogstrom" wrote in message
oups.com...
Following question two:

Regardgin the warning "You receive the "Opening this will run the
following SQL command" message when" .

You said that the connection should be disconnected before running the
connection code - but in that case the MS security popup shouldn't
run, right? Will it occure when the code is executing instead?
And if I will run the code on opening event, I guess I should add the
"ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument" code
in the end?

Have I understand the problem correct?

Kind regards
Tskogstrom

---------------------------------------------
On 27 Aug, 15:31, "Peter Jamieson"
wrote:
First, before you distribute the solution, ensure that the Mail Merge Main
Document has been disconnected from its data source. You have to do that
because nothing you do in VBA can change the data source path /before/
Word
tries to open it.

You can disconnect the data source while the mail merge main document is
open by using

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in the Word VBA Editor Immediate WIndow.

Then, in Word VBA, create an ordinary module in your Document and put a
routine such as the following in it - this assumes that you know the name
of
the .xls and that it will be in the same folder as the .doc

Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String

' set this to be the file name of your data source
strDataSource = "myexcelfile.xls"

' set this to be the connection string for your data source
'strConnection = ""

' set this to be the query for your data source
' if you need to sort aor filter, you need to add
' the appropriate ORDER BY and WHERE clauses
' strQuery = "SELECT * FROM [myrangename]"

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
With .MailMerge
.OpenDataSource _
Name:=strDataSource, _
SQLStatement:=str
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument

' NB the above code does not execute the merge.
End With
End With
End Sub

Unfortunately, you, or your user, will probably also have to take account
of
the following artiicle:

http://support.microsoft.com/kb/825765/en-us

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

"tskogstrom" wrote in message

ups.com...



Hi,

I have a mailmerge document connected to a excel named range. The link
reference is to a library below my 'office standard library'.

When I send the excel source file and the mailmerge word document to
other users, the link/connection doesn't work. I guess it still is
pointing to my own library path and not theirs.

How can I update the refererence path /mailmerge connection so it will
be correct for the user?

I have some code from excel i probably can use in word also (se below
if your are interested) to find the right path, but i'm not used with
VBA in word. Should I create a add-in that do this on opening event
and when this filename is the right one? Or can you have som vba code
follow the word file? When I try to save VBA in the ThisDocument
module, it will get lost after reopening...

Kind regards

-----------------------------------
In a excel application i use this function in vba to identify standard
library:

Function stdDocumentPath()
'Let you know the standard document catalog of the user = myDocPath
'*************************************''

Dim myDocPath As String
On Error GoTo stdDocumentPath_Error
Let myDocPath = Application.DefaultFilePath

'Add a "\" at the end of the path, unless the setting is already
followed by a "\" -
'which it will be if the setting is set to a root folder
If Not Right$(myDocPath, 1) = "\" Then
myDocPath = myDocPath + "\"
End If

Let stdDocumentPath = myDocPath
Debug.Print "stdDocumentPath: " & stdDocumentPath

On Error GoTo 0
Exit Function

stdDocumentPath_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in
procedure ""stdDocumentPath"" of Module UsedCode"
Debug.Print myDocPath
Debug.Print stdDocumentPath
Sheets("Show").Range("a1").Value = "Fel"
On Error GoTo 0
Exit Function

End Function

I call it with:
'Open source file, copy content and paste in this workbook
Workbooks.Open (orgStdPath & FolderName & "\" & SourceDoc)

... where FolderName is the name of the folder "Merge" and SourceDoc
is the name of the Excelfile that is the data source to the word
mailmerge document.- Dölj citerad text -

- Visa citerad text -

[tskogstrom]

Thank your for your kindness!

Should I add the
"ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument"
code
to run before the rest of the code, to ensure the link ist'n there
next time it opens?

Kind regards
Tskgostrom

[Peter Jamieson]

You can do.

If the user never saves/closes the mail merge main document, there should be
no problem anyway. You could set the .doc as read only to prevent updating.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"tskogstrom" wrote in message
ups.com...
Thank your for your kindness!

Should I add the
"ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument"
code
to run before the rest of the code, to ensure the link ist'n there
next time it opens?

Kind regards
Tskgostrom