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

[gssachin]

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.

 

[Macropod]

If the document you're opening is the mailmerge main document, rather than a file produced by a mailmerge, you don't even have one record - all you have is a mailmerge preview that has been disconnected from the data source. A mailmerge must be executed before a document with all records can come into existence. As it is, if your document is the mailmerge main document, you'd normally be getting an SQL prompt when you open it. Until that is answered, the merge can't execute. However, using Application.DisplayAlerts = False (not None) both prevents the SQL prompt and causes Word to disconnect it from the data source. Consequently, you need to both add the code to turn your document back into a mailmerge main document, supply the SQL string, then execute the merge. Try something along the lines of the following. As you'll see, it's a whole lot more involved than what you have.

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
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open("\\Sachin\c\CERTIFICATE\Loan from Bank.doc", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, 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 `Sheet1$`", _
        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

 

[gssachin]

Thanks for reply.

But I m getting following error
Compile Error:
User-defined type not defined

For your Ref : I Have office 2007 on my PC, I m using Excel File 2007 & Output File Word 2003

 

[Macropod]

Did you read the line above the one that gave the error? - the comment line that says:
'Note: A VBA Reference to the Word Object Model is required, via Tools|References

 

[gssachin]

Dear Sir,

Sorry I read the line but not understand. Since I m new in macro I'm unaware of VBA Reference,

I went to Tool > Reference but still not found. Is there any way to download it from internet ???

Please guide me

 

[Macropod]

No, you cannot download it from the internet - it's part of Office, which you already have. Go to Tools|References, then scroll down till you find the entry for the Microsoft Word object library and click on the checkbox.

Note also that the code assumes your data are on a worksheet named Sheet1. If your worksheet has a different name change Sheet1 in the code to that name. You'll see it on the line:
SQLStatement:="SELECT * FROM `Sheet1$`", _

 

[gssachin]

Sir I got it. Thanks a lot

One help After opening word document I want to run Macro1 (word macro). Can this process also included in above macro ???

 

[Macropod]

It would probably be simpler to incorporate the code into the code I provided. That way, you can more easily control whether it runs before/after re-establishing the mailmerge connection or, perhaps, on the output document instead of the mailmerge main document.

 

[gssachin]

I have following macro in word document. to change font of whole document also to I want date format 07th November 2016 (in that "th" is superscript) due to mailmerge I m not getting so done following trick to find & replace the word

How to add this in above macro ???

Sub FindAndReplacedate()


Application.EnableCancelKey = xlDisabled
Selection.WholeStory
Selection.Font.Name = "Times New Roman"


With Selection.Find
.Text = "s--t"
.Replacement.Text = "ST"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Replacement.Font.Superscript = True
.Execute Replace:=wdReplaceAll
.MatchWildcards = True
.Execute Replace:=wdReplaceAll


.Text = "t--h"
.Replacement.Text = "TH"
.Execute Replace:=wdReplaceAll


.Text = "r--d"
.Replacement.Text = "RD"
.Execute Replace:=wdReplaceAll


.Text = "n--d"
.Replacement.Text = "ND"
.Execute Replace:=wdReplaceAll




End With
End Sub

 

[Macropod]

You should not be using a macro to change the font - you should be doing that once, in the mailmerge main document directly and preferably by editing the relevant Styles.

As for your Find/Replace code, none of that is needed if your mailmerge main document uses a suitably-coded field for the date display. To see how to do this and just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at:
Microsoft Word Date Calculation Tutorial | Windows Secrets Lounge
or:
Graham Mayor - Downloads
In particular, look at the item titled 'Insert A Date with Ordinal Numbering'. Do read the document's introductory material.