How to Open Word file (already having Mailmerged details) from excel

gssachin

Board Regular
Joined
Nov 14, 2013
Messages
155
hi,

I Created one master data in excel which I already link to word file through mailmerged. Now I want to Open that document through excel macro. I created following but it's not working as I required (it open only 1 merged record, I want all records should shown in file when I run macro to open that file) .

Sub marco1()
Dim app As Object
Set app = CreateObject("Word.Application")
app.Documents.Open ("\\Sachin\c\CERTIFICATE\Loan from Bank.doc")
app.Application.DisplayAlerts = None
Visible = True


End Sub

Thanks in advanced.
 
Also, it is weird that the produced document is a long chain of forms that are all the results of my mail merged documents.....I thought it was supposed to be like a) open the already preset document; b) find where the Excel file destination is and establish connection; c) apply the SQL conditions; d) I can use the 'Mailings' ribbon to navigate the letters one by one (coz I need to email them out)
If by that you mean you need to create a separate file for each record, see: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
What was the exact QueryString returned by the macro?
Code:
?activedocument.MailMerge.DataSource.Name
I:\Shared drives\EdUHK SEC Department\SEC Departmental OSP.xlsm

?activedocument.MailMerge.DataSource.ConnectString
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=I:\Shared drives\EdUHK SEC Department\SEC Departmental OSP.xlsm;Mode=Read;Extended Properties="HDR=YES;IMEX=1;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

?activedocument.MailMerge.DataSource.querystring
SELECT * FROM `Guest Speakers$`  WHERE `Status` = 'Approved'



BTW I got it. Somehow! Except one little hiccup......the code that works below now opens 2 documents - 1 that is called 'Form Letters 1' (all MM items in one document......not what I want) and another one a proper MM document (exactly what I want)

Code:
Sub DoMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document, wsName As String
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName

wsName = ActiveWorkbook.Sheets("Guest Speakers").Name

With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open(ThisWorkbook.Path & "\1.2 - Guest Speaker\01 - Approved Guest Speaker Notification.docx", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=False, _
        LinkToSource:=True, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=strWorkbookName;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM`" & wsName & "$`" & "WHERE `Status` = 'Approved'", _
        SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      'Excecute the merge
      .Execute
      'Disconnect from the data source
      '.MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    '.Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Display Word and the document
  .Visible = True
End With
End Sub
 
Upvote 0
It 'worked' because you changed the 'WHERE' part of the query string you used between posts 17, 19 and 22, from:
WHERE `Status` = `Approved`
and:
WHERE `Status` = Approved
to:
WHERE `Status` = 'Approved'
 
Upvote 0
It 'worked' because you changed the 'WHERE' part of the query string you used between posts 17, 19 and 22, from:
WHERE `Status` = `Approved`
and:
WHERE `Status` = Approved
to:
WHERE `Status` = 'Approved'

Yes. I noticed that now.

Thanks Paul. But just that one little issue I had. I'm not executing my code and it always opens with 2 documents. The one that joins and the one that doesn't. Can I just have the one that doesn't join?
 
Upvote 0
Yes. I noticed that now.

Thanks Paul. But just that one little issue I had. I'm not executing my code and it always opens with 2 documents. The one that joins and the one that doesn't. Can I just have the one that doesn't join?



I think I got it. It's the execute command that 'simulates' the activation of the mail merge (joining the files into a single letter). Removing it worked fine!

Here is the code for those who wanna use it
Code:
Sub DoMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document, wsName As String
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName

wsName = ActiveWorkbook.Sheets("Guest Speakers").Name

With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open(ThisWorkbook.Path & "\1.2 - Guest Speaker\01 - Approved Guest Speaker Notification.docx", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .SuppressBlankLines = False
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=False, _
        LinkToSource:=True, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=strWorkbookName;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM`" & wsName & "$`" & "WHERE `Status` = 'Delivered'", _
        SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      'Excecute the merge
      '.Execute
      'Disconnect from the data source
      '.MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    '.Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Display Word and the document
  .Visible = True
End With
End Sub

Thank you Paul. This isn't the first time you've helped me out, actually. Thank you very much
 
Last edited:
Upvote 0
You end up with two open documents because you've commented-out the code that closes the mailmerge main document...
 
Last edited:
Upvote 0
I think I got it. It's the execute command that 'simulates' the activation of the mail merge (joining the files into a single letter). Removing it worked fine!
Do NOT do that!!! You'll inevitably lose the merged data if you do.
 
Upvote 0
You end up with two open documents because you've commented-out the code that closes the mailmerge main document...
Yes because I figured that I should comment it out so that I won't close the mailmerge main document - which is the one I want only....
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top