Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
![]()
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! |
#2
![]()
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
![]()
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! |
#3
![]()
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
![]()
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! |
#4
![]() |
|||
|
|||
![]()
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
|
Reply |
Thread Tools | |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Mailmerge opens multiple Access | Mailmerge | |||
Word Mailmerge using Excel Data File | Mailmerge | |||
excel hangs in mailmerge | Microsoft Word Help | |||
Another mailmerge from Excel question | Mailmerge | |||
Mailmerge using Excel via DDE | Mailmerge |