Thursday, March 3, 2016

Print documents from excel through hyperlink assigned to shape or image


Hi, Let's see how you can print pdf or doc file from excel. Nowadays on excel dashboard we seldom give external link to documents through inset image in cells. When we click on an image in a cell we are directed to the document attached to it, and we can view & print that document after it. However, situation would be different when you are asked to print all such hyperlink docs and there are n number of such cells. So lets create a macro to deal with situation, first you need  to set a default printer, then select range containg cells with a image & run below macro


Option Explicit

Public 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 Function PrintThisDoc(formname As Long, FileName As String)

On Error Resume Next

Dim X As Long

X = ShellExecute(formname, "Print", FileName, 0&, 0&, 3)

End Function



Sub print_hyp_doc()

Dim c As Range, rng As Range

Dim shp As Shape

Dim printThis



Set rng = Selection.SpecialCells(xlCellTypeVisible)



On Error Resume Next

For Each c In rng

For Each shp In ActiveSheet.Shapes

If shp.TopLeftCell.Address = c.Address Then

Debug.Print shp.Hyperlink.Address

'shp.Hyperlink.Follow NewWindow:=True

'printThis = PrintThisDoc(0, strDir & "\" & strFile)

printThis = PrintThisDoc(0, shp.Hyperlink.Address)

End If

Next shp

Next

End Sub