Print referenced file
 

Can someone please tell me how I can print a file reference by a hyperlink.
Most of the referenced files are either PDFs or Word documents.
I can get the full file name from the hyperlink but how do I print that file
as if clicking on "Print" on the popup menu in windows explorer. I need to
do this from a macro in Excel.

Thanks for any help,
Fred

Print referenced file
 

Put your hyperlink in some cell; I chose C3, then try something like this:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$C$3" Then ActiveSheet.PrintOut
End Sub

Remember, this is event-ode, so you have to right-click on the tab of the
sheet where you want to run the code. Paste it into the window that opens.

Regards,
Ryan---
--
RyGuy


"Fred" wrote:

Can someone please tell me how I can print a file reference by a hyperlink.
Most of the referenced files are either PDFs or Word documents.
I can get the full file name from the hyperlink but how do I print that file
as if clicking on "Print" on the popup menu in windows explorer. I need to
do this from a macro in Excel.

Thanks for any help,
Fred

Print referenced file
 

Thanks Ryan but I want to print the file that the hyperlink references, not
the worksheet.

Fred

"ryguy7272" wrote in message
...
Put your hyperlink in some cell; I chose C3, then try something like this:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$C$3" Then ActiveSheet.PrintOut
End Sub

Remember, this is event-ode, so you have to right-click on the tab of the
sheet where you want to run the code. Paste it into the window that opens.

Regards,
Ryan---
--
RyGuy


"Fred" wrote:

Can someone please tell me how I can print a file reference by a
hyperlink.
Most of the referenced files are either PDFs or Word documents.
I can get the full file name from the hyperlink but how do I print that
file
as if clicking on "Print" on the popup menu in windows explorer. I need
to
do this from a macro in Excel.

Thanks for any help,
Fred

Print referenced file
 

There's a good example here
http://www.vbaccelerator.com/codelib/shell/shellex.htm

towards the bottom you'll see a sample of how to print, change "Me.Hwnd" to
0&, or application.hwnd (in XL10+).

you might want to change
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_SHOWNORMAL)
to
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_HIDE)

Regards,
Peter T

"Fred" <leavemealone@home wrote in message
...
Can someone please tell me how I can print a file reference by a
hyperlink. Most of the referenced files are either PDFs or Word documents.
I can get the full file name from the hyperlink but how do I print that
file as if clicking on "Print" on the popup menu in windows explorer. I
need to do this from a macro in Excel.

Thanks for any help,
Fred

Print referenced file
 

Thanks Peter.


"Peter T" <peter_t@discussions wrote in message
...
There's a good example here
http://www.vbaccelerator.com/codelib/shell/shellex.htm

towards the bottom you'll see a sample of how to print, change "Me.Hwnd"
to 0&, or application.hwnd (in XL10+).

you might want to change
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_SHOWNORMAL)
to
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_HIDE)

Regards,
Peter T

"Fred" <leavemealone@home wrote in message
...
Can someone please tell me how I can print a file reference by a
hyperlink. Most of the referenced files are either PDFs or Word
documents.
I can get the full file name from the hyperlink but how do I print that
file as if clicking on "Print" on the popup menu in windows explorer. I
need to do this from a macro in Excel.

Thanks for any help,
Fred

Print referenced file
 

Couple of other things you'll need to adapt in the example for VBA for the
error handling -

Change
dim sErr As Long (that must be a typo)
to
sErr As String

after
Else
' raise an appropriate error:
add
On Error Goto errH

change
Err.Raise lErr, , App.EXEName & ".GShell", sErr
to
Err.Raise lErr, , sErr

just before
End Function
add
Exit Function
errH:
MsgBox sFIle & vbCR & Err.Description

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
There's a good example here
http://www.vbaccelerator.com/codelib/shell/shellex.htm

towards the bottom you'll see a sample of how to print, change "Me.Hwnd"
to 0&, or application.hwnd (in XL10+).

you might want to change
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_SHOWNORMAL)
to
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_HIDE)

Regards,
Peter T

Print referenced file
 

Thanks again, with those changes it is working just fine.

"Peter T" <peter_t@discussions wrote in message
...
Couple of other things you'll need to adapt in the example for VBA for the
error handling -

Change
dim sErr As Long (that must be a typo)
to
sErr As String

after
Else
' raise an appropriate error:
add
On Error Goto errH

change
Err.Raise lErr, , App.EXEName & ".GShell", sErr
to
Err.Raise lErr, , sErr

just before
End Function
add
Exit Function
errH:
MsgBox sFIle & vbCR & Err.Description

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
There's a good example here
http://www.vbaccelerator.com/codelib/shell/shellex.htm

towards the bottom you'll see a sample of how to print, change "Me.Hwnd"
to 0&, or application.hwnd (in XL10+).

you might want to change
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_SHOWNORMAL)
to
ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_HIDE)

Regards,
Peter T