Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing a Word Doc (excel 97)

    I know how to use SHELL to open a word document from Excel..
    but how can i print a word document (without printing it) from vba on a button in Excel
    And is there a way to test if word is already open and use that session of word rather than a new one.

    tia

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing a Word Doc (excel 97)

    If I understand you correctly, you want to print the document upon having it opened. Have you tried to include the following code in the word document:

    Private Sub Document_Open()
    ActiveDocument.PrintOut
    End Sub

    In doing so, this will print the word document immediately upon having opened it.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing a Word Doc (excel 97)

    Hi Steve,

    You wrote
    <hr>how can i print a word document (without printing it)<hr>
    Just do nothing? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    But seriously, you could use the ShellExecute WINAPI function. Put the following code in a standard module (the type you create by selecting Insert/Module in the VBE):

    <img src=/w3timages/blueline.gif width=33% height=2>

    Declare Function FindWindow Lib "User32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As Long) As Long

    Declare 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 nShowCmd As Long) As Long

    Public Const SW_SHOWNORMAL = 1
    Public Const SW_SHOWMAXIMIZED As Long = 3

    <img src=/w3timages/blueline.gif width=33% height=2>

    Use code like this in the OnClick event procedure of a command button to print a Word document:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim hWnd As Long
    Dim lngResult As Long
    hWnd = FindWindow("XLMAIN", 0)
    lngResult = ShellExecute(hWnd, "Print", _
    "<path-and-filename>", 0&, 0&, SW_SHOWNORMAL)
    If lngResult <= 32 Then
    MsgBox "Can't print document.", vbExclamation
    End If

    <img src=/w3timages/blueline.gif width=33% height=2>

    (replace <path-and-filename> by the full path and filename of the document you want to print)

    Using ShellExecute with the "Print" argument is the same as right-clicking the document in Windows Explorer and selecting Print. If Word is active, it will use the running instance; if not, it will start Word, print the document, then quit Word.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing a Word Doc (excel 97)

    Hi Hans,

    Why use API ?

    This works equally well (allbeit in a new Word instance):

    Sub OpenDocAndPrint()
    Dim oWd As Object
    Dim oDoc As Object
    Set oWd = CreateObject("Word.application")
    Set oDoc = oWd.Documents.Open(Filename:="C:datatest.doc")
    oDoc.PrintOut
    oDoc.Close savechanges:=False
    Set oDoc = Nothing
    oWd.Quit
    Set oWd = Nothing
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing a Word Doc (excel 97)

    Thanks for the help Guys. I will play with these and see if i can get them to work...

    Hans... you know too much <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing a Word Doc (excel 97)

    Using Automation, as in your example, is another solution.

    It is possible to expand this a little bit to check whether Word is active:

    Sub OpenDocAndPrint()
    Dim oWd As Object
    Dim oDoc As Object
    Dim fNotActive As Boolean
    On Error Resume Next
    Set oWd = GetObject(, "Word.Application")
    If oWd Is Nothing Then
    Set oWd = CreateObject("Word.Application")
    fNotActive = True
    End If
    On Error Goto 0 ' or On Error Goto errorhandler if you have an error handler in this procedure
    Set oDoc = oWd.Documents.Open(Filename:="C:datatest.doc")
    oDoc.PrintOut
    oDoc.Close SaveChanges:=False
    Set oDoc = Nothing
    If fNotActive Then
    oWd.Quit
    End If
    Set oWd = Nothing
    End Sub

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing a Word Doc (excel 97)

    Hi Hans,

    I was too lazy to look up the GetObject method.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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