VBA to print Word file and close is (1 Viewer)

[JPR]

Hello,

I have a form with a VBA code that export data to a MS Word template. After exporting I would like to give the user two options:
1) Open the MS Word document
2) Only print the document without opening it

I am having problems with the second option as it always opens the document and get the save as window. Could it be because it's a template? Thank you

Private Sub cmdExportToWord_Click()

' Code to export and save data to MS Word Template
On Error Resume Next

Dim objWord As Object
Dim strPath As String

Dim AnswerYes As String
Dim AnswerNo As String

strPath = "C:\templates\mytemplate.dot"
Set objWord = CreateObject("Word.Application")
objWord.Visible = False

objWord.Documents.Add strPath

objWord.ActiveDocument.bookmarks("YEAR").Select
objWord.Selection.Text = Me.YEAR

objWord.ActiveDocument.bookmarks("FNAME").Select
objWord.Selection.Text = Me.txtFNAME

objWord.ActiveDocument.bookmarks("LNAME").Select
objWord.Selection.Text = Me.txtLNAME


AnswerYes = MsgBox("Click YES to Open and modify the Word Template or NO to Print", vbQuestion + vbYesNo, "Templates")

If AnswerYes = vbYes Then
objWord.Visible = True

Else

objWord.Visible = False
objWord.PrintOut
objWord.Quit

End If
End Sub

 

[isladogs]

Templates are designed to be read only. Try saving as .docx

 

[arnelgp]

you may try this:

Private Sub cmdExportToWord_Click()
' Code to export and save data to MS Word Template
On Error Resume Next

Dim objWord As Object
Dim wdDoc As Object
Dim strPath As String

Dim AnswerYes As String
Dim AnswerNo As String

strPath = "C:\templates\mytemplate.dot"
Set objWord = CreateObject("Word.Application")
objWord.Visible = False

Set wdDoc = objWord.Documents.Open(strPath)
With wdDoc
    .DisplayAlert = 0
    .Visible = False
    .Bookmarks("Year").Range.Text = Me.Year
    .Bookmarks("FName").Range.Text = Me.FName
    .Bookmarks("LName").Range.Text = Me.LName
End With

AnswerYes = MsgBox("Click YES to Open and modify the Word Template or NO to Print", vbQuestion + vbYesNo, "Templates")

If AnswerYes = vbYes Then
    objWord.Visible = True

Else

    wdDoc.PrintOut
    wdDoc.Close False
    
    objWord.Quit

End If

End Sub

 

[JPR]

Perfect. Works great. Thanks

 

[DickyP]

This thread is a perfect example of the fundamental misuse of Word templates even by experienced Word users. You don't want to open the template but create a new document based on it (which JPR appeared to be doing).

His problem would appear to be he is using Word.PrintOut rather than Document.printout. in the circumstance there Is no current document so Word doesn 't know what print.

 

[Ken Sheridan]

2) Only print the document without opening it

It's often not realised that the Windows API ShellExecute function can be called to print a file as well as opening it. The file can be any type which is capable of being printed:

' module basShellExecute
Option Compare Database
Option Explicit

Public Const SW_HIDE = 0
Public Const SW_SHOWNORMAL = 1
Public Const SW_SHOWMINIMIZED = 2
Public Const SW_SHOWMAXIMIZED = 3
Public Const OP_OPEN = "Open"
Public Const OP_PRINT = "Print"


Declare PtrSafe Function ShellExecute& Lib "shell32.dll" Alias "ShellExecuteA" (ByVal _
hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal _
lpParameters As String, ByVal lpDirectory As String, ByVal nshowcm As Long)


Sub ShellToFile(strPath As String, _
            Optional strOperation As String = OP_OPEN, _
            Optional lngShow As Long = SW_SHOWNORMAL)

    Dim lngRetVal As Long
    Dim lngHwnd As Long
    
    lngHwnd = Application.hWndAccessApp
    
    lngRetVal = ShellExecute(lngHwnd, strOperation, strPath, _
        vbNullString, CurDir, lngShow)
        
    If lngRetVal < 32 Then
        MsgBox "Unable to open/print file " & strPath, vbInformation, "Warning"
    End If
    
End Sub

 

[DickyP]

Kens post above is a great solution, EXCEPT the original posted code didn't have a file to print! A new document cnreated based on a template is an unsaved document. JPR is not opening the document but making it visible. The 'print' option is exactly the same as editing a Word document for real.

 

[Ken Sheridan]

Kens post above is a great solution, EXCEPT the original posted code didn't have a file to print! A new document cnreated based on a template is an unsaved document. JPR is not opening the document but making it visible. The 'print' option is exactly the same as editing a Word document for real.

What you say is correct. I'd assumed from the remark line in the code ' Code to export and save data to MS Word Template that their intention was to save a document based on the template.

 

[DickyP]

I assumed otherwise and that wasn't clear. However, you are just as right as me