Print referenced file

[Fred]

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

 

[ryguy7272]

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---

 

[Fred]

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

Fred

 

[Peter T]

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]

Thanks Peter.

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

 

[Peter T]

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

 

[Fred]

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