Hi there.
I’m hoping someone can help me. I currently have a vba macro in Excel that looks in Column “A” and picks out all paths of documents with an ending of “.pdf” and prints the document using the default pdf reader. I would like to expand this macro so that it prints .jpg, .tiff and .tifflist pictures to either the Microsoft Picture Manager or the Windows Photo Viewer, except that I end up getting the print dialog box, which I don’t want. I tried searching the internet, but couldn’t seem to find a way to resolve this. I can’t use Paint because there are multi-page Tiff files that will only print the first page in the Tiff file.
Has anyone accomplished this? I will include the code that I have so far and cross my fingers that it is something really easy…
Option Explicit
Private Declare Function ShellExecute Lib “shell32.dll” Alias _
“ShellExecuteA” (ByVal hwnd As Long, ByVal lpszOp As _
String, ByVal lpszFile As String, ByVal lpszParams As String, _
ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
Private Declare Function GetDesktopWindow Lib “user32″ () As Long
Const SW_SHOWNORMAL = 1
Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&
Private Declare Function GetRidofDialog Lib ” ” ()
Function StartDoc(DocName As Variant) As Long
Dim Scr_hDC As Long
Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, “Print”, DocName, _
“”, “C:”, SW_SHOWNORMAL)
End Function
Sub PrintPDFfiles()
Dim zProg As String
Dim SayWhat, Btns, BoxTitle, Answer, cell
Dim zLastRow As Long, Temp As String
Dim zFile As Variant, zDone As Long
Dim zCommand As Long
‘FETCH NUMBER OF FILES TO PRINT..
zLastRow = [a65536].End(xlUp).Row ‘find last row in column [A]; e.g. 15
Temp = “a1:a” & zLastRow ‘e.g. “a1:a15”
‘DISPLAY MESSAGE BEFORE PROCEEDING..
SayWhat = “Did you set a Zerox printer as your default printer?” ‘message text
Btns = vbYesNoCancel + vbDefaultButton2 ‘message box buttons
BoxTitle = “PRINT PDF FILES..” ‘message box heading
Answer = MsgBox(SayWhat, Btns, BoxTitle) ‘display message box
‘CHECK IF USER WANTS TO PROCEED..
If Answer vbYes Then ‘User cancelled; did not click [Yes] button
Exit Sub ‘nothing else to do
End If ‘end of test for User cancelled
‘PROCEED WITH PRINTING PDF FILES..
For Each cell In Range(Temp) ‘loop through all entries in COLUMN [A] range
zFile = Trim(cell.Value) ‘fetch filename from cell; remove any trailing spaces
zDone = 0 ‘initialise counter
If zFile Like “*.pdf” Then ‘make sure it is a pdf file type
zCommand = StartDoc(zFile)
ElseIf zFile Like “*.jpg” Then
zCommand = StartDoc(zFile)
ElseIf zFile Like “*.tiff” Then
zCommand = StartDoc(zFile)
ElseIf zFile Like “*.tifflist” Then
zCommand = StartDoc(zFile)
zDone = zDone + 1 ‘increment counter
End If ‘end of test for pdf file type
Application.Wait (Now + TimeValue(“0:00:05”))
Next ‘process next file in list
‘DISPLAY COMPLETION MESSAGE..
SayWhat = “DONE!”
SayWhat = SayWhat & vbCr & vbCr
SayWhat = SayWhat & “Your files are being sent to default printer. This make take a few minutes”
SayWhat = SayWhat & vbCr & vbCr
Btns = vbOKOnly + vbInformation
BoxTitle = “PRINTING ROUTINE”
Answer = MsgBox(SayWhat, Btns, BoxTitle)
End Sub
