Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: How to Open Word file (already having Mailmerged details) from excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular gssachin's Avatar
    Join Date
    Nov 2013
    Location
    Kuwait
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,290
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    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
    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
    Last edited by Macropod; Feb 19th, 2017 at 04:57 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Board Regular gssachin's Avatar
    Join Date
    Nov 2013
    Location
    Kuwait
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Last edited by gssachin; Nov 6th, 2016 at 03:09 AM.

  4. #4
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,290
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Board Regular gssachin's Avatar
    Join Date
    Nov 2013
    Location
    Kuwait
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,290
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    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$`", _
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    Board Regular gssachin's Avatar
    Join Date
    Nov 2013
    Location
    Kuwait
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 ???

  8. #8
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,290
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    Board Regular gssachin's Avatar
    Join Date
    Nov 2013
    Location
    Kuwait
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  10. #10
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,290
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •