[Simon Cleal]

Word/Excel 2000

Hi,

I'm using the following code to open a Word mailmerge doc (with an Excel
data source) from Excel:

Public Sub Merge()
Dim objWord As Word.Application

On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If
On Error GoTo 0

objWord.Application.Visible = True
objWord.Documents.Open "T:\APAS\Letters\NR.doc"
Set objWord = Nothing

End Sub

In NR.doc I have this code to merge to a new doc and save the resulting doc:

Private Sub Document_Open()
Application.ScreenUpdating = False
Application.OnTime When:=Now + TimeValue("00:00:00"), Name:="MergeDoc"
End Sub

Public Sub MergeDoc()
Application.ScreenUpdating = False
Dim strOriginalDoc As String
Dim strDocSuffix As String * 1
Dim strOutputDoc As String
Dim strDocName As String
Dim objField As MailMergeDataField

strOriginalDoc = ActiveDocument.Name

'pick up the file name from the member no
For Each objField In ActiveDocument.MailMerge.DataSource.DataFields
Select Case objField.Name
Case Is = "MemberNo"
strDocName = objField.Value & " NR "
End Select
Next objField
strDocSuffix = " "
strOutputDoc = Trim(strDocName & strDocSuffix)

'files sytem object
Dim objFileSystem As Object
Set objFileSystem = CreateObject("Scripting.FileSystemObject")

'test for file existence
While objFileSystem.fileexists("T:\APAS\FinsihedLetters\ " & strOutputDoc
& ".doc") = True
Dim intDocSuffix As Integer
intDocSuffix = Asc(strDocSuffix)
If intDocSuffix = 32 Then
intDocSuffix = 65
Else
intDocSuffix = intDocSuffix + 1
End If
strDocSuffix = Chr(intDocSuffix)
strOutputDoc = Trim(strDocName & strDocSuffix)
Wend
strDocRef = Trim(strDocRef & " " & strDocSuffix)
'merge & Save the Doc
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

ActiveDocument.SaveAs FileName:="T:\APAS\FinishedLetters\" & strOutputDoc
Documents(strOriginalDoc).Close False
End Sub



The code mostly works but...

....when word opens the mail merge doc it also opens a 2nd Excel application,
this 2nd Excel automatically tries to open my personal macro workbook
(PERSONAL.xls) which is in use by the original Excel application resulting in
a Open Read Only, Retry, Cancel message box which has to be answered before
the merge doc will open. It also opens a new Book1.xls which owing to my
default Excel workbook has changed and so Excel wants to save changes before
it will close down the mailmerge data source and the 2nd Excel app. This
severly slows down the whole process and rather defeats the object of using
VBA.

When the document is opened 'by hand' everything runs smoothly

Is there a way to 'force' Word to open the Excel data source in the existing
Excel app rather than start a new one?


Thanks in advance for any help

Simon

PS sorry if this post is long winded but I figure better too much info than
too little!

[Peter Jamieson]

1. Could anything be blocking the opening of a new DDE connection to Excel?
Not sure what it might be, but for example an open Excel dialog box, that
kind of thing?

2. Is it an option for you to connect to the data source programmatically
(i.e. save the document without the data source and use OpenDataSource to
open it)? I haven't tested that, but wonder whether Word might open it in
the same instance as your existing Excel document in that case. Doubt it,
but perhaps worth a try.

3. If you do not absolutely need the cosy "get the data more or less as
displayed in Excel" behaviour of a DDE connection and you do not need to
have your data source open in Excel at the same time as you connect to it
from Word, you could consider
a. using an ODBC connection instead
b. applying any necessary formatting switches in Word

Peter Jamieson
"Simon Cleal" wrote in message
...
Word/Excel 2000

Hi,

I'm using the following code to open a Word mailmerge doc (with an Excel
data source) from Excel:

Public Sub Merge()
Dim objWord As Word.Application

On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If
On Error GoTo 0

objWord.Application.Visible = True
objWord.Documents.Open "T:\APAS\Letters\NR.doc"
Set objWord = Nothing

End Sub

In NR.doc I have this code to merge to a new doc and save the resulting
doc:

Private Sub Document_Open()
Application.ScreenUpdating = False
Application.OnTime When:=Now + TimeValue("00:00:00"), Name:="MergeDoc"
End Sub

Public Sub MergeDoc()
Application.ScreenUpdating = False
Dim strOriginalDoc As String
Dim strDocSuffix As String * 1
Dim strOutputDoc As String
Dim strDocName As String
Dim objField As MailMergeDataField

strOriginalDoc = ActiveDocument.Name

'pick up the file name from the member no
For Each objField In ActiveDocument.MailMerge.DataSource.DataFields
Select Case objField.Name
Case Is = "MemberNo"
strDocName = objField.Value & " NR "
End Select
Next objField
strDocSuffix = " "
strOutputDoc = Trim(strDocName & strDocSuffix)

'files sytem object
Dim objFileSystem As Object
Set objFileSystem = CreateObject("Scripting.FileSystemObject")

'test for file existence
While objFileSystem.fileexists("T:\APAS\FinsihedLetters\ " &
strOutputDoc
& ".doc") = True
Dim intDocSuffix As Integer
intDocSuffix = Asc(strDocSuffix)
If intDocSuffix = 32 Then
intDocSuffix = 65
Else
intDocSuffix = intDocSuffix + 1
End If
strDocSuffix = Chr(intDocSuffix)
strOutputDoc = Trim(strDocName & strDocSuffix)
Wend
strDocRef = Trim(strDocRef & " " & strDocSuffix)
'merge & Save the Doc
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

ActiveDocument.SaveAs FileName:="T:\APAS\FinishedLetters\" &
strOutputDoc
Documents(strOriginalDoc).Close False
End Sub



The code mostly works but...

...when word opens the mail merge doc it also opens a 2nd Excel
application,
this 2nd Excel automatically tries to open my personal macro workbook
(PERSONAL.xls) which is in use by the original Excel application resulting
in
a Open Read Only, Retry, Cancel message box which has to be answered
before
the merge doc will open. It also opens a new Book1.xls which owing to my
default Excel workbook has changed and so Excel wants to save changes
before
it will close down the mailmerge data source and the 2nd Excel app. This
severly slows down the whole process and rather defeats the object of
using
VBA.

When the document is opened 'by hand' everything runs smoothly

Is there a way to 'force' Word to open the Excel data source in the
existing
Excel app rather than start a new one?


Thanks in advance for any help

Simon

PS sorry if this post is long winded but I figure better too much info
than
too little!

[Simon Cleal]

Thanks Peter, option 2 did the trick

"Peter Jamieson" wrote:

1. Could anything be blocking the opening of a new DDE connection to Excel?
Not sure what it might be, but for example an open Excel dialog box, that
kind of thing?

2. Is it an option for you to connect to the data source programmatically
(i.e. save the document without the data source and use OpenDataSource to
open it)? I haven't tested that, but wonder whether Word might open it in
the same instance as your existing Excel document in that case. Doubt it,
but perhaps worth a try.

3. If you do not absolutely need the cosy "get the data more or less as
displayed in Excel" behaviour of a DDE connection and you do not need to
have your data source open in Excel at the same time as you connect to it
from Word, you could consider
a. using an ODBC connection instead
b. applying any necessary formatting switches in Word

Peter Jamieson
"Simon Cleal" wrote in message
...
Word/Excel 2000

Hi,

I'm using the following code to open a Word mailmerge doc (with an Excel
data source) from Excel:

Public Sub Merge()
Dim objWord As Word.Application

On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If
On Error GoTo 0

objWord.Application.Visible = True
objWord.Documents.Open "T:\APAS\Letters\NR.doc"
Set objWord = Nothing

End Sub

In NR.doc I have this code to merge to a new doc and save the resulting
doc:

Private Sub Document_Open()
Application.ScreenUpdating = False
Application.OnTime When:=Now + TimeValue("00:00:00"), Name:="MergeDoc"
End Sub

Public Sub MergeDoc()
Application.ScreenUpdating = False
Dim strOriginalDoc As String
Dim strDocSuffix As String * 1
Dim strOutputDoc As String
Dim strDocName As String
Dim objField As MailMergeDataField

strOriginalDoc = ActiveDocument.Name

'pick up the file name from the member no
For Each objField In ActiveDocument.MailMerge.DataSource.DataFields
Select Case objField.Name
Case Is = "MemberNo"
strDocName = objField.Value & " NR "
End Select
Next objField
strDocSuffix = " "
strOutputDoc = Trim(strDocName & strDocSuffix)

'files sytem object
Dim objFileSystem As Object
Set objFileSystem = CreateObject("Scripting.FileSystemObject")

'test for file existence
While objFileSystem.fileexists("T:\APAS\FinsihedLetters\ " &
strOutputDoc
& ".doc") = True
Dim intDocSuffix As Integer
intDocSuffix = Asc(strDocSuffix)
If intDocSuffix = 32 Then
intDocSuffix = 65
Else
intDocSuffix = intDocSuffix + 1
End If
strDocSuffix = Chr(intDocSuffix)
strOutputDoc = Trim(strDocName & strDocSuffix)
Wend
strDocRef = Trim(strDocRef & " " & strDocSuffix)
'merge & Save the Doc
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

ActiveDocument.SaveAs FileName:="T:\APAS\FinishedLetters\" &
strOutputDoc
Documents(strOriginalDoc).Close False
End Sub



The code mostly works but...

...when word opens the mail merge doc it also opens a 2nd Excel
application,
this 2nd Excel automatically tries to open my personal macro workbook
(PERSONAL.xls) which is in use by the original Excel application resulting
in
a Open Read Only, Retry, Cancel message box which has to be answered
before
the merge doc will open. It also opens a new Book1.xls which owing to my
default Excel workbook has changed and so Excel wants to save changes
before
it will close down the mailmerge data source and the 2nd Excel app. This
severly slows down the whole process and rather defeats the object of
using
VBA.

When the document is opened 'by hand' everything runs smoothly

Is there a way to 'force' Word to open the Excel data source in the
existing
Excel app rather than start a new one?


Thanks in advance for any help

Simon

PS sorry if this post is long winded but I figure better too much info
than
too little!

[Margaret9121]

The Bomaker Sound Bar is a great option for those who want a compact, portable soundbar that can deliver big sound. This 16-inch soundbar features Bluetooth connectivity, as well as an AUX and RCA input. dialog enhancement soundbar