?
Solved

How to open a Word doc in VBA (2)

Posted on 2014-11-20
6
Medium Priority
?
167 Views
Last Modified: 2016-02-10
In response to my original question of the same title, hnasr gave me the following code;

    Dim objWord As Object, objDoc As Object
    'Start Word and open a new document
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    Set objDoc = objWord.Documents.Add
    ' existing doc
    Dim objDoc2 As Object
    Set objDoc2 = objWord.Documents.Add(ReportName)

It worked fine when applied to a standard document, but when I opened a document created in Mail Merge, it did not refresh the selected record.

Any advice?

Thanks in advance.
0
Comment
Question by:CRB1609
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 1000 total points
ID: 40456236
"but when I opened a document created in Mail Merge, it did not refresh the selected record."

Now, that can mean many things.
Does it mean that you want to open the master Word document of a mail merge and move to the last record?
Or something else?


    ' existing doc
     Dim objDoc2 As Object
     Set objDoc2 = objWord.Documents.Add(ReportName)
    objDoc2.MailMerge.DataSource.ActiveRecord = wdLastRecord

Open in new window

should do that for you.
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1000 total points
ID: 40457164
The add method opens the file in a new document.

Try to replace :
Set objDoc2 = objWord.Documents.Add(ReportName)
With:
Set objDoc2 = objWord.Documents.Open(ReportName)
 
Then check the added statement in Nick67's comment
0
 

Author Comment

by:CRB1609
ID: 40458943
This is what I came up with, reading all the comments:

Public Function OpenWordDocument()

   Dim objWord As Object
   Dim objDoc As Object
   Set objWord = CreateObject("Word.Application")
   objWord.Visible = True
   Dim objDoc2 As Object
   Set objDoc2 = objWord.Documents.Open("C:\Celebrant Assist V3\User Report 1.docx")
   objDoc2.MailMerge.DataSource.ActiveRecord = wdLastRecord

End Function

If I comment out the last line, it opened an instance of Word containing the data from a test I did directly with Word, but it did not merge with the data in the table.  (There is only 1 record in the table).

If I leave the first line in, it gives a compile error: variable not defined.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:hnasr
ID: 40460403
Try to upload a sample database, with  the required test word documents, in one folder, demonstrating the issue.
This may invites other experts to understand the issue and contribute into its resolution.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40463269
Ok,
Here's my code for working with my mail merger
        
'open the mergedoc
        Dim oApp As Object
        Dim MlMrge As Object
        Set oApp = CreateObject(Class:="Word.Application")
        oApp.Visible = True
        'this will open a new document based on the path as a template.  Excellent
        'but it doesn't open it as a mail merge
        oApp.Documents.Add "U:\All Users\Templates\Field Repair Welding Procedure Merge.doc"
        Set MlMrge = oApp.ActiveDocument.MailMerge
        With MlMrge        
            'do the merge
            .OpenDataSource Name:="C:\temp\tblWeldProcData.xls", Connection:="DSN=Excel Files;DBQ=C:\temp\tblWeldProcData.xls;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", SQLStatement:="SELECT * FROM `tblWeldProcData`", SubType:=0 'wdMergeSubTypeOther
            .ViewMailMergeFieldCodes = 9999998 'wdToggle
            .Destination = 0 'wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = 1 'wdDefaultFirstRecord
                .LastRecord = -16 'wdDefaultLastRecord
            End With
            .Execute (False)
        End With
        'save the merged doc
        oApp.ActiveDocument.SaveAs "c:\temp\Repair Procedure Requests\" & Me.Parent.[wo#] & " RPR.doc"
        Dim Thedoc As Object
        'close the mergedoc and any children.
        For Each Thedoc In oApp.Documents
            If Thedoc.Name Like "document*" Then
                oApp.Documents(Thedoc.Name).Close False
            End If
        Next Thedoc
        oApp.Visible = True
        oApp.Quit
        Set oApp = nothing

Open in new window


So, for you, you'll need to get all the elements in order
Open the app
  Dim oApp As Object
        Dim MlMrge As Object
        Set oApp = CreateObject(Class:="Word.Application")
        oApp.Visible = True

Open your existing merge document
Set objDoc2 = objWord.Documents.Open("C:\Celebrant Assist V3\User Report 1.docx")

Adapt the datasource to your requirements.  In code not shown, I blow the results of a query into Excel to use, and then throw away later.
'do the merge
            .OpenDataSource Name:="C:\temp\tblWeldProcData.xls", Connection:="DSN=Excel Files;DBQ=C:\temp\tblWeldProcData.xls;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", SQLStatement:="SELECT * FROM `tblWeldProcData`", SubType:=0 'wdMergeSubTypeOther
            .ViewMailMergeFieldCodes = 9999998 'wdToggle
            .SuppressBlankLines = True


Then you want to move to the last record
ActiveDocument.ActiveWindow.View.ShowFieldCodes = False
With Documents("Main.doc").MailMerge
    .ViewMailMergeFieldCodes = False
    .DataSource.ActiveRecord = wdLastRecord
End With


And show it
oApp.Visible = True
oApp.UserControl = True


Make sense? Try it and post your results
0
 

Author Closing Comment

by:CRB1609
ID: 40585610
Thank you both.  My aplogies for not closing this sooner.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question