Clive Beaton
asked on
How to open a Word doc in VBA (2)
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.Applica tion")
objWord.Visible = True
Set objDoc = objWord.Documents.Add
' existing doc
Dim objDoc2 As Object
Set objDoc2 = objWord.Documents.Add(Repo rtName)
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.
Dim objWord As Object, objDoc As Object
'Start Word and open a new document
Set objWord = CreateObject("Word.Applica
objWord.Visible = True
Set objDoc = objWord.Documents.Add
' existing doc
Dim objDoc2 As Object
Set objDoc2 = objWord.Documents.Add(Repo
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
This may invites other experts to understand the issue and contribute into its resolution.
Ok,
Here's my code for working with my mail merger
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. Applicatio n")
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\tblWeldProc Data.xls", Connection:="DSN=Excel Files;DBQ=C:\temp\tblWeldP rocData.xl s;DriverId =1046;MaxB ufferSize= 2048;PageT imeout=5;" , SQLStatement:="SELECT * FROM `tblWeldProcData`", SubType:=0 'wdMergeSubTypeOther
.ViewMailMergeFieldCodes = 9999998 'wdToggle
.SuppressBlankLines = True
Then you want to move to the last record
ActiveDocument.ActiveWindo w.View.Sho wFieldCode s = False
With Documents("Main.doc").Mail Merge
.ViewMailMergeFieldCodes = False
.DataSource.ActiveRecord = wdLastRecord
End With
And show it
oApp.Visible = True
oApp.UserControl = True
Make sense? Try it and post your results
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
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.
oApp.Visible = True
Open your existing merge document
Set objDoc2 = objWord.Documents.Open("C:
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\tblWeldProc
.ViewMailMergeFieldCodes = 9999998 'wdToggle
.SuppressBlankLines = True
Then you want to move to the last record
ActiveDocument.ActiveWindo
With Documents("Main.doc").Mail
.ViewMailMergeFieldCodes = False
.DataSource.ActiveRecord = wdLastRecord
End With
And show it
oApp.Visible = True
oApp.UserControl = True
Make sense? Try it and post your results
ASKER
Thank you both. My aplogies for not closing this sooner.
ASKER
Public Function OpenWordDocument()
Dim objWord As Object
Dim objDoc As Object
Set objWord = CreateObject("Word.Applica
objWord.Visible = True
Dim objDoc2 As Object
Set objDoc2 = objWord.Documents.Open("C:
objDoc2.MailMerge.DataSour
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.