Printing documents from an Access form?

[Tony Williams]

I have an Access database that we use as a document index system. The
documents can be Word, Excel, pdf's etc I have a command button on a form
that opens the document in whatever program is relevant. The code I use
is
Private Sub Cmdstart_Click()
On Error GoTo Err_Handler

Dim strPath As String
If IsNull(DocURLtxt.Value) Then
strmsg = "You must enter the Document URL to use this function
'" & DocURLtxt.Value _
& "'." & vbCrLf & "Please enter the URL for " _
& "this document now."
If MsgBox(strmsg, vbQuestion) = vbOK Then
Exit Sub
End If
End If
strPath = Nz(Me!DocURLtxt, "")
If Len(strPath) > 0 Then
StartFile strPath, WIN_NORMAL
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

However I would like another button that actually sends the document
straight to print without having to open the document in the application.
For example if we have the path to a Word document I want the command button
to send the document to a printer, or at least open a print dialog box
without opening the document in Word first and then printing from there. Is
this possible?

As ever TIA
Tony Williams

[MacDermott]

As I understand it, you will have to open the document before it can be
printed.
However, you don't have to display it on the screen.

HTH
- Turtle

"Tony Williams" <[e-mail]> wrote in message
news:c0**********@sparta.btinternet.com...

I have an Access database that we use as a document index system. The
documents can be Word, Excel, pdf's etc I have a command button on a form
that opens the document in whatever program is relevant. The code I use
is
Private Sub Cmdstart_Click()
On Error GoTo Err_Handler

Dim strPath As String
If IsNull(DocURLtxt.Value) Then
strmsg = "You must enter the Document URL to use this function
'" & DocURLtxt.Value _
& "'." & vbCrLf & "Please enter the URL for " _
& "this document now."
If MsgBox(strmsg, vbQuestion) = vbOK Then
Exit Sub
End If
End If
strPath = Nz(Me!DocURLtxt, "")
If Len(strPath) > 0 Then
StartFile strPath, WIN_NORMAL
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

However I would like another button that actually sends the document
straight to print without having to open the document in the application.
For example if we have the path to a Word document I want the command button to send the document to a printer, or at least open a print dialog box
without opening the document in Word first and then printing from there. Is this possible?

As ever TIA
Tony Williams

[Fletcher Arnold]

"Tony Williams" <[e-mail]> wrote in message
news:c0**********@sparta.btinternet.com...

I have an Access database that we use as a document index system. The
documents can be Word, Excel, pdf's etc I have a command button on a form
that opens the document in whatever program is relevant. The code I use
is
Private Sub Cmdstart_Click()
On Error GoTo Err_Handler

Dim strPath As String
If IsNull(DocURLtxt.Value) Then
strmsg = "You must enter the Document URL to use this function
'" & DocURLtxt.Value _
& "'." & vbCrLf & "Please enter the URL for " _
& "this document now."
If MsgBox(strmsg, vbQuestion) = vbOK Then
Exit Sub
End If
End If
strPath = Nz(Me!DocURLtxt, "")
If Len(strPath) > 0 Then
StartFile strPath, WIN_NORMAL
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

However I would like another button that actually sends the document
straight to print without having to open the document in the application.
For example if we have the path to a Word document I want the command button to send the document to a printer, or at least open a print dialog box
without opening the document in Word first and then printing from there. Is this possible?

As ever TIA
Tony Williams

Hi Tony
Yes it's possible, but you could spend a while writing the code. Here are a
few thoughts:

The MS Office documents (Word, Excel, etc) would best be printed out using
automation. For example, you create a hidden instance of word, open the
document and print it out. Because these applications have well-documented
object models, the coding for this is fairly standard and works well where
Office is installed on each PC.

You may need a different approach to non-Office files such as .pdf where I
believe you can pass the name of the file, and a printout command in the
switches used for the .exe file.

The following is a start - and is only intended as a start. Here, each time
you print out, a new instance of Word or Excel is started and then shut
down. If speed were to become an issue, then you could change this to keep
Word running (but would you really want many additional Office applications
open considering you're already using Access?)

Copy the following into a module called modPrintout or whatever:
Public Sub PrintFile(strPath As String)

On Error GoTo Err_Handler

Select Case Right$(strPath, 4)

Case ".doc"
PrintWord strPath

Case ".xls"
PrintExcel strPath

Case Else
MsgBox "File=""" & strPath & """" & vbCrLf & _
"This application cannot automatically" & vbCrLf & _
"print files of this type.", vbExclamation, _
"Unknown File Type"

End Select

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub PrintWord(strPath As String)

On Error GoTo Err_Handler

Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.Application")

Set wdDoc = wdApp.Documents.Open(strPath)

wdDoc.PrintOut False

Exit_Handler:

On Error Resume Next

wdDoc.Close

Set wdDoc = Nothing

wdApp.Quit

Set wdApp = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub PrintExcel(strPath As String)

On Error GoTo Err_Handler

Dim xlApp As Object
Dim xlWbk As Object

Set xlApp = CreateObject("Excel.Application")

Set xlWbk = xlApp.Workbooks.Open(strPath)

xlWbk.PrintOut

Exit_Handler:

On Error Resume Next

xlWbk.Close

Set xlWbk = Nothing

xlApp.Quit

Set xlApp = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Tony Williams]

Thanks Fletcher I'm going to try that. As most of the files will be from
Office applications it seems the user is quite happy that for others like
pdfs they open Acrobat and then print.
Again as ever a BIG thank you and I'll come back if there are any problems
if I may.
Cheers
Tony
"Fletcher Arnold" <fl****@home.com> wrote in message
news:c0**********@sparta.btinternet.com...

"Tony Williams" <[e-mail]> wrote in message
news:c0**********@sparta.btinternet.com...

I have an Access database that we use as a document index system. The
documents can be Word, Excel, pdf's etc I have a command button on a form that opens the document in whatever program is relevant. The code I use
is
Private Sub Cmdstart_Click()
On Error GoTo Err_Handler

Dim strPath As String
If IsNull(DocURLtxt.Value) Then
strmsg = "You must enter the Document URL to use this function '" & DocURLtxt.Value _
& "'." & vbCrLf & "Please enter the URL for " _
& "this document now."
If MsgBox(strmsg, vbQuestion) = vbOK Then
Exit Sub
End If
End If
strPath = Nz(Me!DocURLtxt, "")
If Len(strPath) > 0 Then
StartFile strPath, WIN_NORMAL
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

However I would like another button that actually sends the document
straight to print without having to open the document in the application. For example if we have the path to a Word document I want the command button

to send the document to a printer, or at least open a print dialog box
without opening the document in Word first and then printing from there.

Is

this possible?

As ever TIA
Tony Williams

Hi Tony
Yes it's possible, but you could spend a while writing the code. Here are

a few thoughts:

The MS Office documents (Word, Excel, etc) would best be printed out using
automation. For example, you create a hidden instance of word, open the
document and print it out. Because these applications have well-documented object models, the coding for this is fairly standard and works well where
Office is installed on each PC.

You may need a different approach to non-Office files such as .pdf where I
believe you can pass the name of the file, and a printout command in the
switches used for the .exe file.

The following is a start - and is only intended as a start. Here, each time you print out, a new instance of Word or Excel is started and then shut
down. If speed were to become an issue, then you could change this to keep Word running (but would you really want many additional Office applications open considering you're already using Access?)

Copy the following into a module called modPrintout or whatever:
Public Sub PrintFile(strPath As String)

On Error GoTo Err_Handler

Select Case Right$(strPath, 4)

Case ".doc"
PrintWord strPath

Case ".xls"
PrintExcel strPath

Case Else
MsgBox "File=""" & strPath & """" & vbCrLf & _
"This application cannot automatically" & vbCrLf & _
"print files of this type.", vbExclamation, _
"Unknown File Type"

End Select

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub PrintWord(strPath As String)

On Error GoTo Err_Handler

Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.Application")

Set wdDoc = wdApp.Documents.Open(strPath)

wdDoc.PrintOut False

Exit_Handler:

On Error Resume Next

wdDoc.Close

Set wdDoc = Nothing

wdApp.Quit

Set wdApp = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub PrintExcel(strPath As String)

On Error GoTo Err_Handler

Dim xlApp As Object
Dim xlWbk As Object

Set xlApp = CreateObject("Excel.Application")

Set xlWbk = xlApp.Workbooks.Open(strPath)

xlWbk.PrintOut

Exit_Handler:

On Error Resume Next

xlWbk.Close

Set xlWbk = Nothing

xlApp.Quit

Set xlApp = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Tony Williams]

Fletcher can I create this as a function called say Printdoc and then put
=Printdoc in the Onclick of my button? If so how do I change the Publicsub
to a function?
TIA
Tony Williams
"Tony Williams" <[e-mail]> wrote in message
news:c1**********@hercules.btinternet.com...

Thanks Fletcher I'm going to try that. As most of the files will be from
Office applications it seems the user is quite happy that for others like
pdfs they open Acrobat and then print.
Again as ever a BIG thank you and I'll come back if there are any problems
if I may.
Cheers
Tony
"Fletcher Arnold" <fl****@home.com> wrote in message
news:c0**********@sparta.btinternet.com...

"Tony Williams" <[e-mail]> wrote in message
news:c0**********@sparta.btinternet.com...

I have an Access database that we use as a document index system. The
documents can be Word, Excel, pdf's etc I have a command button on a form that opens the document in whatever program is relevant. The code I use is
Private Sub Cmdstart_Click()
On Error GoTo Err_Handler

Dim strPath As String
If IsNull(DocURLtxt.Value) Then
strmsg = "You must enter the Document URL to use this function '" & DocURLtxt.Value _
& "'." & vbCrLf & "Please enter the URL for " _
& "this document now."
If MsgBox(strmsg, vbQuestion) = vbOK Then
Exit Sub
End If
End If
strPath = Nz(Me!DocURLtxt, "")
If Len(strPath) > 0 Then
StartFile strPath, WIN_NORMAL
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

However I would like another button that actually sends the document
straight to print without having to open the document in the application. For example if we have the path to a Word document I want the command button

to send the document to a printer, or at least open a print dialog box
without opening the document in Word first and then printing from

there. Is

this possible?

As ever TIA
Tony Williams

Hi Tony
Yes it's possible, but you could spend a while writing the code. Here are a

few thoughts:

The MS Office documents (Word, Excel, etc) would best be printed out

using automation. For example, you create a hidden instance of word, open the
document and print it out. Because these applications have

well-documented

object models, the coding for this is fairly standard and works well where Office is installed on each PC.

You may need a different approach to non-Office files such as .pdf where I believe you can pass the name of the file, and a printout command in the
switches used for the .exe file.

The following is a start - and is only intended as a start. Here, each

time

you print out, a new instance of Word or Excel is started and then shut
down. If speed were to become an issue, then you could change this to

keep

Word running (but would you really want many additional Office

applications

open considering you're already using Access?)

Copy the following into a module called modPrintout or whatever:
Public Sub PrintFile(strPath As String)

On Error GoTo Err_Handler

Select Case Right$(strPath, 4)

Case ".doc"
PrintWord strPath

Case ".xls"
PrintExcel strPath

Case Else
MsgBox "File=""" & strPath & """" & vbCrLf & _
"This application cannot automatically" & vbCrLf & _
"print files of this type.", vbExclamation, _
"Unknown File Type"

End Select

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub PrintWord(strPath As String)

On Error GoTo Err_Handler

Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.Application")

Set wdDoc = wdApp.Documents.Open(strPath)

wdDoc.PrintOut False

Exit_Handler:

On Error Resume Next

wdDoc.Close

Set wdDoc = Nothing

wdApp.Quit

Set wdApp = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub PrintExcel(strPath As String)

On Error GoTo Err_Handler

Dim xlApp As Object
Dim xlWbk As Object

Set xlApp = CreateObject("Excel.Application")

Set xlWbk = xlApp.Workbooks.Open(strPath)

xlWbk.PrintOut

Exit_Handler:

On Error Resume Next

xlWbk.Close

Set xlWbk = Nothing

xlApp.Quit

Set xlApp = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Fletcher Arnold]

"Tony Williams" <[e-mail]> wrote in message
news:c1**********@sparta.btinternet.com...

Fletcher can I create this as a function called say Printdoc and then put
=Printdoc in the Onclick of my button? If so how do I change the Publicsub
to a function?
TIA
Tony Williams

I would not change it to a function. If the button was named "cmdPrint",
here is the code I might have behind the button's OnClick event:

Private Sub cmdPrint_Click()

On Error GoTo Err_Handler

If Not IsNull(Me!DocPath) Then
PrintFile(Me!DocPath)
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Why use a function? I guess you like simply writing =DoSomething() into the
properties window for the button, but why? I never do that. For one thing,
you don't find out about any errors until you press the button - so that if
you had written =PryntDoc() by mistake this might go un-noticed whereas if
you had written it in code you would immediately see a compile error. Also,
what happens when you change parameters?

All this is not a sub versus function argument, by all means convert it to a
function but unless the function returns a meaningful value (eg whether the
doc printed without error), I don't see any point unless you want to avoid
code-writing which is a bit corner-cutting.
Fletcher

[Tony Williams]

Thanks Fletcher for the sound advice. It isn't corner cutting it's just that
my 59 year old brain has difficulty getting around functions and modules! I
have tried reading Microsoft's Running Access 2000 and Que's Using Access
2000 and a couple of others but I find it both books don't really explain
the relationship between the two and how you should one to make the other
run. Can you suggest any simple 123 steps books I could read. As you will
realise I'm a newbie and totally self taught. Thanks
Tony
"Fletcher Arnold" <fl****@home.com> wrote in message
news:c1**********@hercules.btinternet.com...

"Tony Williams" <[e-mail]> wrote in message
news:c1**********@sparta.btinternet.com...

Fletcher can I create this as a function called say Printdoc and then put =Printdoc in the Onclick of my button? If so how do I change the Publicsub to a function?
TIA
Tony Williams
I would not change it to a function. If the button was named "cmdPrint",
here is the code I might have behind the button's OnClick event:

Private Sub cmdPrint_Click()

On Error GoTo Err_Handler

If Not IsNull(Me!DocPath) Then
PrintFile(Me!DocPath)
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Why use a function? I guess you like simply writing =DoSomething() into

the properties window for the button, but why? I never do that. For one thing, you don't find out about any errors until you press the button - so that if you had written =PryntDoc() by mistake this might go un-noticed whereas if
you had written it in code you would immediately see a compile error. Also, what happens when you change parameters?

All this is not a sub versus function argument, by all means convert it to a function but unless the function returns a meaningful value (eg whether the doc printed without error), I don't see any point unless you want to avoid
code-writing which is a bit corner-cutting.
Fletcher

[Tony Williams]

Thanks Fletcher that worked a treat!
Once again I am indebted to you for your help. I've built about three
databases and I think there must be a bit of your coding in everyone!!!
Tony
"Fletcher Arnold" <fl****@home.com> wrote in message
news:c1**********@hercules.btinternet.com...

"Tony Williams" <[e-mail]> wrote in message
news:c1**********@sparta.btinternet.com...

Fletcher can I create this as a function called say Printdoc and then put =Printdoc in the Onclick of my button? If so how do I change the Publicsub to a function?
TIA
Tony Williams
I would not change it to a function. If the button was named "cmdPrint",
here is the code I might have behind the button's OnClick event:

Private Sub cmdPrint_Click()

On Error GoTo Err_Handler

If Not IsNull(Me!DocPath) Then
PrintFile(Me!DocPath)
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Why use a function? I guess you like simply writing =DoSomething() into

the properties window for the button, but why? I never do that. For one thing, you don't find out about any errors until you press the button - so that if you had written =PryntDoc() by mistake this might go un-noticed whereas if
you had written it in code you would immediately see a compile error. Also, what happens when you change parameters?

All this is not a sub versus function argument, by all means convert it to a function but unless the function returns a meaningful value (eg whether the doc printed without error), I don't see any point unless you want to avoid
code-writing which is a bit corner-cutting.
Fletcher

[Fletcher Arnold]

"Tony Williams" <[e-mail]> wrote in message
news:c1**********@hercules.btinternet.com...

Thanks Fletcher that worked a treat!
Once again I am indebted to you for your help. I've built about three
databases and I think there must be a bit of your coding in everyone!!!
Tony

Hi Tony
I'm really pleased you've got it working.
This sort of feedback is what keeps people posting answers!

Many people here like the Access developer's Handbook but I don't think it
is the best starting point for writing code
http://www.amazon.com/exec/obidos/tg...88636?v=glance
At a more basic level is the following which I thought was good:
http://www.amazon.com/exec/obidos/tg...88636?v=glance
Anyway, definitely have a bookshop browse next time you're in town.

Returning to an earlier point, the decision whether to use a function or a
sub often only amounts to how
you choose to structure your code. The general rule is that functions are
used to return values whereas subs do not.

This distinction is not always true. For example, you could write this:

Public Function SayHi()
MsgBox "Hi"
End Function
More normal, might be a function which returns an actual value, eg:

Public Function GetUserName(strPrompt As String) As String

Dim strName As String

strName = InputBox(strPrompt)

If Len(strName) = 0 Then
strName = "Unknown"
End If

GetUserName = strName

End Function
This you can then call from elsewhere in your code, eg:

strUser = GetUserName("What is your name?")

HTH

Fletcher

[Tony Williams]

Thanks Fletcher Ill have a look at the books you suggest and aim for
Waterstones the next time I get to town(Chester)!
I've printed off your example so that I can keep referring to it, I'll get
there!
Thanks again
Tony
"Fletcher Arnold" <fl****@home.com> wrote in message
news:c1**********@sparta.btinternet.com...

"Tony Williams" <[e-mail]> wrote in message
news:c1**********@hercules.btinternet.com...

Thanks Fletcher that worked a treat!
Once again I am indebted to you for your help. I've built about three
databases and I think there must be a bit of your coding in everyone!!!
Tony

Hi Tony
I'm really pleased you've got it working.
This sort of feedback is what keeps people posting answers!

Many people here like the Access developer's Handbook but I don't think it
is the best starting point for writing code

http://www.amazon.com/exec/obidos/tg...767761-5488636
?v=glance At a more basic level is the following which I thought was good:
http://www.amazon.com/exec/obidos/tg...767761-5488636
?v=glance Anyway, definitely have a bookshop browse next time you're in town.

Returning to an earlier point, the decision whether to use a function or a
sub often only amounts to how
you choose to structure your code. The general rule is that functions are
used to return values whereas subs do not.

This distinction is not always true. For example, you could write this:

Public Function SayHi()
MsgBox "Hi"
End Function
More normal, might be a function which returns an actual value, eg:

Public Function GetUserName(strPrompt As String) As String

Dim strName As String

strName = InputBox(strPrompt)

If Len(strName) = 0 Then
strName = "Unknown"
End If

GetUserName = strName

End Function
This you can then call from elsewhere in your code, eg:

strUser = GetUserName("What is your name?")

HTH

Fletcher